一、问题复现
今天接到同事发过来一个SQL,请求帮忙优化一下。他是在SQLyog中写的，但我平常是用Workbench，我Copy到Workbench中执行就报错。为了更好的说明问题，我将原SQL简化，保留要报错的部份
SQL如下：
SET @text = '明老三';
SELECT * FROM customer WHERE custname LIKE CONCAT('%', @text, '%')
执行报如下错误：
Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation ‘like’
报字符串校对规则不匹配。
二、排查问题
首先查看customer表的建表语句
show create table customer
表字段很多截图关键的几个字段
CREATE TABLE `customer` (
`tid` int(10) unsigned NOT NULL,
`custid` int(10) unsigned NOT NULL COMMENT '单位ID',
`custcode` varchar(50) NOT NULL COMMENT '单位编号',
`custname` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`memcode` varchar(200) NOT NULL COMMENT '助记码',
...
PRIMARY KEY (`tid`,`custid`),
CONSTRAINT `fk_customer_empid` FOREIGN KEY (`tid`, `empid`) REFERENCES `employee` (`tid`, `empid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户档案'
这个custname字段开发人员定义的是 utf8mb4_unicode_ci 校对规则。
那就是说 @text MySQL自动对应的就是 utf8mb4_general_ci
先来查一下。MySQL的字符集
show variables like '%char%'
character_set_client utf8
character_set_connection utf8mb4
character_set_database utf8
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8
character_set_system utf8
character_sets_dir
再查一下校对规则
show variables like '%collation%'
collation_connection utf8mb4_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci
三、修改方案
1、强制将@text 改为utf8mb4_unicode_ci
set @text = convert('明老三' USING utf8mb4) collate utf8mb4_unicode_ci ;
SELECT * FROM customer WHERE custname LIKE CONCAT('%', @text, '%')
执行结果：
2、将character_set_connection 改为 utf8mb4_unicode_ci
SET collation_connection = utf8mb4_unicode_ci ;
SET @text = '明老三';
SELECT * FROM customer WHERE custname LIKE CONCAT('%', @text, '%')
同样可以得到正常的结果
