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 评论