MySQL/MariaDB 在使用 utf8mb4 时无法处理唯一键

Question

我们有带有 utf8mb4 字符串的 MySQL 表:

CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`code` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `test_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入特殊字符时似乎转换错误:

mysql> insert into `test` (`code`, `name`) values ('munster', 'Munster');


mysql> insert into `test` (`code`, `name`) values ('münster', 'Münster');
ERROR 1062 (23000): Duplicate entry 'münster' for key 'test_code_unique'


mysql> SELECT * FROM test WHERE code='münster';
+----+---------+---------+
| id | name    | code    |
+----+---------+---------+
|  1 | Munster | munster |
+----+---------+---------+
1 row in set (0.00 sec)



mysql> SELECT * FROM test WHERE code='munster';
+----+---------+---------+
| id | name    | code    |
+----+---------+---------+
|  1 | Munster | munster |
+----+---------+---------+
1 row in set (0.00 sec)

如果删除唯一键，则第二个插入有效，但即使查询不同，搜索也会返回 2 行:

mysql> drop table test;


CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`code` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


mysql> insert into `test` (`code`, `name`) values ('munster', 'Munster');


mysql> insert into `test` (`code`, `name`) values ('münster', 'Münster');


mysql> SELECT * FROM test WHERE code='münster';
+----+----------+----------+
| id | name     | code     |
+----+----------+----------+
|  1 | Munster  | munster  |
|  2 | Münster  | münster  |
+----+----------+----------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM test WHERE code='munster';
+----+----------+----------+
| id | name     | code     |
+----+----------+----------+
|  1 | Munster  | munster  |
|  2 | Münster  | münster  |
+----+----------+----------+
2 rows in set (0.00 sec)

这已经在 MySQL 5.7 和 MariaDB 10.2 上进行了测试，它们都给出了相同的结果。

可能出了什么问题？

Answer 1

这个看似神秘的问题的原因是您正在使用 utf8mb4_unicode_ci 归类，并且该归类有意忽略重音字符与非重音字符之间的差异。请参阅:https://dev.mysql.com/doc/refman/5.7/en/charset-general.html

要解决此问题，请将 code 列的排序规则更改为 utf8mb4_bin，这将区分重音字符和非重音字符，以及大小写。