MySQL查询表情符号报错问题原因及解决

Question

问题

线上报错 :

java.sql.SQLException: Illegal mix of collations (utf8\_general\_ci,IMPLICIT) and (utf8mb4\_general\_ci,COERCIBLE) for operation 'like'

原因

经查原来是 搜索字符串中包含了 emoji表情符：

...&pageNum=0&pageSize=50&keyword=美元 💵

但数据库字段是utf8的 因为本身该字段不会有表情符号的情况

CREATE TABLE `XXX` (
  ...
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
   ...  
) ENGINE=InnoDB DEFAULT CHARSET=utf8

本地复现

Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

select * from t where name like '💵';
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'

解决

方法1

set names utf8;
Query OK, 0 rows affected (0.00 sec)

select * from t where name like '💵';
Empty set, 1 warning (0.00 sec)

对应代码修改：

url: jdbc:mysql://ip:port/xxx?...&connectionCollation=utf8_general_ci

应用启动的时候 会执行如下的命令

SET NAMES utf8 COLLATE utf8_general_ci

参考文档：
https://dev.mysql.com/doc/con...

Because there is no Java-style character set name for utfmb3 that you can use with the connection option charaterEncoding, the only way to use utf8mb3 as your connection character set is to use a utf8mb3 collation (for example, utf8_general_ci) for the connection option connectionCollation, which forces a utf8mb3 character set to be used, as explained in the last bullet.

方法2

alter table t modify name varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL;

Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from t where name like '💵';
Empty set (0.01 sec)