如何在utf8mb4_general_ci下创建大小写敏感唯一键的数据表

完全可以在保留数据库、表、列默认utf8mb4_general_ci排序规则的前提下，实现唯一键的大小写敏感校验，不需要把整列的排序规则修改为utf8mb4_bin。

之前插入A和a触发唯一键冲突的核心原因是：utf8mb4_general_ci中的ci是case insensitive（大小写不敏感）的缩写，该规则下A和a会被判定为相等值，因此触发唯一约束报错。

方案1：通过SQL语句配置（通用方案，不依赖操作工具） #

MySQL 8.0及以上版本支持单独为索引指定校对规则，不会影响列本身的默认排序规则：日常普通查询依然遵循utf8mb4_general_ci的大小写不敏感逻辑，仅唯一键校验时按大小写敏感规则判断。
操作SQL如下：

-- 1. 先删除原有绑定在code列的唯一索引，注意提前清理仅大小写不同的重复数据，否则后续操作会报错
ALTER TABLE `example` DROP INDEX `code`;
-- 2. 创建新的唯一索引，单独指定索引使用utf8mb4_bin校对规则
ALTER TABLE `example` ADD UNIQUE KEY `uk_code` (`code`) COLLATE utf8mb4_bin;

执行完成后可以查看表结构，code列本身的排序规则依然为utf8mb4_general_ci，仅新建的唯一索引使用二进制校对规则。
如果后续需要在特定查询中实现大小写敏感匹配，不需要修改全局配置，只需要在查询语句中单独指定校对规则即可：

-- 该查询只会严格匹配code值为'A'的记录，不会返回'a'的结果
SELECT * FROM `example` WHERE `code` = 'A' COLLATE utf8mb4_bin;

如果使用的是MySQL 5.x版本（不支持索引单独指定校对规则），可以用生成列的方案折中实现：

-- 新增一个和code值同步的二进制存储生成列，对该列加唯一键即可
ALTER TABLE `example` 
ADD COLUMN `code_bin` VARCHAR(255) GENERATED ALWAYS AS (`code`) STORED,
ADD UNIQUE KEY `uk_code_bin` (`code_bin`);

该方案下原code列的排序规则完全不变，唯一键校验通过二进制格式的生成列实现大小写敏感判断。

方案2：通过phpMyAdmin配置 #

操作步骤如下：

• 登录phpMyAdmin后选中目标example表，进入「结构」标签页
• 在索引列表中找到原绑定在code列上的UNIQUE索引，点击删除移除旧索引
• 点击索引区域的「创建索引」按钮，索引类型选择「UNIQUE」，自定义索引名称（比如uk_code），选择绑定列为code，在索引的校对规则下拉选项中选择utf8mb4_bin，点击保存即可
• 操作完成后返回表结构页，确认code列本身的排序规则仍为utf8mb4_general_ci即配置生效

如果是MySQL 5.x环境，在phpMyAdmin里新增生成列、给生成列加唯一键的操作和上述逻辑一致，在结构页新增列时选择列类型为VARCHAR，勾选「生成列」选项，生成表达式填code，存储类型选STORED，再给该列加唯一索引即可。

方案3：PDO侧适配说明 #

PDO作为数据库连接驱动，没有修改数据库索引规则、排序规则的能力，这类配置必须在数据库侧完成。PDO侧只需要保证连接时字符集配置正确，避免乱码即可，参考连接代码：

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=my_database;charset=utf8mb4',
    'your_db_username',
    'your_db_password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
);

如果业务中部分查询需要大小写敏感匹配，直接在拼接的SQL语句中加入COLLATE utf8mb4_bin即可，不需要修改PDO的全局配置。

• 不要为了实现唯一键大小写敏感直接修改库级、全局的排序规则，会影响所有存量表的默认查询逻辑，引发非预期的匹配结果
• 不管用哪种方案，创建大小写敏感的唯一键前，必须先清理表中已经存在的仅大小写不同的重复值，否则索引创建会直接失败

内容的提问来源于stack exchange，提问作者Sittiphan Sittisak