Home > Database > Mysql Tutorial > body text

Detailed explanation of keywords and reserved words in MySQL5.7

黄舟
Release: 2017-03-27 13:51:12
Original
1879 people have browsed it

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>
Copy after login

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>
Copy after login

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!