MySQL 双引号导致Unknown column '%'

这是我参与11月更文挑战的第30天，活动详情查看：2021最后一次更文挑战

问题描述

### Cause: java.sql.SQLSyntaxErrorException: Unknown column '%' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column '%' in 'where clause'
13:55:08.830 [TID: N/A] [http-nio-7001-exec-2] ERROR c.d.m.tag.domain.CategoryDomainImpl -
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column '%' in 'where clause'
### The error may exist in com/digital/market/tag/mapper/CategoryMapper.xml
### The error may involve defaultParameterMap### The error occurred while setting parameters
### SQL: SELECT t2.*         FROM dm_user_auth_category t1         LEFT JOIN dm_category t2 ON t1.category_id = t2.id         WHERE t1.user_id = ?         AND t2.state =1         AND t2.`code` LIKE CONCAT(?,"%")
### Cause: java.sql.SQLSyntaxErrorException: Unknown column '%' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column '%' in 'where clause'

生产上页面查询报错Cause: java.sql.SQLSyntaxErrorException: Unknown column '%' in 'where clause'，但是测试环境和开发环境没有问题。 查看代码里面很多Mapper里面都用的是双引号, CONTACT(#{code}, "%")

原因分析

报错的原因很明显，双引号MySQL 默认当成列处理了，但是测试开发环境没有问题，肯定是数据库配置哪里有差异

## 正常实例参数 
SQL_MODE = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

## 问题实例参数 SQL_MODE = PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

官网对sql_mode中该参数说明：

ANSI_QUOTES
Treat " as an identifier quote character (like the quote character) and not as a string quote character. You can still use to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

即在开启ANSI_QUOTES参数时，双引号不在作为字符串的引号符而是作为标识符引号字符（同`符号相同）；导致问题sql的条件在解析的时候报错。

解决方案

修复Mysql配置文件，重启数据库 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

参考文档

SQL_MODE 官网：MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes