建表報錯 ERROR 1064 (42000): You have an error in your SQL syntax near 'order'... —— 列名 order、group、select 等與 MySQL 保留字衝突。本文提供標準轉義方案與完整保留字參考表。
` 包裹-- 錯誤
CREATE TABLE test (order INT, group VARCHAR(20), select DATE);
-- 正確：所有標識符（表名、列名、庫名、索引名、別名）均建議加反引號
CREATE TABLE `test` (
`order` INT,
`group` VARCHAR(20),
`select` DATE
);
-- 查詢同理
SELECT `order`, `group` FROM `test` WHERE `select` > '2023-01-01';
最佳實踐：所有 DDL/DML 中的標識符統一加反引號，ORM 框架（Hibernate、MyBatis-Plus、JPA）默認會自動加反引號，手寫 SQL 必須養成習慣。
| 類別 | 高風險保留字（建議避免作標識符） |
|---|---|
| DDL/事務 | ADD, ALTER, CREATE, DROP, INDEX, LOCK, UNLOCK, COMMIT, ROLLBACK, SAVEPOINT, START, TRANSACTION |
| 查詢/條件 | SELECT, FROM, WHERE, GROUP, ORDER, BY, HAVING, LIMIT, OFFSET, DISTINCT, AS, JOIN, INNER, LEFT, RIGHT, FULL, OUTER, ON, USING, UNION, INTERSECT, EXCEPT |
| 數據操作 | INSERT, UPDATE, DELETE, REPLACE, VALUES, SET, DEFAULT, NULL, TRUE, FALSE |
| 數據類型 | INT, BIGINT, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, DECIMAL, FLOAT, DOUBLE, BOOLEAN, BINARY, ENUM, SET, JSON |
| 約束/鍵 | PRIMARY, KEY, UNIQUE, FOREIGN, REFERENCES, CONSTRAINT, CHECK, AUTO_INCREMENT, INDEX, FULLTEXT, SPATIAL |
| 函數/操作符 | COUNT, SUM, AVG, MIN, MAX, IF, CASE, WHEN, THEN, ELSE, END, LIKE, IN, BETWEEN, IS, NOT, AND, OR, XOR |
| 權限/管理 | GRANT, REVOKE, PRIVILEGES, USER, PASSWORD, ROLE, ADMIN, SUPER, PROCESS, SHUTDOWN |
| 極易踩坑的業務詞 | ORDER, GROUP, SELECT, DESC, KEY, INDEX, RANK, LEVEL, STATUS, TYPE, NAME, VALUE, CONDITION, OPTION, MODE, RULE, ACTION, TRIGGER, EVENT, SCHEDULE, PARTITION, TABLESPACE, LOGFILE, MASTER, SLAVE, RELAY, SOURCE, REPLICA |
MySQL 版本間保留字有差異，請以當前版本文檔為準：
#!/bin/bash # 檢查 SQL 文件中的標識符是否為保留字（需配合 MySQL 關鍵字列表） # 簡易版：grep 常見高危詞 grep -i -E '\b(order|group|select|desc|key|index|rank|level|status|type|name|value|condition|option|mode|rule|action|trigger|event|schedule|partition|tablespace|logfile|master|slave|relay|source|replica)\b' your_sql_file.sql
order → order_id、order_no、order_status；group → group_id、group_namet_order、c_group、u_statussnake_case，配合反引號雙重保險反引號是標準、通用、零成本的解決方案。養成「寫標識符先按反引號」的肌肉記憶，配合命名規範，徹底告別保留字衝突報錯。
