Detailed explanation of keywords and reserved words in MySQL5.7

Recently, during the process of migrating data from Oracle to MySQL, I encountered some problems, including keywords. The following article mainly introduces you to the relevant information about keywords and reserved words in MySQL 5.7. The introduction in the article is very detailed. Friends who need it can refer to it. Let’s take a look together.

Foreword

The keywords of MySQL and Oracle are still different. In the Oracle database, a large number of code fields are defined in our data tables to represent the primary key, but in MySQL code is the keyword , using the previous treatment methods is somewhat "acclimated".

Let’s take a look at the keywords and reserved words in MySQL.

What are keywords and reserved words

Keywords refer to words that are meaningful in SQL. Certain keywords, such as SELECT, DELETE, or BIGINT, are reserved and require special handling to be used as identifiers such as table and column names. This also applies to the names of built-in functions.

How to use keywords and reserved words

Non-reserved keywords are allowed as identifiers and do not require quotes. If you want to use a reserved word as an identifier, you must use quotation marks.

For example, BEGIN and END are keywords but not reserved words, so they do not require quotes when used as identifiers. INTERVAL is a reserved keyword and must be quoted to be used as an identifier.

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>

We see that the table name in the first statement uses the reserved word interval, and the execution fails;

The second statement adds quotation marks to interval, and the execution succeeds.

It should be noted here that the quotation marks must be backticks, not single quotation marks. Otherwise, an error will be reported as follows:

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>

There is one exception

If the identifier comes after the period of the qualified name (database name), even reserved keywords do not need quotes.

Let’s take the hoegh database as an example. If the table name is written as hoegh.interval, there is no need to put quotation marks around the reserved word 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>

About using built-in function names

Allowed names of built-in functions can be used as identifiers, but it is best to use them with caution. For example, COUNT is legal as a column name. However, by default, no spaces are allowed in function calls between the function name and the following (. This restriction allows the parser to distinguish whether the name is used in a function call or in a non-function context.

Appendix

At some point you may need to upgrade to a higher version, so it's a good idea to take a look at future reserved words. You can find these in the manuals covering later versions of MySQL for most reservations. Words are prohibited in standard SQL as names of columns or tables (e.g., GROUP). Some of these are reserved words because MySQL requires them and uses a yacc parser.

Three tables are listed below:

The first table 10.2 shows the keywords and reserved words in MySQL 5.7. The reserved keywords are marked (R). In addition, _FILENAME is reserved. The second table 10.3 shows the new additions in MySQL 5.7.

The third table 10.4 shows the reserved words deleted in MySQL 5.7 compared to version 5.6

Table 10.2 Keywords and Reserved Words in MySQL 5.7

RIGHT (R)RLIKE (R)ROLLBACKROLLUP R OWROW_FORMATSCHEDULESCHEMA_NAMESECURITY#特定（R）SQLSTATE (R)SQL_BEFORE_GTIDSSQL_CACHESQL_SMALL_RESULT (R)#SQL_TSI_YEAR開始#啟動（R）##STATS_PERSISTENT#停止##暫停#交換表格TABLE_NAME臨時THAN##TIMESTAMPDIFFTINYBLOB (R)TINYTEXT（R）##交易#TRUE（R）未提交UNDO（R）#解鎖（R） ##無符號（R）##使用（R)# #＃＃＃＃＃＃＃＃＃＃ ＃＃＃用戶＃＃ USE_FRMUTC_TIME（R）VALUEVARIABLES虛擬[ae]（R）##警告#哪裡（R）工作#WRITE（ R）年_月(R)

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)

ROTATE[ab]

ROUTINE

ROWS

ROW_COUNT

RTREE

SAVEPOINT

SCHEMAS (R)

SCHEMAS (R)

SECOND

SECOND_MICROSECOND ( R)

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

##。

SQL（R）

SQLEXCEPTION (R)

SQLWARNING (R)

# SQL_AFTER_GTIDS

#SQL_AFTER_MTS_GAPS

# SQL_BIG_RESULT (R)

## SQL_BIG_RESULT (R)

SQL_BUFFER_RESULT

#SQL_CALC_FOUND_ROWS (R)

SQL_NO_CACHE

################################# ### ######SQL_THREAD######SQL_TSI_DAY######SQL_TSI_HOUR############################ # ########################################################### # ########################################################## ### ######################################################## ##### ############################################## ####### ############################################ ######### ########################################## ########### ######################################## ############# ###################################### ############### #########SQL_TSI_MINUTE######SQL_TSI_MONTH######SQL_TSI_QUARTER#################SQL_TSI_QUARTER############ #######

SQL_TSI_SECOND

SQL_TSI_WEEK

#SSL (R)

堆疊

啟動

STATS_AUTO_RECALC

STATS_SAMPLE_PAGES

狀態

儲存

儲存[ac]（R）

#########################STRAIGHT_JOIN（R）######STRING######SUBCLASS_ORIGIN### #### ################################################ ## ########################################################### ########################################################### ## ######################################################### #### ####################################################### ###### ############################################# ######## ########################################### ########## ######################################### ############ ############

