MySql 5.7关键字和保留字-附表

Table of Contents

转载自:https://www.cnblogs.com/Z-Fanghan/p/6892944.html

现在使用navicat图形界面或者Hibernate做映射生成表的时候,渐渐的会忽视掉关键字这个问题,而后续也会不断的产生错误提示,一遍遍的查询代码无果,甚至开始怀疑人生,但是其实很多情况下只是使用了保留字而已,因此在设计数据库之初便要尽量的规避关键字和保留字。

但是这里要提一下的是 mysql是支持使用关键字做字段名的,但是针对保留关键字是必须要加引用。 mysql官网提供了三个例子是可以很好的说明这些的。

interval begin、end都是关键字,interval是保留关键字,因此会报错


mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

当给interval加上引用''后便可以正确执行该sql语句,也不会报begin、end的错,因为这是mysql允许的,但是不推荐这个用法,谁知道升级之后会不会变成保留字。。。。。。

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

(这个可以略过,,,,,反正不推荐使用关键字做这些事,)还有一种情况——内置函数的名字,官网是这样说的:

Names of built-in functions are permitted as identifiers but may require care to be used as such

mysql> CREATE TABLE mydb.interval (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

下面是最新版的mysql的关键字和保留字的表,可以在设计时多查一查, (R)是保留关键字

有些人可能还不明白关键字和保留字的区别,简单的说,关键字分两种:非保留和保留,保留关键字又有一个特殊类别叫未来保留。

mysql5.7官方文档地址:https://dev.mysql.com/doc/refman/5.7/en/keywords.html

Table 9.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)
DIV (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)

5.7相比于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| |

5.7相比于5.6删除的关键字

OLD_PASSWORD| |

---|---|---

0 评论

发表评论

精品游戏◆乐于分享


Title