使用 utf8mb4 表情符号时出现 MySQL `where` 错误？

Question

MySQL WHERE 在处理表情符号等 4 字节字符时似乎没有按预期工作。以下是从 MySQL Workbench 重现的方法:

-- Enable utf8mb4 in MySQL workbench
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 `text` FROM `mytable`
WHERE `text` = "💩";

预期结果

+------+
| text |
+------+
| 💩   |
+------+

实际结果

+------+
| text |
+------+
| 💩   |
| 😃   |  <-- ??
+------+

我已经在 5.6.32 和 5.7.20 中验证了这种行为。

请指教这是怎么回事。谢谢

Answer 1

这不是 MySQL 错误，而是关于什么是 COLLATE 以及正确设置它的重要性的一个很好的教训。比较字符串时使用排序规则。

COLLATE子句可以用在select语句中

SELECT `text` FROM `mytable`
WHERE `text` COLLATE utf8mb4_bin = "💩";

或者表格永久改变

ALTER TABLE `mytable` 
CHANGE COLUMN `text` `text` TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NULL DEFAULT NULL ;

也可以在建表的时候设置

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