MySQL WHERE子句处理utf8mb4表情符号时不符合预期的问题

嘿，这个坑我之前踩过！当用WHERE子句的=匹配💩这类4字节表情符号时，结果经常不符合预期，本质是MySQL的utf8mb4排序规则在字符比较时的等价性处理搞的鬼。

复现步骤（MySQL Workbench中） #

先执行以下SQL准备测试数据：

-- 会话级别启用utf8mb4
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

CREATE TABLE `mytable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text` TEXT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4;

INSERT INTO `mytable` (`text`) VALUES ("ABC"), ("💩"), ("😃"), ("💩😃");

执行查询SELECT textFROMmytableWHEREtext = "💩";时，预期仅返回内容为"💩"的行，但实际会把包含该表情的"💩😃"也查出来，结果和预期不符。

问题原因 #

这是因为默认的utf8mb4_unicode_ci排序规则是基于Unicode标准的“不区分大小写/等价字符”比较规则，它会把某些语义或视觉相近的字符视为等价，甚至在某些情况下，会忽略部分字符的存在进行匹配，导致范围扩大。

解决办法 #

有两种简单的方式可以解决这个问题：

方式1：临时使用二进制比较

在查询时用BINARY关键字强制按字节精准匹配：

SELECT `text` FROM `mytable` WHERE BINARY `text` = "💩";

这种方式不需要修改表结构，适合临时的精准查询场景，会严格对比字符串的每一个字节，只返回完全相等的行。

方式2：修改字段/表的排序规则为二进制规则

如果需要长期精准匹配字符，可以把字段的排序规则改为utf8mb4_bin，它会直接按字符的二进制值进行比较，不会做任何等价转换：

-- 修改现有字段的排序规则
ALTER TABLE `mytable` MODIFY COLUMN `text` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- 或者建表时直接指定
CREATE TABLE `mytable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text` TEXT NULL COLLATE utf8mb4_bin,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE utf8mb4_bin;

修改后，再执行原来的查询语句就能得到预期的精准匹配结果。

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