MySQL多列唯一约束下仅修改字段大小写触发重复键错误咨询

问题根因 #

• 你使用的utf8mb4_general_ci校对规则属于大小写不敏感类型，唯一索引校验时会将orange和Orange判定为相同值
• 报错并非是MySQL将新值和当前行原值做校验，本质是索引校验规则和你的需求不匹配：你需要唯一约束校验时大小写敏感，普通查询时大小写不敏感，单靠字段本身的校对规则无法同时满足这两个需求
• 额外说明：InnoDB并不支持用户自定义HASH索引，你建索引时指定的USING HASH会被自动转换为B树索引，不影响本次问题，但属于无效配置

可行解决方案（完全满足需求） #

方案1：新增大小写敏感的生成列建唯一约束（推荐，无业务侵入） #

这个方案不需要修改原有字段的校对规则，原有查询仍然保持大小写不敏感，仅唯一约束校验时大小写敏感：

1. 先删除原有联合唯一索引

ALTER TABLE `mytable` DROP INDEX `col5`;

2. 新增三个VARCHAR类型的生成列，存储对应字段的二进制（大小写敏感）值，长度和你之前的索引前缀保持255即可

ALTER TABLE `mytable`
ADD COLUMN `col5_unique` VARCHAR(255) GENERATED ALWAYS AS (BINARY `col5`) STORED,
ADD COLUMN `col7_unique` VARCHAR(255) GENERATED ALWAYS AS (BINARY `col7`) STORED,
ADD COLUMN `col9_unique` VARCHAR(255) GENERATED ALWAYS AS (BINARY `col9`) STORED;

3. 在三个生成列上建联合唯一约束

ALTER TABLE `mytable`
ADD UNIQUE KEY `unique_col5_col7_col9` (`col5_unique`, `col7_unique`, `col9_unique`);

改造完成后：

• 普通查询仍然使用col5/col7/col9，校对规则还是utf8mb4_general_ci，保持大小写不敏感
• 唯一约束基于生成列的二进制值校验，大小写不同会被判定为不同值，修改大小写不会再误报重复
• 生成列是数据库自动维护的，不需要业务代码做任何修改，无侵入

方案2：临时禁用唯一校验（仅适合单次修改，不推荐日常使用） #

如果只是需要临时执行这一次更新，可以在执行更新语句前临时关闭当前会话的唯一键检查，执行完再打开，不会影响其他会话：

SET SESSION unique_checks = 0;
UPDATE `mytable` SET `col7` = 'Orange' WHERE `mytable`.`post_ID` = 120;
SET SESSION unique_checks = 1;

注意：这个方案仅可在确认没有其他行和你更新后的值重复时使用，否则会插入重复数据破坏约束。

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