Mariadb 关键字导致 sql 语法错误

2023-06-21

有个报错, sql语法怎么都看不出问题, 而且这sql是mybatis从java代码自动生成的, 不应该有问题.

SELECT  id,method,success,retry_time,topic,partition,offset,kafka_record,parameters,failed_message,extra_json,create_time,update_time  FROM security_retry_task ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'partition,offset,kafka_record,parameters,failed_message,extra_json,create_tim...' at line 1

表结构, 几个字段看起来都有包含在内

MariaDB [data_security]> desc security_retry_task;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| method         | varchar(256)        | NO   | MUL | NULL    |                |
| health         | int(10)             | YES  |     | NULL    |                |
| retry_time     | int(11)             | YES  |     | NULL    |                |
| topic          | varchar(128)        | NO   |     | NULL    |                |
| partition      | int(11)             | YES  |     | NULL    |                |
| offset         | mediumtext          | YES  |     | NULL    |                |
| kafka_record   | mediumtext          | YES  |     | NULL    |                |
| parameters     | mediumtext          | YES  |     | NULL    |                |
| failed_message | varchar(500)        | YES  |     | NULL    |                |
| extra_json     | mediumtext          | YES  |     | NULL    |                |
| create_time    | bigint(20) unsigned | NO   |     | NULL    |                |
| update_time    | bigint(20) unsigned | NO   |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+
13 rows in set (0.001 sec)

其他简单的查询没有问题, 语法角度真看不出为什么会报错了, 都开始怀疑是不是mariadb有什么特殊语法了.

MariaDB [data_security]> select * from security_retry_task;
Empty set (0.000 sec)

MariaDB [data_security]> select id from security_retry_task;
Empty set (0.000 sec)

扔到chatgpt里咨询, 分分钟就得到正确答案, 原因是有几个字段是mariadb/mysql里的关键字, 搬砖的世界真的变了.