解决MySQL 中使用index desc 作为字段名时Mybatis-Plus操作出现的问题

解决方法

•    再有关键字实体类属性上添加 @TableField("`表的列名`") 重点是  “`”这个符号
  

•   # 不加的语句
    INSERT INTO test  ( index, desc )  VALUES  ( ?, ? );
    # 加的语句
    INSERT INTO test ( `index`, `desc` ) VALUES ( ?, ? ) 
  

结果

1. 测试类

   @Autowired
   TestMapper mapper;
   @Test
   void test(){
       com.book.yomance.entity.Test test = new com.book.yomance.entity.Test();
       test.setDesc("描述")
           .setIndex(1);
       mapper.insert(test);
       System.out.println(mapper.selectById(test.getId()));
   }
   

2. 实体类

   public class Test {
       @TableField("`index`")
       private Integer index;
       @TableField("`desc`")
       private String desc;
       @TableId(type = IdType.AUTO)
       private Integer id;
       // 省略get set
   }
   

3. 结果

   Creating a new SqlSession
   SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@51f95f0d] was not registered for synchronization because synchronization is not active
   2021-02-27 16:53:36.436  INFO 21164 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
   2021-02-27 16:53:36.663  INFO 21164 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
   JDBC Connection [HikariProxyConnection@1576526619 wrapping com.mysql.cj.jdbc.ConnectionImpl@63a9c661] will not be managed by Spring
   ==>  Preparing: INSERT INTO test ( `index`, `desc` ) VALUES ( ?, ? ) 
   ==> Parameters: 1(Integer), 描述(String)
   <==    Updates: 1
   Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@51f95f0d]
   Creating a new SqlSession
   SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@149c39b] was not registered for synchronization because synchronization is not active
   JDBC Connection [HikariProxyConnection@816760435 wrapping com.mysql.cj.jdbc.ConnectionImpl@63a9c661] will not be managed by Spring
   ==>  Preparing: SELECT id,`index`,`desc` FROM test WHERE id=? 
   ==> Parameters: 1(Integer)
   <==    Columns: id, index, desc
   <==        Row: 1, 1, 描述
   <==      Total: 1
   Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@149c39b]
   Test{index=1, desc='描述', id=1}
   

   

报错情况

```sql
org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 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 'index,
desc )  VALUES  ( 1,
'描述' )' at line 1
### The error may exist in com/book/yomance/mapper/TestMapper.java (best guess)
### The error may involve com.book.yomance.mapper.TestMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO test  ( index, desc )  VALUES  ( ?, ? )
### Cause: java.sql.SQLSyntaxErrorException: 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 'index,
desc )  VALUES  ( 1,
'描述' )' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 'index,
desc )  VALUES  ( 1,
'描述' )' at line 1
```