主頁 > 知識庫 > MySQL 5.7中的關(guān)鍵字與保留字詳解

MySQL 5.7中的關(guān)鍵字與保留字詳解

前言

MySQL和Oracle的關(guān)鍵字還是不盡相同的，在Oracle數(shù)據(jù)庫中，我們的數(shù)據(jù)表中定義了大量的code字段用來表示主鍵，但是在MySQL中code是關(guān)鍵字，使用以前的處理方法就有些“水土不服”。

下面我們來了解一下MySQL中的關(guān)鍵字和保留字。

什么是關(guān)鍵字和保留字

關(guān)鍵字是指在SQL中有意義的字。某些關(guān)鍵字（例如SELECT，DELETE或BIGINT）是保留的，需要特殊處理才能用作表和列名稱等標(biāo)識符。這一點對于內(nèi)置函數(shù)的名稱也適用。

如何使用關(guān)鍵字和保留字

非保留關(guān)鍵字允許作為標(biāo)識符，不需要加引號。如果您要適用保留字作為標(biāo)識符，就必須適用引號。

舉個例子，BEGIN和END是關(guān)鍵字，但不是保留字，因此它們用作標(biāo)識符不需要引號。 INTERVAL是保留關(guān)鍵字，必須加上引號才能用作標(biāo)識符。

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，執(zhí)行失敗；

第二條語句對interval加了引號，執(zhí)行成功。

在這里需要注意的是，引號必須是反引號，而非單引號。否則會報錯，如下所示：

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>

有一個例外

如果標(biāo)識符在限定名稱（數(shù)據(jù)庫名）的句點之后，即使是保留關(guān)鍵字也不需要引號。

我們以hoegh數(shù)據(jù)庫為例，如果表名寫為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>

關(guān)于使用內(nèi)置函數(shù)名稱

允許內(nèi)置函數(shù)的名稱可以作為標(biāo)識符，但最好謹(jǐn)慎使用。例如，COUNT作為列名稱是合法的。但是，默認(rèn)情況下，在函數(shù)名和后面的（之間的函數(shù)調(diào)用中不允許有空格。這個限制使解析器能夠區(qū)分名稱是用于函數(shù)調(diào)用還是用在非函數(shù)上下文中。

附錄

在某些時候，您可能需要升級到更高版本，因此最好查看一下未來的保留字。您可以在涵蓋更高版本的MySQL的手冊中找到這些。對于表中的大多數(shù)保留字，在標(biāo)準(zhǔn)SQL中禁止作為列或表的名稱（例如，GROUP）。其中一些保留字，是由于MySQL需要它們并使用一個yacc解析器。

以下列出三張表格：

第一個表格10.2顯示MySQL 5.7中的關(guān)鍵字和保留字。保留的關(guān)鍵字標(biāo)記為（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)

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

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)

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_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

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

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)

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

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

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章:

Python處理mysql特殊字符的問題
Python3連接Mysql8.0遇到的問題及處理步驟
python mysql 字段與關(guān)鍵字沖突的解決方式

標(biāo)簽：湘潭崇左銅川黃山仙桃湖南衡水蘭州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL 5.7中的關(guān)鍵字與保留字詳解》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。