MySQL 添加联合唯一索引与重复数据处理指南

什么是联合唯一索引

联合唯一索引（Unique Index）用于约束表中多个字段组成的复合值的唯一性。与普通唯一索引不同，它允许多个单字段存在重复值，只要它们的组合是唯一的即可。

典型应用场景：

• 统计报表：如"每个用户每天的访问记录"，要求 user_account_id 与 daily_date 的组合不可重复。
• 配置项管理：如"每个系统下的特定参数名"，需保证 system_name 和 param_name 的配对唯一。

添加联合唯一索引的操作方法

在 MySQL 中，添加联合唯一索引主要有两种方式：在建表时定义或在表结构创建后修改。

第1步：在现有表中添加联合唯一索引

如果表已经存在且没有该索引，可以使用 ALTER TABLE 语句。这种方式适用于生产环境中已有数据结构的情况。

语法说明：

• UNIQUE INDEX 或 UNIQUE KEY：两者等价，均表示唯一索引。
• index_name：自定义索引名称，建议包含关键字段含义以便识别。
• (column1, column2, ...)：指定参与联合约束的字段顺序。

操作示例：

-- 添加三列组合的唯一索引
ALTER TABLE user_info ADD UNIQUE INDEX agd_key (user_account_id, game_id, daily_date);

-- 或使用 KEY 关键字
ALTER TABLE user_info ADD UNIQUE KEY agd (user_account_id, game_id, daily_date); 

执行成功后，再次查看表结构可使用以下命令验证：

SHOW INDEX FROM user_info; 

第2步：在创建表时直接定义

如果是新建表，建议在 DDL 语句中直接声明，减少后续维护成本。

CREATE TABLE t_aa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    aa VARCHAR(50),
    bb VARCHAR(50),
    -- 定义联合唯一索引
    UNIQUE INDEX uk_aa_bb (aa, bb)
); 

第3步：创建普通的联合索引

除了唯一索引，有时仅需加速查询而允许数据重复，可使用普通索引。

CREATE INDEX first_index ON student(id, name, address); 

注意：创建索引时，索引名、表名及字段名通常不建议加反引号以外的引号，除非名称包含特殊字符或保留字。

处理已存在的重复数据

如果在添加唯一索引之前，表中已经包含了符合唯一约束规则的重复记录，直接执行 ALTER TABLE ... ADD UNIQUE 会报错失败。此时有以下两种处理策略：

策略一：忽略重复项并建立索引（谨慎使用）

使用 IGNORE 关键字，MySQL 会自动删除重复行中的一条，保留另一条，然后建立索引。

ALTER IGNORE TABLE t_aa ADD UNIQUE INDEX uk_aa_bb (aa, bb); 

风险提示：此操作会导致部分历史数据永久丢失。执行前务必先备份表数据，或通过 SELECT 语句确认哪些数据将被清除。

策略二：先清理数据再建索引（推荐）

1. 查找重复数据：确定哪些组合发生了重复。
2. 删除多余记录：根据业务逻辑手动或编写脚本清洗数据。
3. 添加唯一索引：确保数据干净后执行添加索引操作。

配合 INSERT ... ON DUPLICATE KEY UPDATE 的使用

在实际业务中，我们常遇到"如果记录不存在则插入，如果已存在则更新字段"的需求。配合联合唯一索引，该功能可以完美运行。

场景模拟：

假设有一张设备表 unit，其主键或唯一索引由 id 决定。我们需要写入新设备信息，若已存在则覆盖其属性。

INSERT INTO unit (id, unitsubclass, name, state) 
VALUES ('1111', 'CPU', 'Intel CPU', '0') 
ON DUPLICATE KEY UPDATE 
    unitsubclass = VALUES(unitsubclass), 
    name = VALUES(name), 
    state = VALUES(state); 

逻辑解析：

• 匹配条件：当 INSERT 的值与表中现有的主键或唯一索引冲突时，触发 UPDATE 分支。
• VALUES(column)：引用的是试图插入的新值，而非旧值。
• 优势：避免了先 SELECT 判断是否存在，再分别执行 INSERT 或 UPDATE 的网络交互和锁竞争，性能更高且原子性更好。

其他 MySQL 索引类型概览

了解联合唯一索引的同时，也可参考以下常用索引的创建语法：

1. 主键索引 (PRIMARY KEY)

非空且唯一，一个表只能有一个。通常用于标识记录。

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`); 

2. 普通索引 (INDEX)

最基本索引，没有任何限制，用于加速查询。

ALTER TABLE `table_name` ADD INDEX index_name (`column`); 

3. 全文索引 (FULLTEXT)

适用于大型文本字段（如文章正文），支持中文分词搜索（MyISAM/InnoDB引擎支持度取决于版本）。

ALTER TABLE `table_name` ADD FULLTEXT (`column`); 

4. 多列普通索引

类似联合唯一索引，但允许组合重复。

ALTER TABLE `table_name` ADD INDEX multi_col_idx (`col1`, `col2`, `col3`); 

总结

利用 MySQL 联合唯一索引可以有效防止脏数据产生，保障业务逻辑的一致性。对于已有的重复数据，需采用 ALTER IGNORE 或手动清洗方式解决；对于动态写入需求，结合 INSERT ... ON DUPLICATE KEY UPDATE 语句可以实现简洁高效的 Upsert 操作。合理设计索引不仅能提升查询效率，还能简化应用层的代码逻辑。

以上就是 MySQL 添加联合唯一索引及配合更新语句的详细内容，更多关于 SQL 索引优化与数据完整性的资料请关注本站其它相关文章！