MySQL5.7中的关键字与保留字详解
前⾔
MySQL和Oracle的关键字还是不尽相同的，在Oracle数据库中，我们的数据表中定义了⼤量的code字段⽤来表⽰主键，但是在MySQL中code是关键字，使⽤以前的处理⽅法就有些“⽔⼟不服”。
下⾯我们来了解⼀下MySQL中的关键字和保留字。
什么是关键字和保留字
关键字是指在SQL中有意义的字。某些关键字（例如SELECT，DELETE或BIGINT）是保留的，需要特殊处理才能⽤作表和列名称等标识符。这⼀点对于内置函数的名称也适⽤。
如何使⽤关键字和保留字
⾮保留关键字允许作为标识符，不需要加引号。如果您要适⽤保留字作为标识符，就必须适⽤引号。
举个例⼦，BEGIN和END是关键字，但不是保留字，因此它们⽤作标识符不需要引号。 INTERVAL是保留关键字，必须加上引号才能⽤作标识符。
mysql>
mysql> use hoegh;
Database changed
mysql>
mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000):
mysql>
mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.42 sec)
mysql>
mysql> show create table `interval`;
+----------+---------------------------------------------------------
| Table | Create Table
+----------+---------------------------------------------------------
| interval | CREATE TABLE `interval` (
`begin` int(11) DEFAULT NULL,
`end` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------
1 row in set (0.00 sec)
mysql>
我们看到，第⼀条语句中表名使⽤了保留字interval，执⾏失败；
第⼆条语句对interval加了引号，执⾏成功。
在这⾥需要注意的是，引号必须是反引号，⽽⾮单引号。否则会报错，如下所⽰：
mysql>
mysql> drop table `interval`;--使⽤反引号
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> create table 'interval' (begin INT, end INT);--使⽤单引号，报错
ERROR 1064 (42000):
mysql>
有⼀个例外
如果标识符在限定名称（数据库名）的句点之后，即使是保留关键字也不需要引号。
我们以hoegh数据库为例，如果表名写为hoegh.interval就不需要对保留字interval加引号了。
mysql>
mysql> create table hoegh.interval (begin INT, end INT);
Query OK, 0 rows affected (0.19 sec)
mysql>
mysql> show create table hoegh.interval;
+----------+---------------------------------------------------------
| Table | Create Table
+----------+---------------------------------------------------------
| interval | CREATE TABLE `interval` (
`begin` int(11) DEFAULT NULL,
`end` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------
1 row in set (0.00 sec)
mysql>
关于使⽤内置函数名称
允许内置函数的名称可以作为标识符，但最好谨慎使⽤。例如，COUNT作为列名称是合法的。但是，默认情况下，在函数名和后⾯的（之间的函数调⽤中不允许有空格。这个限制使解析器能够区分名称是⽤于函数调⽤还是⽤在⾮函数上下⽂中。
附录
在某些时候，您可能需要升级到更⾼版本，因此最好查看⼀下未来的保留字。您可以在涵盖更⾼版本的MySQL的⼿册中到这些。对于表中的⼤多数保留字，在标准SQL中禁⽌作为列或表的名称（例如，GROUP）。其中⼀些保留字，是由于MySQL需要它们并使⽤⼀个yacc解析器。
以下列出三张表格：
第⼀个表格10.2显⽰MySQL 5.7中的关键字和保留字。保留的关键字标记为（R）。此外，_FILENAME是保留的。
第⼆个表格10.3显⽰MySQL 5.7相⽐5.6版本新增的保留字。
第三个表格10.4显⽰MySQL 5.7相⽐5.6版本删除的保留字。
Table 10.2 Keywords and Reserved Words in MySQL 5.7
ACCESSIBLE (R)ACCOUNT[a]ACTION
ADD (R)AFTER AGAINST
AGGREGATE ALGORITHM ALL (R)
ALTER (R)ALWAYS[b]ANALYSE
ANALYZE (R)AND (R)ANY
AS (R)ASC (R)ASCII
ASENSITIVE (R)AT AUTOEXTEND_SIZE
AUTO_INCREMENT AVG AVG_ROW_LENGTH
BACKUP BEFORE (R)BEGIN
retry是什么意思BETWEEN (R)BIGINT (R)BINARY (R)
BINLOG BIT BLOB (R)
BLOCK BOOL BOOLEAN
BOTH (R)BTREE BY (R)
BYTE CACHE CALL (R)
CASCADE (R)CASCADED CASE (R)
CATALOG_NAME CHAIN CHANGE (R)
CHANGED CHANNEL[c]CHAR (R)
CHARACTER (R)CHARSET CHECK (R)
CHECKSUM CIPHER CLASS_ORIGIN
CLIENT CLOSE COALESCE
CODE COLLATE (R)COLLATION
COLUMN (R)COLUMNS COLUMN_FORMAT
COLUMN_NAME COMMENT COMMIT
COMMITTED COMPACT COMPLETION
COMPRESSED COMPRESSION[d]CONCURRENT
CONDITION (R)CONNECTION CONSISTENT
CONSTRAINT (R)CONSTRAINT_CATALOG CONSTRAINT_NAME CONSTRAINT_SCHEMA CONTAINS CONTEXT
CONTINUE (R)CONVERT (R)CPU
CREATE (R)CROSS (R)CUBE
CURRENT CURRENT_DATE (R)CURRENT_TIME (R) CURRENT_TIMESTAMP (R)CURRENT_USER (R)CURSOR (R) CURSOR_NAME DATA DATABASE (R) DATABASES (R)DATAFILE DATE
DATETIME DAY DAY_HOUR (R) DAY_MICROSECOND (R)DAY_MINUTE (R)DAY_SECOND (R) DEALLOCATE DEC (R)DECIMAL (R) DECLARE (R)DEFAULT (R)DEFAULT_AUTH DEFINER DELAYED (R)DELAY_KEY_WRITE DELETE (R)DESC (R)DESCRIBE (R)
DES_KEY_FILE DETERMINISTIC (R)DIAGNOSTICS DIRECTORY DISABLE DISCARD
DISK DISTINCT (R)DISTINCTROW (R) DIV (R)DO DOUBLE (R)
DROP (R)DUAL (R)DUMPFILE DUPLICATE DYNAMIC EACH (R)
ELSE (R)ELSEIF (R)ENABLE ENCLOSED (R)ENCRYPTION[e]END
ENDS ENGINE ENGINES
ENUM ERROR ERRORS
ESCAPE ESCAPED (R)EVENT
EVENTS EVERY EXCHANGE EXECUTE EXISTS (R)EXIT (R) EXPANSION EXPIRE EXPLAIN (R) EXPORT EXTENDED EXTENT_SIZE FALSE (R)FAST FAULTS
FETCH (R)FIELDS FILE
FILE_BLOCK_SIZE[f]FILTER[g]FIRST
FIXED FLOAT (R)FLOAT4 (R)
FLOAT8 (R)FLUSH FOLLOWS[h]
FOR (R)FORCE (R)FOREIGN (R) FORMAT FOUND FROM (R)
FULL FULLTEXT (R)FUNCTION GENERAL GENERATED[i] (R)GEOMETRY GEOMETRYCOLLECTION GET (R)GET_FORMAT GLOBAL GRANT (R)GRANTS
GROUP (R)GROUP_REPLICATION[j]HANDLER
HASH HAVING (R)HELP
HIGH_PRIORITY (R)HOST HOSTS
HOUR HOUR_MICROSECOND (R)HOUR_MINUTE (R) HOUR_SECOND (R)IDENTIFIED IF (R)
IGNORE (R)IGNORE_SERVER_IDS IMPORT
IN (R)INDEX (R)INDEXES
INFILE (R)INITIAL_SIZE INNER (R)
INOUT (R)INSENSITIVE (R)INSERT (R) INSERT_METHOD INSTALL INSTANCE[k]
INT (R)INT1 (R)INT2 (R)
INT3 (R)INT4 (R)INT8 (R)
INTEGER (R)INTERVAL (R)INTO (R) INVOKER IO IO_AFTER_GTIDS (R) IO_BEFORE_GTIDS (R)IO_THREAD IPC
IS (R)ISOLATION ISSUER
ITERATE (R)JOIN (R)JSON[l]
KEY (R)KEYS (R)KEY_BLOCK_SIZE
KILL (R)LANGUAGE LAST
LEADING (R)LEAVE (R)LEAVES
LEFT (R)LESS LEVEL
LIKE (R)LIMIT (R)LINEAR (R)
LINES (R)LINESTRING LIST
LOAD (R)LOCAL LOCALTIME (R) LOCALTIMESTAMP (R)LOCK (R)LOCKS
LOGFILE LOGS LONG (R)
LONGBLOB (R)LONGTEXT (R)LOOP (R)
LOW_PRIORITY (R)MASTER MASTER_AUTO_POSITION MASTER_BIND (R)MASTER_CONNECT_RETRY MASTER_DELAY MASTER_HEARTBEAT_PERIOD MASTER_HOST MASTER_LOG_FILE MASTER_LOG_POS MASTER_PASSWORD MASTER_PO
RT
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(R)MASTER_TLS_VERSION[m] MASTER_USER MATCH (R)MAXVALUE (R)
MAX_CONNECTIONS_PER_HOUR MAX_QUERIES_PER_HOUR MAX_ROWS
MAX_SIZE MAX_STATEMENT_TIME[n]MAX_UPDATES_PER_HOUR MAX_USER_CONNECTIONS MEDIUM MEDIUMBLOB (R) MEDIUMINT (R)MEDIUMTEXT (R)MEMORY
MERGE MESSAGE_TEXT MICROSECOND MIDDLEINT (R)MIGRATE MINUTE
MINUTE_MICROSECOND (R)MINUTE_SECOND (R)MIN_ROWS
MOD (R)MODE MODIFIES (R)
MODIFY MONTH MULTILINESTRING MULTIPOINT MULTIPOLYGON MUTEX
MYSQL_ERRNO NAME NAMES
NATIONAL NATURAL (R)NCHAR
NDB NDBCLUSTER NEVER[o]
NEW NEXT NO
NODEGROUP NONBLOCKING[p]NONE
NOT (R)NO_WAIT NO_WRITE_TO_BINLOG (R) NULL (R)NUMBER NUMERIC (R) NVARCHAR OFFSET OLD_PASSWORD[q]
ON (R)ONE ONLY
OPEN OPTIMIZE (R)OPTIMIZER_COSTS[r] (R) OPTION (R)OPTIONALLY (R)OPTIONS
OR (R)ORDER (R)OUT (R)
OUTER (R)OUTFILE (R)OWNER
PACK_KEYS PAGE PARSER
PARSE_GCOL_EXPR[s]PARTIAL PARTITION (R) PARTITIONING PARTITIONS PASSWORD
PHASE PLUGIN PLUGINS
PLUGIN_DIR POINT POLYGON
PORT PRECEDES[t]PRECISION (R) PREPARE PRESERVE PREV
PRIMARY (R)PRIVILEGES PROCEDURE (R) PROCESSLIST PROFILE PROFILES
PROXY PURGE (R)QUARTER
QUERY QUICK RANGE (R)
READ (R)READS (R)READ_ONLY
READ_WRITE (R)REAL (R)REBUILD
RECOVER REDOFILE REDO_BUFFER_SIZE REDUNDANT REFERENCES (R)REGEXP (R)
RELAY RELAYLOG RELAY_LOG_FILE
RELAY_LOG_POS RELAY_THREAD RELEASE (R)
RELOAD REMOVE RENAME (R)
REORGANIZE REPAIR REPEAT (R)
REPEATABLE REPLACE (R)REPLICATE_DO_DB[u]
REPLICATE_DO_TABLE[v]REPLICATE_IGNORE_DB[w]REPLICATE_IGNORE_TABLE[x] REPLICATE_REWRITE_DB[y]REPLICATE_WILD_DO_TABLE[z]REPLICATE_WILD_IGNORE_TABLE[aa] REPLICATION REQUIRE (R)RESET
RESIGNAL (R)RESTORE RESTRICT (R)
RESUME RETURN (R)RETURNED_SQLSTATE
RETURNS REVERSE REVOKE (R)
RIGHT (R)RLIKE (R)ROLLBACK
ROLLUP ROTATE[ab]ROUTINE
ROW ROWS ROW_COUNT
ROW_FORMAT RTREE SAVEPOINT
SCHEDULE SCHEMA (R)SCHEMAS (R)
SCHEMA_NAME SECOND SECOND_MICROSECOND (R) SECURITY SELECT (R)SENSITIVE (R)
SEPARATOR (R)SERIAL SERIALIZABLE
SERVER SESSION SET (R)
SHARE SHOW (R)SHUTDOWN
SIGNAL (R)SIGNED SIMPLE
SLAVE SLOW SMALLINT (R)
SNAPSHOT SOCKET SOME
SONAME SOUNDS SOURCE
SPATIAL (R)SPECIFIC (R)SQL (R)
SQLEXCEPTION (R)SQLSTATE (R)SQLWARNING (R)
SQL_AFTER_GTIDS SQL_AFTER_MTS_GAPS SQL_BEFORE_GTIDS
SQL_BIG_RESULT (R)SQL_BUFFER_RESULT SQL_CACHE
SQL_CALC_FOUND_ROWS (R)SQL_NO_CACHE SQL_SMALL_RESULT (R)
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 (R)STACKED START
STARTING (R)STARTS STATS_AUTO_RECALC
STATS_PERSISTENT STATS_SAMPLE_PAGES STATUS
STOP STORAGE STORED[ac] (R)
STRAIGHT_JOIN (R)STRING SUBCLASS_ORIGIN
SUBJECT SUBPARTITION SUBPARTITIONS
SUPER SUSPEND SWAPS
SWITCHES TABLE (R)TABLES
TABLESPACE TABLE_CHECKSUM TABLE_NAME
TEMPORARY TEMPTABLE TERMINATED (R)
TEXT THAN THEN (R)
TIME TIMESTAMP TIMESTAMPADD TIMESTAMPDIFF TINYBLOB (R)TINYINT (R)
TINYTEXT (R)TO (R)TRAILING (R)
TRANSACTION TRIGGER (R)TRIGGERS

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。