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

最近在将数据从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.7ACCESSIBLE (R)ACCOUNT[a]ACTION

ADD (R)AFTERAGAINST

AGGREGATEALGORITHMALL (R)

ALTER (R)ALWAYS[b]ANALYSE

ANALYZE (R)AND (R)ANY

AS (R)ASC (R)ASCII

ASENSITIVE (R)ATAUTOEXTEND_SIZE

AUTO_INCREMENTAVGAVG_ROW_LENGTH

BACKUPBEFORE (R)BEGIN

BETWEEN (R)BIGINT (R)BINARY (R)

BINLOGBITBLOB (R)

BLOCKBOOLBOOLEAN

BOTH (R)BTREEBY (R)

BYTECACHECALL (R)

CASCADE (R)CASCADEDCASE (R)

CATALOG_NAMECHAINCHANGE (R)

CHANGEDCHANNEL[c]CHAR (R)

CHARACTER (R)CHARSETCHECK (R)

CHECKSUMCIPHERCLASS_ORIGIN

CLIENTCLOSECOALESCE

CODECOLLATE (R)COLLATION

COLUMN (R)COLUMNSCOLUMN_FORMAT

COLUMN_NAMECOMMENTCOMMIT

COMMITTEDCOMPACTCOMPLETION

COMPRESSEDCOMPRESSION[d]CONCURRENT

CONDITION (R)CONNECTIONCONSISTENT

CONSTRAINT (R)CONSTRAINT_CATALOGCONSTRAINT_NAME

CONSTRAINT_SCHEMACONTAINSCONTEXT

CONTINUE (R)CONVERT (R)CPU

CREATE (R)CROSS (R)CUBE

CURRENTCURRENT_DATE (R)CURRENT_TIME (R)

CURRENT_TIMESTAMP (R)CURRENT_USER (R)CURSOR (R)

CURSOR_NAMEDATADATABASE (R)

DATABASES (R)DATAFILEDATE

DATETIMEDAYDAY_HOUR (R)

DAY_MICROSECOND (R)DAY_MINUTE (R)DAY_SECOND (R)

DEALLOCATEDEC (R)DECIMAL (R)

DECLARE (R)DEFAULT (R)DEFAULT_AUTH

DEFINERDELAYED (R)DELAY_KEY_WRITE

DELETE (R)DESC (R)DESCRIBE (R)

DES_KEY_FILEDETERMINISTIC (R)DIAGNOSTICS

DIRECTORYDISABLEDISCARD

DISKDISTINCT (R)DISTINCTROW (R)

p (R)DODOUBLE (R)

DROP (R)DUAL (R)DUMPFILE

DUPLICATEDYNAMICEACH (R)

ELSE (R)ELSEIF (R)ENABLE

ENCLOSED (R)ENCRYPTION[e]END

ENDSENGINEENGINES

ENUMERRORERRORS

ESCAPEESCAPED (R)EVENT

EVENTSEVERYEXCHANGE

EXECUTEEXISTS (R)EXIT (R)

EXPANSIONEXPIREEXPLAIN (R)

EXPORTEXTENDEDEXTENT_SIZE

FALSE (R)FASTFAULTS

FETCH (R)FIELDSFILE

FILE_BLOCK_SIZE[f]FILTER[g]FIRST

FIXEDFLOAT (R)FLOAT4 (R)

FLOAT8 (R)FLUSHFOLLOWS[h]

FOR (R)FORCE (R)FOREIGN (R)

FORMATFOUNDFROM (R)

FULLFULLTEXT (R)FUNCTION

GENERALGENERATED[i] (R)GEOMETRY

GEOMETRYCOLLECTIONGET (R)GET_FORMAT

GLOBALGRANT (R)GRANTS

GROUP (R)GROUP_REPLICATION[j]HANDLER

HASHHAVING (R)HELP

HIGH_PRIORITY (R)HOSTHOSTS

HOURHOUR_MICROSECOND (R)HOUR_MINUTE (R)

HOUR_SECOND (R)IDENTIFIEDIF (R)

IGNORE (R)IGNORE_SERVER_IDSIMPORT

IN (R)INDEX (R)INDEXES

INFILE (R)INITIAL_SIZEINNER (R)

INOUT (R)INSENSITIVE (R)INSERT (R)

INSERT_METHODINSTALLINSTANCE[k]

