欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

详解MySQL5.7中的关键字与保留字

程序员文章站 2022-04-19 21:11:52
...
最近在将数据从Oracle迁移到MySQL的过程中,遇到一些问题,其中就包括关键字。下面这篇文章主要给大家介绍了MySQL 5.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














































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)














































p (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

总结

以上就是详解MySQL5.7中的关键字与保留字的详细内容,更多请关注其它相关文章!