MySQL唯一索引创建

引言

邮箱、手机号、订单号这类业务字段要求不能重复，光靠应用层判断并发下仍可能插入重复值。唯一索引在数据库层直接约束重复，是防脏数据的最后一道保险，同时还能加速等值查询。

建表时定义唯一索引

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

uk_email是索引名，email列唯一。

已有表追加唯一索引

ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);

数据里已有重复值时会报错1062，需先清理重复再加。

组合唯一索引

多列组合唯一，比如同一商品同一活动只领一次券：

ALTER TABLE coupon ADD UNIQUE KEY uk_user_goods (user_id, goods_id);

允许单列重复，但组合起来不能重复。

插入重复的报错处理

INSERT INTO users(email) VALUES('a@x.com');
-- 再次插入同email触发 1062 Duplicate entry

应用层catch错误码，返回邮箱已注册，而不是让500报错。

插入或更新冲突

INSERT INTO users(email) VALUES('a@x.com')
ON DUPLICATE KEY UPDATE login_time=NOW();

唯一键冲突时转成更新，适合登录时间刷新。

注意事项

• 加唯一索引前先查重：SELECT email,COUNT(*) FROM users GROUP BY email HAVING COUNT(*)>1；
• NULL在唯一索引里可以出现多个，MySQL不认为NULL相等；
• 唯一索引也是普通索引，等值查询能走索引；
• 大表加索引会锁表或耗时，高峰期用在线DDL；
• 删除索引DROP INDEX uk_email ON users。

总结

唯一索引在建表时用UNIQUE KEY定义，已有表用ALTER TABLE ADD UNIQUE KEY追加，多列组合唯一用组合索引。加之前先清重复，插入冲突用ON DUPLICATE KEY UPDATE转更新，数据库层约束比应用层判断更可靠。

前言

表里有重复数据，同一个用户存了好几条。怎么删除重复数据，只保留一条？

一、先查有哪些重复数据

按字段查重复

SELECT name, COUNT(*) as cnt 
FROM users 
GROUP BY name 
HAVING cnt > 1;

查重复的完整记录

SELECT * FROM users 
WHERE name IN (
    SELECT name FROM users 
    GROUP BY name 
    HAVING COUNT(*) > 1
)
ORDER BY name;

二、删除重复数据，保留一条

方法1：用自连接（推荐）

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.name = u2.name;

意思： 同一个name，id大的删掉，保留id小的。

方法2：用NOT IN

DELETE FROM users 
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id 
        FROM users 
        GROUP BY name
    ) as tmp
);

方法3：用临时表

-- 第一步：建临时表存要保留的id
CREATE TABLE tmp AS
SELECT MIN(id) as min_id FROM users GROUP BY name;

-- 第二步：删除不在临时表里的
DELETE FROM users WHERE id NOT IN (SELECT min_id FROM tmp);

-- 第三步：删临时表
DROP TABLE tmp;

三、按多个字段去重

比如name和email都重复才算重复：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id 
AND u1.name = u2.name 
AND u1.email = u2.email;

四、保留最新的一条

不是保留id最小的，而是保留最新创建的：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.created_at < u2.created_at 
AND u1.name = u2.name;

五、批量去重前先备份

重要！ 删之前先备份：

CREATE TABLE users_bak AS SELECT * FROM users;

确认没问题再删。

六、怎么防止重复数据

加唯一索引

ALTER TABLE users ADD UNIQUE INDEX uk_name (name);

加了唯一索引，重复数据就插不进去了。

插入前先查

// PHP示例
$exists = $db->query("SELECT id FROM users WHERE name = '$name'");
if ($exists) {
    echo "已存在";
} else {
    $db->query("INSERT INTO users ...");
}

用INSERT IGNORE

INSERT IGNORE INTO users (name) VALUES ('张三');

重复了就忽略，不报错。

用ON DUPLICATE KEY UPDATE

INSERT INTO users (name, count) VALUES ('张三', 1)
ON DUPLICATE KEY UPDATE count = count + 1;

重复了就更新，不插入新的。

常见坑

坑1：删完发现数据没了

删重复的时候把正常数据也删了。

原因： 关联条件写错了。

解决： 先备份，再用SELECT确认要删哪些：

SELECT u1.* FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.name = u2.name;

确认没问题再把SELECT改成DELETE。

坑2：大表删太慢

几百万条数据，删半天删不完。

解决： 分批删，每次删1000条：

DELETE FROM users WHERE ... LIMIT 1000;

坑3：自连接删不干净

有的重复数据没删掉。

原因： 字段值有NULL，NULL=NULL是unknown。

解决： 加NULL判断：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id 
AND (u1.name = u2.name OR (u1.name IS NULL AND u2.name IS NULL));

坑4：报错"You can't specify target table for update in FROM clause"

MySQL不允许先查再删同一张表。

解决： 套一层临时表：

DELETE FROM users 
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id FROM users GROUP BY name
    ) as tmp
);

总结

去重三步：

1. 先查：GROUP BY ... HAVING COUNT(*) > 1
2. 再确认：SELECT出来看哪些要删
3. 最后删：自连接DELETE

记住： 删之前务必备份！

遇到问题加QQ23979811 协助处理