1. 背景描述
前几天遇到这么一个问题,用navicat创建个数据表,其中有一个字段标识用量,用量嘛依照我的六级未遂的英语,Usage。嗯,这个单词贴切,够短,perfect!表真的创建成功了,但是插入数据时就是不对,语法错误!what a fuck!检查了半天终于发现Usage这个罪魁祸首,这货别是个关键字吧,查了一下,嘿,还真是,fuck again!
2. 官方文档说明
查了一下官方文档是这么说的:
Keywords are words that have significance in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions.
关键字是在SQL中有意义的单词。某些关键字(如SELECT、DELETE或BIGINT)是保留的,需要特殊处理才能用作表和列名等标识符。对于内置函数的名称也可能是这样。
Nonreserved keywords are permitted as identifiers without quoting.
不带引号的关键字可以作为标识符。
mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ... near 'interval (begin INT, end INT)'
BEGIN and END are keywords but not reserved, so their use as identifiers does not require quoting. INTERVAL is a reserved keyword and must be quoted to be used as an identifier
BEGIN和END是关键字,但不是保留的,因此它们用作标识符不需要引用。INTERVAL是保留关键字,必须引用才能用作标识符
mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
Exception: A word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved
例外:限定名中句点后面的单词必须是标识符,因此即使保留了它,也不必引用
Names of built-in functions are permitted as identifiers but may require care to be used as such. For example, COUNT is acceptable as a column name. However, by default, no whitespace is permitted in function invocations between the function name and the following ( character. This requirement enables the parser to distinguish whether the name is used in a function call or in nonfunction context.
内置函数的名称允许作为标识符,但可能需要谨慎使用。例如,COUNT可以作为列名。但是,默认情况下,函数调用中函数名和以下字符之间不允许有空格。这个需求使解析器能够区分名称是在函数调用中使用还是在非函数上下文中使用。
3. MySQL5.7关键字保留字列表
下面罗列了MySQL5.7版本的关键字和保留字,做个笔记吧,毕竟年龄大了,记性不好,顺便说一句,带 ®标识的为关键字
A
ACCESSIBLE ® | ACCOUNT; added in 5.7.6 (nonreserved) | ACTION | ADD ® | AFTER |
AGAINST | AGGREGATE | ALGORITHM | ALL ® | ALTER ® |
ALWAYS; added in 5.7.6 (nonreserved) | ANALYSE | ANALYZE ® | AND ® | ANY |
AS ® | ASC ® | ASCII | ASENSITIVE ® | AT |
AUTOEXTEND_SIZE | AUTO_INCREMENT | AVG | AVG_ROW_LENGTH |
B
ACKUP | BEFORE ® | BEGIN | BETWEEN ® | BIGINT ® |
INARY ® | BINLOG | BIT | BLOB ® | BLOCK |
BOOL | BOOLEAN | BOTH ® | BTREE | BY ® |
BYTE |
C
CACHE | CALL ® | CASCADE ® | CASCADED | CASE ® |
CATALOG_NAME | CHAIN | CHANGE ® | CHANGED | CHANNEL; added in 5.7.6 (nonreserved) |
CHAR ® | CHARACTER ® | CHARSET | CHECK ® | CHECKSUM |
CIPHER | CLASS_ORIGIN | CLIENT | CLOSE | COALESCE |
CODE | COLLATE ® | COLLATION | COLUMN ® | COLUMNS |
COLUMN_FORMAT | COLUMN_NAME | COMMENT | COMMIT | COMMITTED |
COMPACT | COMPLETION | COMPRESSED | COMPRESSION; added in 5.7.8 (nonreserved) | CONCURRENT |
CONDITION ® | CONNECTION | CONSISTENT | CONSTRAINT ® | CONSTRAINT_CATALOG |
CONSTRAINT_NAME | CONSTRAINT_SCHEMA | CONTAINS | CONTEXT | CONTINUE ® |
CONVERT ® | CPU | CREATE ® | CROSS ® | CUBE |
CURRENT | CURRENT_DATE ® | CURRENT_TIME ® | CURRENT_TIMESTAMP ® | CURRENT_USER ® |
CURSOR ® | CURSOR_NAME |
D
DATA | DATABASE ® | DATABASES ® | DATAFILE | DATE |
DATETIME | DAY | DAY_HOUR ® | DAY_MICROSECOND ® | DAY_MINUTE ® |
DAY_SECOND ® | DEALLOCATE | DEC ® | DECIMAL ® | DECLARE ® |
DEFAULT ® | DEFAULT_AUTH | DEFINER | DELAYED ® | DELAY_KEY_WRITE |
DELETE ® | DESC ® | DESCRIBE ® | DES_KEY_FILE | DETERMINISTIC ® |
DIAGNOSTICS | DIRECTORY | DISABLE | DISCARD | DISK |
DISTINCT ® | DISTINCTROW ® | DIV ® | DO | DOUBLE ® |
DROP ® | DUAL ® | DUMPFILE | DUPLICATE | DYNAMIC |
E
EACH ® | ELSE ® | ELSEIF ® | ENABLE | ENCLOSED ® |
ENCRYPTION; added in 5.7.11 (nonreserved) | END | ENDS | ENGINE | ENGINES |
ENUM | ERROR | ERRORS | ESCAPE | ESCAPED ® |
EVENT | EVENTS | EVERY | EXCHANGE | EXECUTE |
EXISTS ® | EXIT ® | EXPANSION | EXPIRE | EXPLAIN ® |
EXPORT | EXTENDED | EXTENT_SIZE |
F
FALSE ® | FAST | FAULTS | FETCH ® | FIELDS |
FILE | FILE_BLOCK_SIZE; added in 5.7.6 (nonreserved) | FILTER; added in 5.7.3 (nonreserved) | FIRST | FIXED |
FLOAT ® | FLOAT4 ® | FLOAT8 ® | FLUSH | FOLLOWS; added in 5.7.2 (nonreserved) |
FOR ® | FORCE ® | FOREIGN ® | FORMAT | FOUND |
FROM ® | FULL | FULLTEXT ® | FUNCTION |
G
GENERAL | GENERATED ®; added in 5.7.6 (reserved) | GEOMETRY | GEOMETRYCOLLECTION | GET ® |
GET_FORMAT | GLOBAL | GRANT ® | GRANTS | GROUP ® |
GROUP_REPLICATION; added in 5.7.6 (nonreserved) |
H
HANDLER | HASH | HAVING ® | HELP | HIGH_PRIORITY ® |
HOST | HOSTS | HOUR | HOUR_MICROSECOND ® | HOUR_MINUTE ® |
HOUR_SECOND ® |
I
IDENTIFIED | IF ® | IGNORE ® | IGNORE_SERVER_IDS | IMPORT |
IN ® | INDEX ® | INDEXES | INFILE ® | INITIAL_SIZE |
INNER ® | INOUT ® | INSENSITIVE ® | INSERT ® | INSERT_METHOD |
INSTALL | INSTANCE; added in 5.7.11 (nonreserved) | INT ® | INT1 ® | INT2 ® |
INT3 ® | INT4 ® | INT8 ® | INTEGER ® | INTERVAL ® |
INTO ® | INVOKER | IO | IO_AFTER_GTIDS ® | IO_BEFORE_GTIDS ® |
IO_THREAD | IPC | IS ® | ISOLATION | ISSUER |
J
JOIN ® | JSON; added in 5.7.8 (nonreserved) |
K
KEY ® | KEYS ® | KEY_BLOCK_SIZE | KILL ® |
L
LANGUAGE | LAST | LEADING ® | LEAVE ® | LEAVES |
LEFT ® | LESS | LEVEL | LIKE ® | LIMIT ® |
INEAR ® | LINES ® | LINESTRING | LIST | LOAD ® |
LOCAL | LOCALTIME ® | LOCALTIMESTAMP ® | LOCK ® | LOCKS |
LOGFILE | LOGS | LONG ® | LONGBLOB ® | LONGTEXT ® |
LOOP ® | LOW_PRIORITY ® |
M
MASTER | MASTER_AUTO_POSITION | MASTER_BIND ® | MASTER_CONNECT_RETRY | MASTER_DELAY |
MASTER_HEARTBEAT_PERIOD | MASTER_HOST | MASTER_LOG_FILE | MASTER_LOG_POS | MASTER_PASSWORD |
MASTER_PORT | MASTER_RETRY_COUNT | MASTER_SERVER_ID | MASTER_SSL | MASTER_SSL_CA |
MASTER_SSL_CAPATH | MASTER_SSL_CERT | MASTER_SSL_CIPHER | MASTER_SSL_CRL | MASTER_SSL_CRLPATH |
MASTER_SSL_KEY | MASTER_SSL_VERIFY_SERVER_CERT ® | MASTER_TLS_VERSION; added in 5.7.10 (nonreserved) | MASTER_USER | MATCH ® |
MAXVALUE ® | MAX_CONNECTIONS_PER_HOUR | MAX_QUERIES_PER_HOUR | MAX_ROWS | MAX_SIZE |
MAX_STATEMENT_TIME; added in 5.7.4 (nonreserved); removed in 5.7.8 | MAX_UPDATES_PER_HOUR | MAX_USER_CONNECTIONS | MEDIUM | MEDIUMBLOB ® |
MEDIUMINT ® | MEDIUMTEXT ® | MEMORY | MERGE | MESSAGE_TEXT |
MICROSECOND | MIDDLEINT ® | MIGRATE | MINUTE | MINUTE_MICROSECOND ® |
MINUTE_SECOND ® | MIN_ROWS | MOD ® | MODE | MODIFIES ® |
MODIFY | MONTH | MULTILINESTRING | MULTIPOINT | MULTIPOLYGON |
MUTEX | MYSQL_ERRNO |
N
NAME | NAMES | NATIONAL | NATURAL ® | NCHAR |
NDB | NDBCLUSTER | NEVER; added in 5.7.4 (nonreserved) | NEW | NEXT |
NO | NODEGROUP | NONBLOCKING; removed in 5.7.6 | NONE | NOT ® |
NO_WAIT | NO_WRITE_TO_BINLOG ® | NULL ® | NUMBER | NUMERIC ® |
NVARCHAR |
O
OFFSET | OLD_PASSWORD; removed in 5.7.5 | ON ® | ONE | ONLY |
OPEN | OPTIMIZE ® | OPTIMIZER_COSTS ®; added in 5.7.5 (reserved) | OPTION ® | OPTIONALLY ® |
OPTIONS | OR ® | ORDER ® | OUT ® | OUTER ® |
OUTFILE ® | OWNER |
P
PACK_KEYS | PAGE | PARSER | PARSE_GCOL_EXPR; added in 5.7.6 (reserved); became nonreserved in 5.7.8 | PARTIAL |
PARTITION ® | PARTITIONING | PARTITIONS | PASSWORD | PHASE |
PLUGIN | PLUGINS | PLUGIN_DIR | POINT | POLYGON |
PORT | PRECEDES; added in 5.7.2 (nonreserved) | PRECISION ® | PREPARE | PRESERVE |
PREV | PRIMARY ® | PRIVILEGES | PROCEDURE ® | PROCESSLIST |
PROFILE | PROFILES | PROXY | PURGE ® |
Q
QUARTER | QUERY | QUICK |
R
RANGE ® | READ ® | READS ® | READ_ONLY | READ_WRITE ® |
REAL ® | REBUILD | RECOVER | REDOFILE | REDO_BUFFER_SIZE |
REDUNDANT | REFERENCES ® | REGEXP ® | RELAY | RELAYLOG |
RELAY_LOG_FILE | ELAY_LOG_POS | RELAY_THREAD | RELEASE ® | |
RELOAD | REMOVE | RENAME ® | REORGANIZE | REPAIR |
REPEAT ® | REPEATABLE | REPLACE ® | REPLICATE_DO_DB; added in 5.7.3 (nonreserved) | REPLICATE_DO_TABLE; added in 5.7.3 (nonreserved) |
REPLICATE_IGNORE_DB; added in 5.7.3 (nonreserved) | REPLICATE_IGNORE_TABLE; added in 5.7.3 (nonreserved) | REPLICATE_REWRITE_DB; added in 5.7.3 (nonreserved) | REPLICATE_WILD_DO_TABLE; added in 5.7.3 (nonreserved) | REPLICATE_WILD_IGNORE_TABLE; added in 5.7.3 (nonreserved) |
REPLICATION | REQUIRE ® | RESET | RESIGNAL ® | RESTORE |
RESTRICT ® | RESUME | RETURN ® | RETURNED_SQLSTATE | RETURNS |
REVERSE | REVOKE ® | RIGHT ® | RLIKE ® | ROLLBACK |
ROLLUP | ROTATE; added in 5.7.11 (nonreserved) | ROUTINE | ROW | ROWS |
ROW_COUNT | ROW_FORMAT | RTREE |
S
SAVEPOINT | SCHEDULE | SCHEMA ® | SCHEMAS ® | SCHEMA_NAME |
SECOND | SECOND_MICROSECOND ® | SECURITY | SELECT ® | SENSITIVE ® |
SEPARATOR ® | SERIAL | SERIALIZABLE | SERVER | SESSION |
SET ® | SHARE | SHOW ® | SHUTDOWN | SIGNAL ® |
SIGNED | SIMPLE | SLAVE | SLOW | SMALLINT ® |
SNAPSHOT | SOCKET | SOME | SONAME | SOUNDS |
SOURCE | SPATIAL ® | SPECIFIC ® | SQL ® | SQLEXCEPTION ® |
SQLSTATE ® | SQLWARNING ® | SQL_AFTER_GTIDS | SQL_AFTER_MTS_GAPS | SQL_BEFORE_GTIDS |
SQL_BIG_RESULT ® | SQL_BUFFER_RESULT | SQL_CACHE | SQL_CALC_FOUND_ROWS ® | SQL_NO_CACHE |
SQL_SMALL_RESULT ® | SQL_THREAD | SQL_TSI_DAY | SQL_TSI_HOUR | SQL_TSI_MINUTE |
SQL_TSI_MONTH | SQL_TSI_QUARTER | SQL_TSI_SECOND | SQL_TSI_WEEK | SQL_TSI_YEAR |
SSL ® | STACKED | START | STARTING ® | STARTS |
STATS_AUTO_RECALC | STATS_PERSISTENT | STATS_SAMPLE_PAGES | STATUS | STOP |
STORAGE | STORED ®; added in 5.7.6 (reserved) | STRAIGHT_JOIN ® | STRING | SUBCLASS_ORIGIN |
SUBJECT | SUBPARTITION | SUBPARTITIONS | SUPER | SUSPEND |
SWAPS | SWITCHES |
T
TABLE ® | TABLES | TABLESPACE | TABLE_CHECKSUM | TABLE_NAME |
TEMPORARY | TEMPTABLE | TERMINATED ® | TEXT | THAN |
THEN ® | TIME | TIMESTAMP | TIMESTAMPADD | TIMESTAMPDIFF |
TINYBLOB ® | TINYINT ® | TINYTEXT ® | TO ® | TRAILING ® |
TRANSACTION | TRIGGER ® | TRIGGERS | TRUE ® | TRUNCATE |
TYPE | TYPES |
U
UNCOMMITTED | UNDEFINED | UNDO ® | UNDOFILE | UNDO_BUFFER_SIZE |
UNICODE | UNINSTALL | UNION ® | UNIQUE ® | UNKNOWN |
UNLOCK ® | UNSIGNED ® | UNTIL | UPDATE ® | UPGRADE |
USAGE ® | USE ® | USER | USER_RESOURCES | USE_FRM |
USING ® | UTC_DATE ® | UTC_TIME ® | UTC_TIMESTAMP ® |
V
VALIDATION; added in 5.7.5 (nonreserved) | VALUE | VALUES ® | VARBINARY ® | VARCHAR ® |
VARCHARACTER ® | VARIABLES | VARYING ® | VIEW | VIRTUAL ®; added in 5.7.6 (reserved) |
W
WAIT | WARNINGS | WEEK | WEIGHT_STRING | WHEN ® |
WHERE ® | WHILE ® | WITH ® | WITHOUT; added in 5.7.5 (nonreserved) | WORK |
WRAPPER | WRITE ® |
X
X509 | XA | XID; added in 5.7.5 (nonreserved) | XML | XOR ® |
Y
YEAR | YEAR_MONTH ® |
Z
ZEROFILL ® |
4. 总结
MySQL关键字很多,大部分关键字可以直接用作表名或者字段名,但是保留字必须用引号包含才可以使用。但是还是能不用尽量不用吧,毕竟麻烦,打断sql多一个少一个引号根本注意不到好吗?!!你可以做但没必要~