mysql 建立联合唯一索引

基础概念

MySQL中的联合唯一索引（Composite Unique Index）是指在一个表上创建的索引，该索引由两个或多个列组成，用于确保这些列的组合值在表中是唯一的。联合唯一索引可以有效地防止插入重复的组合数据，从而保证数据的完整性和一致性。

相关优势

1. 数据唯一性：确保多个列的组合值在表中唯一，防止重复数据。
2. 查询优化：索引可以加速基于这些列的查询操作。
3. 数据完整性：通过数据库层面的约束，减少应用层的逻辑判断。

类型

联合唯一索引是唯一索引的一种，唯一索引还包括单列唯一索引。

应用场景

• 用户表：在用户表中，通常会有用户名和邮箱的组合，可以创建联合唯一索引确保每个用户名和邮箱的组合是唯一的。
• 订单表：在订单表中，订单号和用户ID的组合可以创建联合唯一索引，确保每个用户对每个订单号的组合是唯一的。

示例代码

假设我们有一个用户表 users，包含以下列：id, username, email。我们希望确保 username 和 email 的组合是唯一的。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE INDEX idx_unique_username_email (username, email)
);

遇到的问题及解决方法

问题：为什么插入重复的组合数据时，MySQL没有报错？

原因：可能是没有正确创建联合唯一索引，或者插入的数据与现有数据完全一致。

解决方法：

1. 确认联合唯一索引已经正确创建。
2. 检查插入的数据是否与现有数据完全一致。

-- 检查索引是否存在
SHOW INDEX FROM users WHERE Key_name = 'idx_unique_username_email';

-- 如果索引不存在，重新创建
ALTER TABLE users ADD UNIQUE INDEX idx_unique_username_email (username, email);

问题：插入重复数据时，MySQL报错，但无法定位具体哪条数据重复？

原因：可能是由于数据量较大，查询重复数据时效率低下。

解决方法：

1. 使用 SELECT 语句查找重复数据。

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1;

1. 删除重复数据。

DELETE u1 FROM users u1
JOIN users u2 
WHERE u1.id < u2.id AND u1.username = u2.username AND u1.email = u2.email;

参考链接

• MySQL 联合唯一索引
• MySQL 索引

通过以上信息，你应该能够全面了解MySQL联合唯一索引的基础概念、优势、类型、应用场景以及常见问题及其解决方法。