使用Jpa操作数据库时,如果字段名称使用了数据库保留关键字,会报错:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option, state, id) values ('2017-09-18 16:50:53.477', '0', '2017-09-18 16:50:53.' at line 1
然后,我到mysql官网查看了一下保留关键字都有(带(R)标识的是保留关键字),我们在创建表的时候,字段名称表名称注意避免。
Table 9.2 Keywords and Reserved Words in MySQL 5.5
ACCESSIBLE (R) | ACTION | ADD (R) |
AFTER | AGAINST | AGGREGATE |
ALGORITHM | ALL (R) | ALTER (R) |
ANALYZE (R) | AND (R) | ANY |
AS (R) | ASC (R) | ASCII |
ASENSITIVE (R) | AT | AUTHORS |
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 | CHAR (R) |
CHARACTER (R) | CHARSET | CHECK (R) |
CHECKSUM | CIPHER | CLASS_ORIGIN |
CLIENT | CLOSE | COALESCE |
CODE | COLLATE (R) | COLLATION |
COLUMN (R) | COLUMNS | COLUMN_NAME |
COMMENT | COMMIT | COMMITTED |
COMPACT | COMPLETION | COMPRESSED |
CONCURRENT | CONDITION (R) | CONNECTION |
CONSISTENT | CONSTRAINT (R) | CONSTRAINT_CATALOG |
CONSTRAINT_NAME | CONSTRAINT_SCHEMA | CONTAINS |
CONTEXT | CONTINUE (R) | CONTRIBUTORS |
CONVERT (R) | CPU | CREATE (R) |
CROSS (R) | CUBE | 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) |
DEFINER | DELAYED (R) | DELAY_KEY_WRITE |
DELETE (R) | DESC (R) | DESCRIBE (R) |
DES_KEY_FILE | DETERMINISTIC (R) | 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) |
END | ENDS | ENGINE |
ENGINES | ENUM | ERROR [a] |
ERRORS | ESCAPE | ESCAPED (R) |
EVENT | EVENTS | EVERY |
EXECUTE | EXISTS (R) | EXIT (R) |
EXPANSION | EXPLAIN (R) | EXTENDED |
EXTENT_SIZE | FALSE (R) | FAST |
FAULTS | FETCH (R) | FIELDS |
FILE | FIRST | FIXED |
FLOAT (R) | FLOAT4 (R) | FLOAT8 (R) |
FLUSH | FOR (R) | FORCE (R) |
FOREIGN (R) | FOUND | FRAC_SECOND [b] |
FROM (R) | FULL | FULLTEXT (R) |
FUNCTION | GENERAL [c] | GEOMETRY |
GEOMETRYCOLLECTION | GET_FORMAT | GLOBAL |
GRANT (R) | GRANTS | GROUP (R) |
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 [d] |
IMPORT | IN (R) | INDEX (R) |
INDEXES | INFILE (R) | INITIAL_SIZE |
INNER (R) | INNOBASE [e] | INNODB [f] |
INOUT (R) | INSENSITIVE (R) | INSERT (R) |
INSERT_METHOD | INSTALL | INT (R) |
INT1 (R) | INT2 (R) | INT3 (R) |
INT4 (R) | INT8 (R) | INTEGER (R) |
INTERVAL (R) | INTO (R) | INVOKER |
IO | IO_THREAD | IPC |
IS (R) | ISOLATION | ISSUER |
ITERATE (R) | JOIN (R) | 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_CONNECT_RETRY | MASTER_HEARTBEAT_PERIOD [g] |
MASTER_HOST | MASTER_LOG_FILE | MASTER_LOG_POS |
MASTER_PASSWORD | MASTER_PORT | MASTER_SERVER_ID |
MASTER_SSL | MASTER_SSL_CA | MASTER_SSL_CAPATH |
MASTER_SSL_CERT | MASTER_SSL_CIPHER | MASTER_SSL_KEY |
MASTER_SSL_VERIFY_SERVER_CERT (R) | MASTER_USER | MATCH (R) |
MAXVALUE (R) | MAX_CONNECTIONS_PER_HOUR | MAX_QUERIES_PER_HOUR |
MAX_ROWS | MAX_SIZE | 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 | NEW |
NEXT | NO | NODEGROUP |
NONE | NOT (R) | NO_WAIT |
NO_WRITE_TO_BINLOG (R) | NULL (R) | NUMERIC (R) |
NVARCHAR | OFFSET | OLD_PASSWORD |
ON (R) | ONE | ONE_SHOT |
OPEN | OPTIMIZE (R) | OPTION (R) |
OPTIONALLY (R) | OPTIONS | OR (R) |
ORDER (R) | OUT (R) | OUTER (R) |
OUTFILE (R) | OWNER | PACK_KEYS |
PAGE | PARSER | PARTIAL |
PARTITION | PARTITIONING | PARTITIONS |
PASSWORD | PHASE | PLUGIN |
PLUGINS | POINT | POLYGON |
PORT | PRECISION (R) | PREPARE |
PRESERVE | PREV | PRIMARY (R) |
PRIVILEGES | PROCEDURE (R) | PROCESSLIST |
PROFILE | PROFILES | PROXY [h] |
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 [i] |
RELAYLOG | RELAY_LOG_FILE | RELAY_LOG_POS |
RELAY_THREAD | RELEASE (R) | RELOAD |
REMOVE | RENAME (R) | REORGANIZE |
REPAIR | REPEAT (R) | REPEATABLE |
REPLACE (R) | REPLICATION | REQUIRE (R) |
RESET | RESIGNAL (R) | RESTORE |
RESTRICT (R) | RESUME | RETURN (R) |
RETURNS | REVOKE (R) | RIGHT (R) |
RLIKE (R) | ROLLBACK | ROLLUP |
ROUTINE | ROW | ROWS |
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 [j] | SMALLINT (R) |
SNAPSHOT | SOCKET | SOME |
SONAME | SOUNDS | SOURCE |
SPATIAL (R) | SPECIFIC (R) | SQL (R) |
SQLEXCEPTION (R) | SQLSTATE (R) | SQLWARNING (R) |
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_FRAC_SECOND [k] |
SQL_TSI_HOUR | SQL_TSI_MINUTE | SQL_TSI_MONTH |
SQL_TSI_QUARTER | SQL_TSI_SECOND | SQL_TSI_WEEK |
SQL_TSI_YEAR | SSL (R) | START |
STARTING (R) | STARTS | STATUS |
STOP | STORAGE | 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) |
VALUE | VALUES (R) | VARBINARY (R) |
VARCHAR (R) | VARCHARACTER (R) | VARIABLES |
VARYING (R) | VIEW | WAIT |
WARNINGS | WEEK | WHEN (R) |
WHERE (R) | WHILE (R) | WITH (R) |
WORK | WRAPPER | WRITE (R) |
X509 | XA | XML |
XOR (R) | YEAR | YEAR_MONTH (R) |
ZEROFILL (R) |
[a] ERROR
: added in 5.5.3 (nonreserved)
[b] FRAC_SECOND
: removed in 5.5.3
[c] GENERAL
: added in 5.5.3 (reserved); became nonreserved in 5.5.8
[d] IGNORE_SERVER_IDS
: became nonreserved in 5.5.8
[e] INNOBASE
: removed in 5.5.3
[f] INNODB
: removed in 5.5.3
[g] MASTER_HEARTBEAT_PERIOD
: became nonreserved in 5.5.8
[h] PROXY
: added in 5.5.7 (nonreserved)
[i] RELAY
: added in 5.5.3 (nonreserved)
[j] SLOW
: added in 5.5.3 (reserved); became nonreserved in 5.5.8
[k] SQL_TSI_FRAC_SECOND
: removed in 5.5.3