数据库字段命名导致的SQL报错

数据库字段命名导致的SQL报错

1.表设计 

create table variables
(id                     bigint       not null comment '主键',business_key           varchar(128) null comment '业务key',`key`                  varchar(128) null comment 'Map中的key',value                  varchar(255) null comment 'Map中的value',data_type              varchar(32)  null comment '数据类型',created                datetime     null comment '创建时间',modified               datetime     null comment '修改时间',yn                     int          null comment '数据是否有效，1：有效，0：无效'
)comment '自定义变量表';

2.报错

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: ptions.jdbc4.MySQLSyntaxErrorException: vtgate: xxxte-xx3b-xrxdxxe-4p2cf:12301/: target: test_db.0.master, used tablet: xx3b-11225725 (1x): vttablet: 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 'key,value,data_type,created,modified,yn) values  (  100021,null,'xx' at line 1 (errno 1064) (sqlstate 42000) (CallerID: ): Sql: "/* uag::test_db;1x.1x.1xx.1xx:16xx9;1x.2x.3x1.2x4:1xxx24;1x.x5.1xx.60:31x28;enable */  insert into variables (id,business_key,key,value,data_type,created,modified,yn) values  (  100021,null,'orderTime','1691579920000','Long','2023-08-09 19:21:50','2023-08-09 19:21:50',1 )

..........

..........

### The error may exist in xxx/core/dao/mapper/xxx/VariablesMapper.java (best guess)
### The error may dao.mapper.VariablesMapper.insertBatch-Inline
### The error occurred while setting parameters
### SQL: insert into variables (id,key,value,data_type,created,modified,yn) values  (  ?,?,?,?,?,?,? ),( ?,?,?,?,?,?,? )
### Cause: ptions.jdbc4.MySQLSyntaxErrorException: vtgate: xxxte-xx3b-xrxdxxe-4p2cf:12301/: target: test_db.0.master, used tablet: xxx: vttablet: 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 'key,value,data_type,created,modified,yn) values  (  100021,null,'orderTime' at line 1 (errno 1064) (sqlstate 42000) (CallerID: ): 

3.报错原因分析：

找了半天发现也没有啥错，最后通过浏览大量文档，决定本地调试下看看是不是字段名称与关键字冲突导致的，改了字段名称后立马好使了。

key这个字段与MySQL中的关键字有冲突，将key改为其它名称，比如data_key就可以了。