主題

子分割區

子分割區

#超級

#開關

表格（R）

表空間

TABLE_CHECKSUM

終止（R）

#TEXT

THEN（R）

########################################################## ##############################TIME######TIMESTAMP######TIMESTAMPADD###### # ########################################################## ### ######################################################## ##### ############################################## ####### ############################################ ######### ########################################## ########### ######################################## ############# ###################################### ############### ########################

TINYINT (R)

TO（R）

尾隨（R）

觸發器（R）

觸發器

截斷

類型

#類型

未定義

UNDOFILE

UNDO_BUFFER_SIZE

########### ### ######################################################## ##### ################################UNICODE######卸載###### UNION (R)# ############################################# ####### ############################################ ######### ########################################## ########### ######################################## ############# ###################################### ############### #################################### ################# #####

##獨特（R）

未知

直到

更新（R）

使用( R)

#USER_RESOURCES

使用（R )

UTC_DATE（R）

UTC_TIMESTAMP ( R)

VALIDATION[ad]

############################################# ### ######################################################## ##### ############################################## ####### ############################################ ######### ############值(R)#######VARBINARY (R)######VARCHAR (R)###### ######### ########################################## ########### ######################################## ############# ###################################### ############### #################################### ################# ##

VARCHARACTER (R)

VARYING (R)

##查看

等待

WEEK

WEIGHT_STRING

何時（R）

WHILE（R）

與（R）

沒有[af]

X509

######################## # ########################################################## ### ######################################################## ##### ############################################## ####### ############################################ ######### ########################################## ########### ######################################## ###########XA #####XID[ag]######XML##################### ############ ############################################### ############## ##################################### ################ ################################### ######

##或(R)

年

## ZEROFILL（R）

[c] 通道：在5.7.6 中新增（非保留）[g] 篩選器：新增在5.7.3 中（非保留）[k] INSTANCE：在5.7.11 中新增（非保留）[o] NEVER：在5.7.4 中加入（非保留）[s] PARSE_GCOL_EXPR：在 5.7.6 中加入（保留）；在5.7.8 中成為非保留[w] REPLICATE_IGNORE_DB：在5.7.3 中加入（非保留）[z] REPLICATE_WILD_DO_TABLE：在5.7.3中加入（非保留）[ad] 驗證：在5.7.5 中加入（非保留）表10.3 新增的關鍵字和保留字在MySQL 5.7 中與MySQL 5.6 比較#ACCOUNTALWAYSCHANNEL

	##[a] 帳號：在5.7.6 中新增（非保留）	[ b]始終：在5.7.6 中新增（非保留）
[d] 壓縮：在5.7.8 中加入（非保留）	[e] 加密：在5.7.11 中新增（非保留）	[f] FILE_BLOCK_SIZE：在5.7.6 中新增（非保留）
[h] FOLLOWS：在5.7.2 中新增（非保留）	[i] GENERATED：在5.7.6 中新增（保留）	[j] GROUP_REPLICATION：在5.7.6 中新增（非保留）
[l] JSON：在5.7.8 中加入（非保留）	[m] MASTER_TLS_VERSION：在5.7.10 中新增（非保留）	#[ n] MAX_STATEMENT_TIME：在5.7.4 中加入（非保留）；在5.7.8
[p] NONBLOCKING：在5.7.6 中刪除	[q ] OLD_PASSWORD ：在 5.7.5 中刪除	[r] OPTIMIZER_COSTS：在 5.7.5 中新增（保留）
[t] PRECEDES：在5.7.2 中加入（非保留）	[u] REPLICATE_DO_DB：在5.7.3 中加入（非保留）	[v] REPLICATE_DO_TABLE：在5.7.3 中加入（非保留）
[x ] REPLICATE_IGNORE_TABLE：在5.7.3 中加入（非保留） )	[y] REPLICATE_REWRITE_DB：在5.7.3 中新增（非保留）	[z] REPLICATE_WILD_DO_TABLE：在5.7.3
[aa] REPLICATE_WILD_IGNORE_TABLE：在5.7.3 中加入（非保留）	[ab] ROTATE：在5.7.11 中新增（非保留）	[ac] STORED：在5.7.6 中加入（保留）
[ae] 虛擬：在5.7.6 中新增（保留）	[af] WITHOUT：在5.7.5 中新增（非保留）	[ag] XID：在5.7.5 中新增（非保留)

# ##壓縮######加密######FILE_BLOCK_SIZE ############過濾器######遵循######產生（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_IGN_TABLE######O_TAB##ICATE_ ####REPLICATE_REWRITE_DB######REPLICATE_WILD_DO_TABLE######REPLICATE_WILD_IGNORE_TABLE############ROTATE######################ROT） ###########驗證######虛擬（R）######沒有##### #######XID###### ## #### ############

表10.4 與MySQL 5.6 相比，MySQL 5.7 中刪除的關鍵字和保留字

OLD_PASSWORD

##總結##

The above is the detailed content of Detailed explanation of keywords and reserved words in MySQL5.7. For more information, please follow other related articles on the PHP Chinese website!