蓝桉云顶

MySQL数据库去除重复数据

在MySQL数据库中，处理重复数据是一个常见的任务，本文将详细介绍如何查找和删除MySQL数据库中的重复数据。

防止表中出现重复数据

在数据还未插入之前，可以通过设置主键（PRIMARY KEY）或唯一索引（UNIQUE）来防止重复数据的产生，在学生信息表中，学号no不允许重复：

CREATE TABLE student (
    no CHAR(12) NOT NULL,
    name CHAR(20),
    sex CHAR(10),
    PRIMARY KEY (no)
);

查找并删除重复数据

统计重复数据

要查找表中的重复记录数，可以使用以下SQL语句：

SELECT COUNT(*) as repetitions, no
FROM student
GROUP BY no
HAVING repetitions > 1;

该查询返回student表中重复的记录数。

过滤重复数据

如果需要读取不重复的数据，可以在SELECT语句中使用DISTINCT关键字：

SELECT DISTINCT no
FROM student;

或者使用GROUP BY：

SELECT no
FROM student
GROUP BY no;

删除重复数据

删除重复数据的方法有多种，下面介绍几种常见的方法：

方法一：使用临时表

CREATE TABLE tmp AS
SELECT no, name, sex FROM student GROUP BY no;
DROP TABLE student;
ALTER TABLE tmp RENAME TO student;

这种方法通过创建一个临时表来存储不重复的数据，然后删除原表并将其重命名为原表名。

方法二：添加主键或唯一索引

ALTER IGNORE TABLE student ADD PRIMARY KEY (no);

此方法通过添加主键来自动删除重复的记录。

方法三：使用DELETE JOIN语句

DELETE t1 FROM student t1
INNER JOIN student t2 WHERE t1.id < t2.id AND t1.email = t2.email;

这个查询引用了两次student表，使用别名t1和t2，保留具有最大id的记录。

方法四：使用ROW_NUMBER()函数（适用于MySQL 8.02及以上版本）

DELETE FROM student
WHERE id IN (
    SELECT id FROM (
        SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
        FROM student
    ) t
    WHERE row_num > 1
);

这个查询使用ROW_NUMBER()函数给每一行分配一个整数序列值，然后删除重复的行。

示例操作

假设有一个名为contacts的表，包含以下数据：

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);
INSERT INTO contacts (first_name, last_name, email) VALUES 
('Carine', 'Schmitt', 'carine.schmitt@verizon.net'),
('Jean', 'King', 'jean.king@me.com'),
('Peter', 'Ferguson', 'peter.ferguson@google.com'),
('Janine', 'Labrune', 'janine.labrune@aol.com'),
('Jonas', 'Bergulfsen', 'jonas.bergulfsen@mac.com'),
('Janine', 'Labrune', 'janine.labrune@aol.com'),
('Susan', 'Nelson', 'susan.nelson@comcast.net'),
('Zbyszek', 'Piestrzeniewicz', 'zbyszek.piestrzeniewicz@att.net'),
('Roland', 'Keitel', 'roland.keitel@yahoo.com'),
('Julie', 'Murphy', 'julie.murphy@yahoo.com'),
('Kwai', 'Lee', 'kwai.lee@google.com'),
('Jean', 'King', 'jean.king@me.com'),
('Susan', 'Nelson', 'susan.nelson@comcast.net'),
('Roland', 'Keitel', 'roland.keitel@yahoo.com');

要删除重复的email记录，可以执行以下SQL语句：

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 ON c1.id < c2.id AND c1.email = c2.email;

该语句将保留每个email地址的最大id记录，删除其他重复记录。

常见问题及解答

Q1: 如何在MySQL中删除重复数据但保留一条记录？

A1: 可以使用DELETE JOIN语句或ROW_NUMBER()函数来实现。

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 ON c1.id < c2.id AND c1.email = c2.email;

或（适用于MySQL 8.02及以上版本）：

DELETE FROM contacts
WHERE id IN (
    SELECT id FROM (
        SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
        FROM contacts
    ) t
    WHERE row_num > 1
);

Q2: 如何在MySQL中查找重复数据？

A2: 可以使用GROUP BY和HAVING子句来查找重复数据。

SELECT email, COUNT(email) as repetitions
FROM contacts
GROUP BY email
HAVING repetitions > 1;