INT (R)INT1 (R)INT2 (R)

INT3 (R)INT4 (R)INT8 (R)

INTEGER (R)INTERVAL (R)INTO (R)

INVOKERIOIO_AFTER_GTIDS (R)

IO_BEFORE_GTIDS (R)IO_THREADIPC

IS (R)ISOLATIONISSUER

ITERATE (R)JOIN (R)JSON[l]

KEY (R)KEYS (R)KEY_BLOCK_SIZE

KILL (R)LANGUAGELAST

LEADING (R)LEAVE (R)LEAVES

LEFT (R)LESSLEVEL

LIKE (R)LIMIT (R)LINEAR (R)

LINES (R)LINESTRINGLIST

LOAD (R)LOCALLOCALTIME (R)

LOCALTIMESTAMP (R)LOCK (R)LOCKS

LOGFILELOGSLONG (R)

LONGBLOB (R)LONGTEXT (R)LOOP (R)

LOW_PRIORITY (R)MASTERMASTER_AUTO_POSITION

MASTER_BIND (R)MASTER_CONNECT_RETRYMASTER_DELAY

MASTER_HEARTBEAT_PERIODMASTER_HOSTMASTER_LOG_FILE

MASTER_LOG_POSMASTER_PASSWORDMASTER_PORT

MASTER_RETRY_COUNTMASTER_SERVER_IDMASTER_SSL

MASTER_SSL_CAMASTER_SSL_CAPATHMASTER_SSL_CERT

MASTER_SSL_CIPHERMASTER_SSL_CRLMASTER_SSL_CRLPATH

MASTER_SSL_KEYMASTER_SSL_VERIFY_SERVER_CERT(R)MASTER_TLS_VERSION[m]

MASTER_USERMATCH (R)MAXVALUE (R)

MAX_CONNECTIONS_PER_HOURMAX_QUERIES_PER_HOURMAX_ROWS

MAX_SIZEMAX_STATEMENT_TIME[n]MAX_UPDATES_PER_HOUR

MAX_USER_CONNECTIONSMEDIUMMEDIUMBLOB (R)

MEDIUMINT (R)MEDIUMTEXT (R)MEMORY

MERGEMESSAGE_TEXTMICROSECOND

MIDDLEINT (R)MIGRATEMINUTE

MINUTE_MICROSECOND (R)MINUTE_SECOND (R)MIN_ROWS

MOD (R)MODEMODIFIES (R)

MODIFYMONTHMULTILINESTRING

MULTIPOINTMULTIPOLYGONMUTEX

MYSQL_ERRNONAMENAMES

NATIONALNATURAL (R)NCHAR

NDBNDBCLUSTERNEVER[o]

NEWNEXTNO

NODEGROUPNONBLOCKING[p]NONE

NOT (R)NO_WAITNO_WRITE_TO_BINLOG (R)

NULL (R)NUMBERNUMERIC (R)

NVARCHAROFFSETOLD_PASSWORD[q]

ON (R)ONEONLY

OPENOPTIMIZE (R)OPTIMIZER_COSTS[r] (R)

OPTION (R)OPTIONALLY (R)OPTIONS

OR (R)ORDER (R)OUT (R)

OUTER (R)OUTFILE (R)OWNER

PACK_KEYSPAGEPARSER

PARSE_GCOL_EXPR[s]PARTIALPARTITION (R)

PARTITIONINGPARTITIONSPASSWORD

PHASEPLUGINPLUGINS

PLUGIN_DIRPOINTPOLYGON

PORTPRECEDES[t]PRECISION (R)

PREPAREPRESERVEPREV

PRIMARY (R)PRIVILEGESPROCEDURE (R)

PROCESSLISTPROFILEPROFILES

PROXYPURGE (R)QUARTER

QUERYQUICKRANGE (R)

READ (R)READS (R)READ_ONLY

READ_WRITE (R)REAL (R)REBUILD

RECOVERREDOFILEREDO_BUFFER_SIZE

REDUNDANTREFERENCES (R)REGEXP (R)

RELAYRELAYLOGRELAY_LOG_FILE

RELAY_LOG_POSRELAY_THREADRELEASE (R)

RELOADREMOVERENAME (R)

REORGANIZEREPAIRREPEAT (R)

REPEATABLEREPLACE (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]

REPLICATIONREQUIRE (R)RESET

