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

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

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

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

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;