MySQL 5.7中的关键字与保留字详解

数据库  /  管理员 发布于 6年前   244

前言

MySQL和Oracle的关键字还是不尽相同的，在Oracle数据库中，我们的数据表中定义了大量的code字段用来表示主键，但是在MySQL中code是关键字，使用以前的处理方法就有些“水土不服”。

下面我们来了解一下MySQL中的关键字和保留字。

什么是关键字和保留字

关键字是指在SQL中有意义的字。 某些关键字（例如SELECT，DELETE或BIGINT）是保留的，需要特殊处理才能用作表和列名称等标识符。 这一点对于内置函数的名称也适用。

如何使用关键字和保留字

非保留关键字允许作为标识符，不需要加引号。 如果您要适用保留字作为标识符，就必须适用引号。

举个例子，BEGIN和END是关键字，但不是保留字，因此它们用作标识符不需要引号。 INTERVAL是保留关键字，必须加上引号才能用作标识符。

mysql>mysql> use hoegh;Database changedmysql>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
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_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(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
TRUE (R)	TRUNCATE	TYPE
TYPES	UNCOMMITTED	UNDEFINED
UNDO (R)	UNDOFILE	UNDO_BUFFER_SIZE
UNICODE	UNINSTALL	UNION (R)
UNIQUE (R)	UNKNOWN	UNLOCK (R)
UNSIGNED (R)	UNTIL	UPDATE (R)
UPGRADE	USAGE (R)	USE (R)
USER	USER_RESOURCES	USE_FRM
USING (R)	UTC_DATE (R)	UTC_TIME (R)
UTC_TIMESTAMP (R)	VALIDATION[ad]	VALUE
VALUES (R)	VARBINARY (R)	VARCHAR (R)
VARCHARACTER (R)	VARIABLES	VARYING (R)
VIEW	VIRTUAL[ae] (R)	WAIT
WARNINGS	WEEK	WEIGHT_STRING
WHEN (R)	WHERE (R)	WHILE (R)
WITH (R)	WITHOUT[af]	WORK
WRAPPER	WRITE (R)	X509
XA	XID[ag]	XML
XOR (R)	YEAR	YEAR_MONTH (R)
ZEROFILL (R)
[a] ACCOUNT: added in 5.7.6 (nonreserved) [b] ALWAYS: added in 5.7.6 (nonreserved) [c] CHANNEL: added in 5.7.6 (nonreserved) [d] COMPRESSION: added in 5.7.8 (nonreserved) [e] ENCRYPTION: added in 5.7.11 (nonreserved) [f] FILE_BLOCK_SIZE: added in 5.7.6 (nonreserved) [g] FILTER: added in 5.7.3 (nonreserved) [h] FOLLOWS: added in 5.7.2 (nonreserved) [i] GENERATED: added in 5.7.6 (reserved) [j] GROUP_REPLICATION: added in 5.7.6 (nonreserved) [k] INSTANCE: added in 5.7.11 (nonreserved) [l] JSON: added in 5.7.8 (nonreserved) [m] MASTER_TLS_VERSION: added in 5.7.10 (nonreserved) [n] MAX_STATEMENT_TIME: added in 5.7.4 (nonreserved); removed in 5.7.8 [o] NEVER: added in 5.7.4 (nonreserved) [p] NONBLOCKING: removed in 5.7.6 [q] OLD_PASSWORD: removed in 5.7.5 [r] OPTIMIZER_COSTS: added in 5.7.5 (reserved) [s] PARSE_GCOL_EXPR: added in 5.7.6 (reserved); became nonreserved in 5.7.8 [t] PRECEDES: added in 5.7.2 (nonreserved) [u] REPLICATE_DO_DB: added in 5.7.3 (nonreserved) [v] REPLICATE_DO_TABLE: added in 5.7.3 (nonreserved) [w] REPLICATE_IGNORE_DB: added in 5.7.3 (nonreserved) [x] REPLICATE_IGNORE_TABLE: added in 5.7.3 (nonreserved) [y] REPLICATE_REWRITE_DB: added in 5.7.3 (nonreserved) [z] REPLICATE_WILD_DO_TABLE: added in 5.7.3 (nonreserved) [aa] REPLICATE_WILD_IGNORE_TABLE: added in 5.7.3 (nonreserved) [ab] ROTATE: added in 5.7.11 (nonreserved) [ac] STORED: added in 5.7.6 (reserved) [ad] VALIDATION: added in 5.7.5 (nonreserved) [ae] VIRTUAL: added in 5.7.6 (reserved) [af] WITHOUT: added in 5.7.5 (nonreserved) [ag] XID: added in 5.7.5 (nonreserved)

Table 10.3 Keywords and Reserved Words Added in MySQL 5.7 Compared to MySQL 5.6

ACCOUNT	ALWAYS	CHANNEL
COMPRESSION	ENCRYPTION	FILE_BLOCK_SIZE
FILTER	FOLLOWS	GENERATED (R)
GROUP_REPLICATION	INSTANCE	JSON
MASTER_TLS_VERSION	NEVER	OPTIMIZER_COSTS (R)
PARSE_GCOL_EXPR	PRECEDES	REPLICATE_DO_DB
REPLICATE_DO_TABLE	REPLICATE_IGNORE_DB	REPLICATE_IGNORE_TABLE
REPLICATE_REWRITE_DB	REPLICATE_WILD_DO_TABLE	REPLICATE_WILD_IGNORE_TABLE
ROTATE	STACKED	STORED (R)
VALIDATION	VIRTUAL (R)	WITHOUT
XID

Table 10.4 Keywords and Reserved Words Removed in MySQL 5.7 Compared to MySQL 5.6

OLD_PASSWORD

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。