在使用Mysql5.7插入数据因为关键字和保留字遇到的的坑

最新推荐文章于 2022-12-20 20:57:43 发布

RemainderTime

最新推荐文章于 2022-12-20 20:57:43 发布

阅读量1.5k

点赞数

文章标签： mysql关键字

本文链接：https://blog.csdn.net/qq_39818325/article/details/85258501

版权

java知识小点心同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

遇到的坑

在进行数据的插入的时候总是数据插不进数据库，页面提交表单的时候，页面一直处于请求中，但就是不跳转页面，项目就开始弹出是否进行debug操作，检测了许久，逻辑代码和sql语句都没有问题，实在是让自己感到难受，在网上查询了许多的资料，才发现自己的字段使用的是数据库的保留关键字

在网上查阅资料整理的部分内容解释

关于mysql5.7保留关键字和关键字，mysql是支持使用关键字做字段名的（但不推荐使用），但是针对保留关键字是必须要加引用

我自己使用到的是保留关键字 describe 和 require 在插入数据库的时候字段需要加上 ' ' 或者修改字段名字，我自己是修改了字段的名字

下面是最新版的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`