大小写敏感的唯一性和大小写不敏感的搜索

Question

我有一个带有字段a的表，使用的编码是utf8，排序规则为utf8_unicode_ci：

CREATE TABLE dictionary (
    a varchar(128) NOT NULL
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

使用utf8_unicode_ci排序规则可以实现扩展和连接符的高效不区分大小写搜索。为此，我有以下索引：

CREATE INDEX a_idx on dictionary(a);

问题: 此外，我还必须确保字段a的所有存储值都是唯一的，但以区分大小写的方式。 德语示例："blühen"和"Blühen"都必须存储在表中。但是第二次添加"Blühen"应该是不可能的。

MySQL中是否有内置功能可以同时实现这两个条件？

不幸的是，在MySQL 5.1中似乎无法为索引设置校对规则。

解决此问题的方法包括在插入之前进行唯一性检查或触发器。但这两种方法都不如使用唯一索引优雅。

- user1091141

---

很不幸，MySQL缺乏其他关系型数据库管理系统所具备的索引/物化视图、计算列或基于函数的索引等功能。当然我很想看看它是如何实现的... - gbn

在您的情况下，增加一个大小写敏感的排序规则并添加唯一性约束的另一列是否可行？ - Adrian Cornish

我认为这个话题会很有帮助。https://dev59.com/eVTTa4cB1Zd3GeqPpyp5 - MahanGM

感谢您的评论！添加另一列可能是一个选项。对于我的特定目的，我认为在任何少数更新或插入操作之前添加唯一性检查是最佳选择。我的原始帖子是一个简化版。实际上，我有多个列，许多读取操作，并担心添加更多列会因内存有限而成为性能问题。 - user1091141

Answer 1

好的，有两种方法可以实现这个：

1. 使用_bin排序规则
2. 将数据类型更改为VARBINARY

情况1：使用_bin排序规则

按照以下方式创建您的表：

CREATE TABLE `dictionary` (
 `a` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 UNIQUE KEY `idx_un_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

请注意：

1. 列 a 的数据类型
2. 在列 a 上的唯一索引

情况2：使用 VARBINARY 数据类型 请按以下方式创建您的表：

CREATE TABLE `dictionary` (
 `a` VARBINARY(128) NOT NULL,
 UNIQUE KEY `idx_uniq_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

请注意：

1. 新的数据类型 VARBINARY 2. 在列 a 上的唯一索引

因此，上述两者都可以解决您的问题。也就是说，它们都允许像 'abc'、'Abc'、'ABC'、'aBc' 等值，但如果大小写匹配，则不允许再次使用相同的值。

请注意，给出 "_bin" 排序规则与使用二进制数据类型是不同的。因此，请随时参考以下链接：

1. 二进制和VARBINARY数据类型 2. _bin和binary排序规则 希望以上内容能够帮助到您！

- Abhay

---

谢谢回复！我看不出这个解决方案如何实现高效（O(log(n))）和不区分大小写的搜索。 - user1091141

@user1091141，当然可以通过更改排序规则来进行不区分大小写的搜索，例如像这样的查询 SELECT * FROM dictionary WHERE a COLLATE utf8_general_ci = 'abc'。如果我的回答不够清晰，我很抱歉，但我猜你能理解，这里有一个链接 - "http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html"。关于O(log(n))，很抱歉，我的数学不是那么强，但我不明白为什么搜索会低效。或者，您可以保留2个`a`列 - 一个用于不区分大小写的搜索，另一个用于区分大小写的插入，使用_bin排序规则。 - Abhay

如果我在 where 子句中指定的排序规则与表定义中定义的不同，MySQL 将不使用索引而进行完整表扫描。对于大型表格，全表扫描可能需要很长时间，因此它们被认为是低效的。执行 EXPLAIN SELECT * FROM dictionary WHERE a COLLATE utf8_general_ci = 'abc' 将显示读取表中所有行。这至少适用于我的 MySQL 版本（5.0 和 5.1）。如果有所不同就好了。 - user1091141

我能想到另外两件事情。第一种是保留原始表结构（不带排序规则），并使用BEFORE INSERT触发器防止区分大小写的插入。第二种是将我的“a”定义作为单独列添加到您的原始表中。 - Abhay

我同意这两个解决方案。不幸的是，我无法编写所需的触发器，因为我不知道如何在MySQL 5.1中使用触发器抛出错误消息。这怎么可能呢？我更喜欢触发器解决方案，因为我注意到添加更多列时性能会显着下降。 - user1091141

触发器作为主查询的一部分工作，因此您不需要对其进行显式错误跟踪。无论触发器抛出什么错误（如果有），都将由主查询本身返回。因此，您只需要对主查询进行错误跟踪。 - Abhay

Answer 2

您可以通过添加附加列“column_lower”来实现此目的。

CREATE TABLE `dictionary` (
  `a` VARCHAR(128) NOT NULL,
  `a_lower` VARCHAR(128) NOT NULL,
  UNIQUE KEY `idx_un_a_lower` (`a_lower`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

插入代码如下：

insert into dictionary set a = x, a_lower = lower(x);

现在可以不区分大小写地使用Select：

select * from dictionary where a_lower like lower('search_term%')

请注意，具有索引的列最多可以存储191个字符。MySQL可以拥有最长为767字节的索引，即767/4（如果使用utf8mb4排序规则，则Unicode最多可占用4个字节）= 191.75 = 191个字符。如果使用最多每个字符占用3个字节的utf8排序规则，则列最多可以存储767/3 = 255个字符。

- broadband

Answer 3

SELECT * FROM dictionary WHERE a COLLATE utf8_general_ci = 'abc'

试试这个，它会起作用的...对我来说已经起作用了。

- user3328257