MySQL 保留字踩坑實錄：`GROUP` 作為欄位名導致語法錯誤

血淚教訓

整整花了一個上午 + 下午兩小時調試一條 `GROUP BY` 語句報錯，最終發現：資料表中有個欄位命名為 `group` —— 這是 MySQL 保留字！加上反引號 \`group\` 後問題瞬間解決。

MySQL 保留字完整列表 (部分)

以下為高頻保留字，完整列表請參考官方文檔：

ADD, ALL, ALTER, ANALYZE, AND, AS, ASC, BEFORE, BETWEEN, BIGINT, BINARY, BLOB,
BOTH, BY, CALL, CASCADE, CASE, CHANGE, CHAR, CHARACTER, CHECK, COLLATE, COLUMN,
CONDITION, CONSTRAINT, CONTINUE, CONVERT, CREATE, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, DATABASE, DATABASES, DAY_HOUR, DAY_MICROSECOND,
DAY_MINUTE, DAY_SECOND, DEC, DECIMAL, DECLARE, DEFAULT, DELAYED, DELETE, DESC, DESCRIBE,
DETERMINISTIC, DISTINCT, DISTINCTROW, DIV, DOUBLE, DROP, DUAL, EACH, ELSE, ELSEIF,
ENCLOSED, ESCAPED, EXISTS, EXIT, EXPLAIN, FALSE, FETCH, FLOAT, FOR, FORCE, FOREIGN,
FROM, FULLTEXT, GRANT, GROUP, HAVING, HIGH_PRIORITY, HOUR_MICROSECOND, HOUR_MINUTE,
HOUR_SECOND, IF, IGNORE, IN, INDEX, INFILE, INNER, INOUT, INSENSITIVE, INSERT, INT,
INTERVAL, INTO, IS, ITERATE, JOIN, KEY, KEYS, KILL, LEADING, LEAVE, LEFT, LIKE, LIMIT,
LINEAR, LINES, LOAD, LOCK, LONG, LONGBLOB, LONGTEXT, LOOP, LOW_PRIORITY, MATCH,
MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NATURAL, NOT, NULL, NUMERIC, ON, OPTIMIZE, OPTION,
OPTIONALLY, OR, ORDER, OUT, OUTER, OUTFILE, PRECISION, PRIMARY, PROCEDURE, PURGE,
RANGE, READ, READS, REAL, REFERENCES, REGEXP, RELEASE, RENAME, REPEAT, REPLACE,
REQUIRE, RESTRICT, RETURN, REVOKE, RIGHT, RLIKE, SCHEMA, SCHEMAS, SECOND_MICROSECOND,
SELECT, SENSITIVE, SEPARATOR, SET, SHOW, SMALLINT, SPATIAL, SPECIFIC, SQL, SQLEXCEPTION,
SQLSTATE, SQLWARNING, SSL, STARTING, STRAIGHT_JOIN, TABLE, TERMINATED, THEN, TINYBLOB,
TINYINT, TINYTEXT, TO, TRAILING, TRIGGER, TRUE, UNDO, UNION, UNIQUE, UNLOCK, UNSIGNED,
UPDATE, USAGE, USE, USING, UTC_DATE, UTC_TIME, UTC_TIMESTAMP, VALUES, VARBINARY, VARCHAR,
VARYING, WHEN, WHERE, WHILE, WITH, WRITE, XOR, YEAR_MONTH, ZEROFILL

最佳實踐

1. 避免使用保留字作為資料庫、資料表、欄位、索引、別名等標識符
2. 強制使用反引號包裹所有標識符
3. 命名規範建議：t_user、col_create_time、idx_status_type 等帶前綴風格
4. 使用 IDE/工具 (DataGrip、MySQL Workbench) 會自動高亮保留字

-- 建表時
CREATE TABLE \`order\` (
  \`id\` INT PRIMARY KEY,
  \`group\` VARCHAR(50),  -- 保留字作為欄位名
  \`order\` INT           -- 保留字作為欄位名
);

-- 查詢時
SELECT \`id\`, \`group\`, \`order\` FROM \`order\` WHERE \`group\` = 'VIP' ORDER BY \`order\` DESC;