mysql查询忽略符号_MySQL查询表情符号报错问题原因及解决

问题

线上报错 :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'

解决

方法1set 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_ciBecause 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.

方法2alter 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)