mybatisplus保留字问题

一 保留字查询造成的报错如下

2020-08-25 09:03:56.552 ERROR 47459 --- [nio-8082-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying 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 'desc,f_cover AS cover,f_address AS address,f_longitude AS longitude,f_latitude A' at line 1
### The error may exist in com/test/device/dal/mapper/CompanyMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select count(0) from (SELECT  f_id AS id,f_name AS name,f_province AS province,f_city AS city,f_desc AS desc,f_cover AS cover,f_address AS address,f_longitude AS longitude,f_latitude AS latitude,f_company_type AS companyType,f_phone AS phone,f_parent_company AS parentCompany,f_gmt_create AS gmtCreate,f_gmt_modify AS gmtModify,f_industry AS industry,f_contact_id AS contactId,f_contact_name AS contactName,f_area AS area  FROM t_company) tmp_count
### 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 'desc,f_cover AS cover,f_address AS address,f_longitude AS longitude,f_latitude A' 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 'desc,f_cover AS cover,f_address AS address,f_longitude AS longitude,f_latitude A' at line 1] with root 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 'desc,f_cover AS cover,f_address AS address,f_longitude AS longitude,f_latitude A' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.21.jar:8.0.21]

二 原因分析及解决方案

通过报错信息可以看出，是因为desc是mysql数据库的关键字，而sql里如果包含关键字，会报java.sql.SQLSyntaxErrorException异常。

解决方案：

• 字段名改名，比如 desc用description替换。
• 如果是数据库字段名本身是关键字，还可以通过注解方式的value值前后加上”`”，如下：

  @TableField(value = "`desc`")

关于此示例的报错，明显只能通过第一种方案解决，因为是别名，通过注解的value解决不了，其实这也是mybatisplus的局限性，如果mybatis plus的设计，所有字段都添加了”`”，则不存在这种问题了。

如此生成的查询sql则是这样：

select `f_desc` as `desc` from t_company limit 10