关于mysql字段名和保留字冲突的问题
建了个表，有个字段起名为key，结果insert语句报错了，说是sql不对。建表语句如下：CREATE TABLE `base_error_message_test` ( `message_id` varchar(50) NOT NULL, `message` text , `send_count` int(2) DEFAULT '0' , `status`
建了个表，有个字段起名为key，结果insert语句报错了，说是sql不对。
建表语句如下：
CREATE TABLE `base_error_message_test` (
`message_id` varchar(50) NOT NULL,
`message` text ,
`send_count` int(2) DEFAULT '0' ,
`status` int(2) DEFAULT '0' ,
`last_modified_date` datetime DEFAULT NULL ,
`creation_date` datetime DEFAULT NULL ,
`topic` varchar(50) DEFAULT NULL,
`key` varchar(50) DEFAULT NULL,
PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='推送失败消息记录表';
以下语句是不对的：
select key from base_error_message_test;
INSERT INTO base_error_message_test (message_id, message, send_count, status, last_modified_date, creation_date, topic, key) VALUES (.......);
原因：字段key和mysql的保留字冲突了，当mysql的字段名和保留字冲突的时候，sql语句中的字段名需要加上反引号``来加以区别，反引号可以用Esc键下面那个按键在英文模式不按shift键打出来，注意，是反引号不是单引号，回车键左边那个是单引号。
所以上面两个语句可以这样写：
select `key` from base_error_message_test;
INSERT INTO base_error_message_test (message_id, message, send_count, status, last_modified_date, creation_date, topic, `key`) VALUES (.......);
另外附上mysql保留字列表：
| Reserved Words in MySQL 5.6.23 |
|
|
| ACCESSIBLE | ADD | ALL |
| ALTER | ANALYZE | AND |
| AS | ASC | ASENSITIVE |
| BEFORE | BETWEEN | BIGINT |
| BINARY | BLOB | BOTH |
| BY | CALL | CASCADE |
| CASE | CHANGE | CHAR |
| CHARACTER | CHECK | COLLATE |
| COLUMN | CONDITION | CONSTRAINT |
| CONTINUE | CONVERT | CREATE |
| CROSS | CURRENT_DATE | CURRENT_TIME |
| CURRENT_TIMESTAMP | CURRENT_USER | CURSOR |
| DATABASE | DATABASES | DAY_HOUR |
| DAY_MICROSECOND | DAY_MINUTE | DAY_SECOND |
| DEC | DECIMAL | DECLARE |
| DEFAULT | DELAYED | DELETE |
| DESC | DESCRIBE | DETERMINISTIC |
| DISTINCT | DISTINCTROW | DIV |
| DOUBLE | DROP | DUAL |
| EACH | ELSE | ELSEIF |
| ENCLOSED | ESCAPED | EXISTS |
| EXIT | EXPLAIN | FALSE |
| FETCH | FLOAT | FLOAT4 |
| FLOAT8 | FOR | FORCE |
| FOREIGN | FROM | FULLTEXT |
| GET | GRANT | GROUP |
| HAVING | HIGH_PRIORITY | HOUR_MICROSECOND |
| HOUR_MINUTE | HOUR_SECOND | IF |
| IGNORE | IN | INDEX |
| INFILE | INNER | INOUT |
| INSENSITIVE | INSERT | INT |
| INT1 | INT2 | INT3 |
| INT4 | INT8 | INTEGER |
| INTERVAL | INTO | IO_AFTER_GTIDS |
| IO_BEFORE_GTIDS | IS | ITERATE |
| JOIN | KEY | KEYS |
| KILL | LEADING | LEAVE |
| LEFT | LIKE | LIMIT |
| LINEAR | LINES | LOAD |
| LOCALTIME | LOCALTIMESTAMP | LOCK |
| LONG | LONGBLOB | LONGTEXT |
| LOOP | LOW_PRIORITY | MASTER_BIND |
| MASTER_SSL_VERIFY_SERVER_CERT | MATCH | MAXVALUE |
| MEDIUMBLOB | MEDIUMINT | MEDIUMTEXT |
| MIDDLEINT | MINUTE_MICROSECOND | MINUTE_SECOND |
| MOD | MODIFIES | NATURAL |
| NOT | NO_WRITE_TO_BINLOG | NULL |
| NUMERIC | ON | OPTIMIZE |
| OPTION | OPTIONALLY | OR |
| ORDER | OUT | OUTER |
| OUTFILE | PARTITION | PRECISION |
| PRIMARY | PROCEDURE | PURGE |
| RANGE | READ | READS |
| READ_WRITE | REAL | REFERENCES |
| REGEXP | RELEASE | RENAME |
| REPEAT | REPLACE | REQUIRE |
| RESIGNAL | RESTRICT | RETURN |
| REVOKE | RIGHT | RLIKE |
| SCHEMA | SCHEMAS | SECOND_MICROSECOND |
| SELECT | SENSITIVE | SEPARATOR |
| SET | SHOW | SIGNAL |
| SMALLINT | SPATIAL | SPECIFIC |
| SQL | SQLEXCEPTION | SQLSTATE |
| SQLWARNING | SQL_BIG_RESULT | SQL_CALC_FOUND_ROWS |
| SQL_SMALL_RESULT | SSL | STARTING |
| STRAIGHT_JOIN | TABLE | TERMINATED |
| THEN | TINYBLOB | TINYINT |
| TINYTEXT | TO | TRAILING |
| TRIGGER | TRUE | UNDO |
| UNION | UNIQUE | UNLOCK |
| UNSIGNED | UPDATE | USAGE |
| USE | USING | UTC_DATE |
| UTC_TIME | UTC_TIMESTAMP | VALUES |
| VARBINARY | VARCHAR | VARCHARACTER |
| VARYING | WHEN | WHERE |
| WHILE | WITH | WRITE |
| XOR | YEAR_MONTH | ZEROFILL |
| NewReserved Words in MySQL 5.6 |
|
|
| GET | IO_AFTER_GTIDS | IO_BEFORE_GTIDS |
| MASTER_BIND | ONE_SHOT | PARTITION |
| SQL_AFTER_GTIDS | SQL_BEFORE_GTIDS |
|
无需安装部署，在线快速体验 Byzer
更多推荐
2
0- 0
已为社区贡献7条内容
所有评论(0)