之前在创建mysql语句时,觉得有个字段可能是保留字,不太确定,便去google了。结果英语不好,搜索的是“mysql Retention”,什么都没搜到。痛定思痛,加强英语的学习吧,顺便记录个文章方便以后查阅。

关键字:keywords
保留字:reserved words

随着mysql版本的升级,关键字和保留字也会因为版本升级而进行增减,mysql官方文档会记录当前最新的Keywords and Reserved Words列表。

关键字其实就是sql语句中有意义的字,而其中一部分关键字在被mysql当做标识符时需要进行特殊处理,这类关键字叫做保留字。保留字包括SELECT,DELETE或BIGINT等等。

非保留关键字作为标识符,不需要引用声明(键盘上左上角`符号),而保留关键词在被当做标识符时,为了避免产生歧义,需要加引用声明。

这里以创建一个表为例子。

#创建一个表,表名test,执行成功
create table test (id int);
Query OK, 0 rows affected (0.05 sec)
#创建一个表,表名为关键字action,但非保留字,执行成功
create table action (id int);
Query OK, 0 rows affected (0.12 sec)
#创建一个表,表名为关键字asc,且为保留字,执行失败
create table asc(id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asc (id int)' at line 1

所以我们在使用sql语句时,不能使用保留字作为表名、列名等,若想使用,则需要引用声明。

#创建一个表,表名为关键字`asc`,且为保留字,执行成功
create table `asc`(id int);
Query OK, 0 rows affected (0.10 sec)

关键字列表如下,红色字体表示保留字。

平时在使用时,可以经常查阅,避免使用关键字作为数据库字段名。

ACCESSIBLE ACCOUNT ACTION
ADD AFTER AGAINST
AGGREGATE ALGORITHM ALL
ALTER ALWAYS ANALYSE
ANALYZE AND ANY
AS ASC ASCII
ASENSITIVE AT AUTOEXTEND_SIZE
AUTO_INCREMENT AVG AVG_ROW_LENGTH
BACKUP BEFORE BEGIN
BETWEEN BIGINT BINARY
BINLOG BIT BLOB
BLOCK BOOL BOOLEAN
BOTH BTREE BY
BYTE CACHE CALL
CASCADE CASCADED CASE
CATALOG_NAME CHAIN CHANGE
CHANGED CHANNEL 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 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 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 EACH
ELSE ELSEIF ENABLE
ENCLOSED END ENDS
ENGINE ENGINES ENUM
ERROR ERRORS ESCAPE
ESCAPED EVENT EVENTS
EVERY EXCHANGE EXECUTE
EXISTS EXIT EXPANSION
EXPIRE EXPLAIN EXPORT
EXTENDED EXTENT_SIZE FALSE
FAST FAULTS FETCH
FIELDS FILE FILE_BLOCK_SIZE
FILTER FIRST FIXED
FLOAT FLOAT4 FLOAT8
FLUSH FOLLOWS FOR
FORCE FOREIGN FORMAT
FOUND FROM FULL
FULLTEXT FUNCTION GENERAL
GENERATED GEOMETRY GEOMETRYCOLLECTION
GET GET_FORMAT GLOBAL
GRANT GRANTS GROUP
GROUP_REPLICATION HANDLER HASH
HAVING HELP HIGH_PRIORITY
HOST HOSTS HOUR
HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND
IDENTIFIED IF IGNORE
IGNORE_SERVER_IDS IMPORT IN
INDEX INDEXES INFILE
INITIAL_SIZE INNER INOUT
INSENSITIVE INSERT INSERT_METHOD
INSTALL INT INT1
INT2 INT3 INT4
INT8 INTEGER INTERVAL
INTO INVOKER IO
IO_AFTER_GTIDS IO_BEFORE_GTIDS IO_THREAD
IPC IS ISOLATION
ISSUER ITERATE JOIN
JSON KEY KEYS
KEY_BLOCK_SIZE KILL LANGUAGE
LAST LEADING LEAVE
LEAVES LEFT LESS
LEVEL LIKE LIMIT
LINEAR LINES LINESTRING
LIST LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCK
LOCKS LOGFILE LOGS
LONG LONGBLOB LONGTEXT
LOOP LOW_PRIORITY 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_USER MATCH MAXVALUE
MAX_CONNECTIONS_PER_HOUR MAX_QUERIES_PER_HOUR MAX_ROWS
MAX_SIZE MAX_STATEMENT_TIME 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 NAME NAMES
NATIONAL NATURAL NCHAR
NDB NDBCLUSTER NEVER
NEW NEXT NO
NODEGROUP NONBLOCKING NONE
NOT NO_WAIT NO_WRITE_TO_BINLOG
NULL NUMBER NUMERIC
NVARCHAR OFFSET OLD_PASSWORD
ON ONE ONLY
OPEN OPTIMIZE OPTIMIZER_COSTS
OPTION OPTIONALLY OPTIONS
OR ORDER OUT
OUTER OUTFILE OWNER
PACK_KEYS PAGE PARSER
PARSE_GCOL_EXPR PARTIAL PARTITION
PARTITIONING PARTITIONS PASSWORD
PHASE PLUGIN PLUGINS
PLUGIN_DIR POINT POLYGON
PORT PRECEDES PRECISION
PREPARE PRESERVE PREV
PRIMARY PRIVILEGES PROCEDURE
PROCESSLIST PROFILE PROFILES
PROXY PURGE QUARTER
QUERY QUICK RANGE
READ READS READ_ONLY
READ_WRITE REAL REBUILD
RECOVER REDOFILE REDO_BUFFER_SIZE
REDUNDANT REFERENCES REGEXP
RELAY RELAYLOG RELAY_LOG_FILE
RELAY_LOG_POS RELAY_THREAD RELEASE
RELOAD REMOVE RENAME
REORGANIZE REPAIR REPEAT
REPEATABLE REPLACE REPLICATE_DO_DB
REPLICATE_DO_TABLE REPLICATE_IGNORE_DB REPLICATE_IGNORE_TABLE
REPLICATE_REWRITE_DB REPLICATE_WILD_DO_TABLE REPLICATE_WILD_IGNORE_TABLE
REPLICATION REQUIRE RESET
RESIGNAL RESTORE RESTRICT
RESUME RETURN RETURNED_SQLSTATE
RETURNS REVERSE REVOKE
RIGHT RLIKE ROLLBACK
ROLLUP ROUTINE ROW
ROWS ROW_COUNT ROW_FORMAT
RTREE 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 STRAIGHT_JOIN
STRING SUBCLASS_ORIGIN SUBJECT
SUBPARTITION SUBPARTITIONS SUPER
SUSPEND SWAPS SWITCHES
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
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
VALIDATION VALUE VALUES
VARBINARY VARCHAR VARCHARACTER
VARIABLES VARYING VIEW
VIRTUAL WAIT WARNINGS
WEEK WEIGHT_STRING WHEN
WHERE WHILE WITH
WITHOUT WORK WRAPPER
WRITE X509 XA
XID XML XOR
YEAR YEAR_MONTH ZEROFILL