RESIGNAL (R)RESTORERESTRICT (R)

RESUMERETURN (R)RETURNED_SQLSTATE

RETURNSREVERSEREVOKE (R)

RIGHT (R)RLIKE (R)ROLLBACK

ROLLUPROTATE[ab]ROUTINE

ROWROWSROW_COUNT

ROW_FORMATRTREESAVEPOINT

SCHEDULESCHEMA (R)SCHEMAS (R)

SCHEMA_NAMESECONDSECOND_MICROSECOND (R)

SECURITYSELECT (R)SENSITIVE (R)

SEPARATOR (R)SERIALSERIALIZABLE

SERVERSESSIONSET (R)

SHARESHOW (R)SHUTDOWN

SIGNAL (R)SIGNEDSIMPLE

SLAVESLOWSMALLINT (R)

SNAPSHOTSOCKETSOME

SONAMESOUNDSSOURCE

SPATIAL (R)SPECIFIC (R)SQL (R)

SQLEXCEPTION (R)SQLSTATE (R)SQLWARNING (R)

SQL_AFTER_GTIDSSQL_AFTER_MTS_GAPSSQL_BEFORE_GTIDS

SQL_BIG_RESULT (R)SQL_BUFFER_RESULTSQL_CACHE

SQL_CALC_FOUND_ROWS (R)SQL_NO_CACHESQL_SMALL_RESULT (R)

SQL_THREADSQL_TSI_DAYSQL_TSI_HOUR

SQL_TSI_MINUTESQL_TSI_MONTHSQL_TSI_QUARTER

SQL_TSI_SECONDSQL_TSI_WEEKSQL_TSI_YEAR

SSL (R)STACKEDSTART

STARTING (R)STARTSSTATS_AUTO_RECALC

STATS_PERSISTENTSTATS_SAMPLE_PAGESSTATUS

STOPSTORAGESTORED[ac] (R)

STRAIGHT_JOIN (R)STRINGSUBCLASS_ORIGIN

SUBJECTSUBPARTITIONSUBPARTITIONS

SUPERSUSPENDSWAPS

SWITCHESTABLE (R)TABLES

TABLESPACETABLE_CHECKSUMTABLE_NAME

TEMPORARYTEMPTABLETERMINATED (R)

TEXTTHANTHEN (R)

TIMETIMESTAMPTIMESTAMPADD

TIMESTAMPDIFFTINYBLOB (R)TINYINT (R)

TINYTEXT (R)TO (R)TRAILING (R)

TRANSACTIONTRIGGER (R)TRIGGERS

TRUE (R)TRUNCATETYPE

TYPESUNCOMMITTEDUNDEFINED

UNDO (R)UNDOFILEUNDO_BUFFER_SIZE

UNICODEUNINSTALLUNION (R)

UNIQUE (R)UNKNOWNUNLOCK (R)

UNSIGNED (R)UNTILUPDATE (R)

UPGRADEUSAGE (R)USE (R)

USERUSER_RESOURCESUSE_FRM

USING (R)UTC_DATE (R)UTC_TIME (R)

UTC_TIMESTAMP (R)VALIDATION[ad]VALUE

VALUES (R)VARBINARY (R)VARCHAR (R)

VARCHARACTER (R)VARIABLESVARYING (R)

VIEWVIRTUAL[ae] (R)WAIT

WARNINGSWEEKWEIGHT_STRING

WHEN (R)WHERE (R)WHILE (R)

WITH (R)WITHOUT[af]WORK

WRAPPERWRITE (R)X509

XAXID[ag]XML

XOR (R)YEARYEAR_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.6ACCOUNTALWAYSCHANNEL

COMPRESSIONENCRYPTIONFILE_BLOCK_SIZE

FILTERFOLLOWSGENERATED (R)

GROUP_REPLICATIONINSTANCEJSON

MASTER_TLS_VERSIONNEVEROPTIMIZER_COSTS (R)

PARSE_GCOL_EXPRPRECEDESREPLICATE_DO_DB

REPLICATE_DO_TABLEREPLICATE_IGNORE_DBREPLICATE_IGNORE_TABLE

REPLICATE_REWRITE_DBREPLICATE_WILD_DO_TABLEREPLICATE_WILD_IGNORE_TABLE

ROTATESTACKEDSTORED (R)

VALIDATIONVIRTUAL (R)WITHOUT

XID

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

总结