MariaDB下SQL查询无法区分不同Emoji字符问题排查求助

问题根因 #

问题核心出在nick字段使用的utf8mb4_general_ci排序规则：

• utf8mb4_general_ci是旧版通用排序规则，在MariaDB 10.1版本中对4字节长度的emoji类Unicode字符的匹配逻辑存在缺陷，会将所有4字节Unicode字符判定为相等，因此查询不同emoji都会返回相同统计结果，模糊匹配也会命中所有带emoji的记录。

解决方案 #

临时验证方案 #

可先在查询中强制指定严谨的排序规则验证效果，执行以下测试语句确认返回结果是否符合预期：

SELECT Sum(CASE WHEN u.nick LIKE '%👾%' COLLATE utf8mb4_unicode_ci
           THEN 1
           ELSE 0
           END) AS 'NF'
    ,Sum(CASE WHEN u.nick LIKE '%🌱%' COLLATE utf8mb4_unicode_ci
           THEN 1
           ELSE 0
           END) AS 'T'
FROM users u

单emoji匹配测试语句：

SELECT *
FROM users u
WHERE u.nick LIKE '%👾%' COLLATE utf8mb4_unicode_ci

如果测试结果和预期一致，再执行永久修复。

永久修复方案 #

直接修改nick字段的排序规则和表级规则保持统一为utf8mb4_unicode_ci即可，修改语句如下：

ALTER TABLE users MODIFY COLUMN nick varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

注意：如果nick字段允许为NULL，删除语句中的NOT NULL参数即可；如果表数据量较大，建议在业务低峰期执行ALTER操作，避免锁表影响线上业务

修改完成后原有查询无需额外加参数即可正常区分不同emoji。

补充建议 #

你当前使用的MariaDB 10.1属于已停止维护的旧版本，后续如果有条件建议升级到10.5及以上的长期支持版本，对utf8mb4字符集的支持更完善，可避免很多已知的字符处理类BUG。

内容的提问来源于stack exchange，提问作者IrishWolf