MYSQL UTF8_bin 不区分大小写的唯一索引

Question

我在 MYSQL 表中有一些列将包含不同语言的唯一字符串，并且在某些情况下某些字符会匹配，例如英语 A 与瑞典语 A 和 MYSQL 会将它们视为平等。

所以我正在考虑对这些列使用 UTF8_bin 排序规则，因为它似乎更合适。

问题在于，使用 UTF_8_bin 时，唯一索引将不区分大小写，因此如果两个字符串的语言相同，则可能会出现 'Andreas' 和 'andreas'。

有解决办法吗？

Answer 1

您可以使用生成的列来存储字符串的小写版本，并在其上使用唯一索引。

create table test_utf8_bin_ci
( u8 varchar(50) charset utf8mb4 collate utf8mb4_unicode_ci,
  u8_bin_ci varchar(50) charset utf8mb4 collate utf8mb4_bin as (lower(u8)) unique
);

insert into test_utf8_bin_ci (u8)
values ('A'),('Ä'),('Å'),('Â'),('Á'),('À');

insert into test_utf8_bin_ci (u8)
values ('å');

Error Code: 1062. Duplicate entry 'å' for key 'u8_bin_ci'

对于 5.7.8 之前的 MySQL 版本，尚不支持生成列的索引。所以你需要添加一个“正常”列(没有 as (lower(u8)))并计算 insert/update 中的值> 触发器。唯一索引的工作方式与计算列的工作方式相同，只是需要多编写一些代码。

create trigger trbins_test_u8_bin_ci before insert on test_u8_bin_ci
for each row 
  set new.u8_bin_ci = lower(new.u8);
create trigger trbupd_test_u8_bin_ci before update on test_u8_bin_ci
for each row 
  set new.u8_bin_ci = lower(new.u8);

如果你使用_bin，你应该知道很多函数将不再区分大小写，例如

select * 
from test_utf8_bin_ci 
where u8 = 'ä';

不会给你任何结果。为了能够使用索引进行搜索(如果您使用例如 where lower(u8) = lower('ä')，这是不可能的)，您可以使用

select * 
from test_utf8_bin_ci 
where u8_bin_ci = lower('ä');

这意味着您的查询将使用不同的列来比较和更新(如果您使用框架，这可能需要进一步调整)，但如果变通办法是完美的，就不会称为变通办法。