如何在MySQL中仅对数据子集执行区分大小写的唯一索引？

在MySQL中，要仅对数据子集执行区分大小写的唯一索引，可以通过以下步骤实现：

基础概念

唯一索引（Unique Index）：确保索引列中的每个值都是唯一的。
区分大小写（Case Sensitivity）：在某些字符集和排序规则下，字符串比较会区分大小写。

类型

单列索引：仅对一个列创建索引。
复合索引：对多个列创建索引。

应用场景

用户表中的用户名：确保用户名唯一且区分大小写。
产品表中的产品代码：确保产品代码唯一且区分大小写。

实现步骤

假设我们有一个表 users，其中有一个列 username，我们希望对 username 列创建一个区分大小写的唯一索引，并且仅对特定子集生效。

步骤1：创建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

步骤2：创建区分大小写的唯一索引

MySQL 默认使用 utf8mb4_general_ci 排序规则，这是不区分大小写的。我们需要使用 utf8mb4_bin 排序规则来实现区分大小写。

ALTER TABLE users ADD UNIQUE INDEX idx_username_case_sensitive (username(255)) USING BTREE;

步骤3：确保列使用区分大小写的排序规则

ALTER TABLE users MODIFY username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

遇到问题及解决方法

问题1：索引创建失败

原因：可能是因为列中已经存在重复的值，或者列的数据类型不兼容。

解决方法：

检查并删除重复值：
检查并删除重复值：
确保列的数据类型和排序规则正确。

问题2：查询时不区分大小写

原因：可能是查询语句中没有正确使用区分大小写的比较。

解决方法：确保查询语句中使用 COLLATE utf8mb4_bin 来强制区分大小写：

SELECT * FROM users WHERE username = 'JohnDoe' COLLATE utf8mb4_bin;

示例代码

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

-- 修改列的排序规则为区分大小写
ALTER TABLE users MODIFY username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

-- 创建唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_username_case_sensitive (username(255)) USING BTREE;

-- 插入数据
INSERT INTO users (username) VALUES ('JohnDoe');
INSERT INTO users (username) VALUES ('johndoe'); -- 这将失败，因为索引是区分大小写的

-- 查询数据
SELECT * FROM users WHERE username = 'JohnDoe' COLLATE utf8mb4_bin;

通过以上步骤，你可以在MySQL中对数据子集执行区分大小写的唯一索引，并解决可能遇到的问题。