MySQL 5.7 utf8mb4环境下按umlaut变音符号搜索结果异常修复方案

基础环境 #

• MySQL版本：5.7.26
• 存储引擎：InnoDB
• 全局字符集：utf8mb4

相关配置与测试数据 #

字符集&排序规则配置查询 #

执行如下SQL查看系统配置：

SHOW VARIABLES WHERE Variable_name RLIKE '^(character_set|collation)_' ;

查询结果：

测试表结构与数据 #

建表语句：

CREATE TABLE `umlauttest` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `umlautvalue` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

测试表全量数据：

SELECT * from umlauttest;

结果：

问题现象 #

测试表中前3条数据的umlautvalue字段不含变音字符ä，后2条包含该字符。按照预期：

• 执行SELECT COUNT(id) FROM umlauttestWHEREumlautvalue LIKE '%max%' 应返回3条结果
• 执行SELECT COUNT(id) FROM umlauttestWHEREumlautvalue LIKE '%mäx%' 应返回2条结果
  实际执行后两条查询均返回5条结果，变音符号的差异被查询逻辑忽略。

原因说明 #

默认使用的utf8mb4_unicode_ci、utf8mb4_general_ci这类ci（大小写不敏感）排序规则，默认会对特殊字符做归一化处理，会将带变音符号的ä和普通字符a判定为等价字符，因此匹配时不会区分二者差异。

解决方案 #

在查询语句中显式指定二进制排序规则utf8mb4_bin，该规则会基于字符的二进制编码值做严格匹配，可实现变音符号的区分：

SELECT COUNT(id) FROM `umlauttest` 
WHERE `umlautvalue` LIKE '%mäx%' collate utf8mb4_bin;

如果需要该字段所有查询都默认严格区分变音符号，也可以直接修改字段的排序规则为utf8mb4_bin，避免每次查询都指定规则。 #

内容的提问来源于stack exchange，提问作者endo.anaconda