Skip to main content

如何删除MySQL中的重复行

在上一个教程中,向您展示了如何在表中查找重复值。一旦找出重复的行,您可能需要删除它们以清理数据。

准备测试数据

下面的sql语句创建 contacts 表并插入测试数据。

DROP TABLE IF EXISTS 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');

下面的语句从 contacts 表查询数据:

SELECT * FROM contacts
ORDER BY email;

以下语句返回 contacts 表中的重复电子邮件:

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

可以看到,有四行重复的email。

A)使用Delete JOIN语句删除重复的行

MySQL提供了DELETE JOIN语句,可以用来快速删除重复的行。

以下语句删除重复的行并保留id大的一行:

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

这个查询两次引用contacts表,因此使用表别名t1和t2。

输出为:

Query OK, 4 rows affected (0.10 sec)

这表明有四行被删除,你可以再次执行查找重复email的查询来验证:

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

查询返回一个空集,这说明重复的行已被删除。

我们来查看contacts表中的数据:

SELECT * FROM contacts;

id为2、4、7和9的行已被删除。

如果要删除重复行并保留id较小的那一行,可以使用以下语句:

DELETE c1 FROM contacts c1
INNER JOIN contacts c2
WHERE
c1.id > c2.id AND
c1.email = c2.email;
注意

可以再次执行创建 contacts 表的脚本并测试此查询。

以下输出展示了删除重复行后 contacts 表的数据。

B) 使用中间表删除重复行

使用中间表删除重复行的步骤如下:

  1. 创建一张新表,其结构与要删除重复行的原始表相同。
  2. 将不重复的行从原始表插入中间表。
  3. 删除原始表并将中间表重命名为原始表。

第一步,创建结构与原始表相同的新表:

CREATE TABLE source_copy LIKE source;

第二步,将不重复的行从原始表插入新表:

INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- 有重复值的列

第三步,删除原始表并将中间表重命名为原始表

DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;

例如,以下语句从contacts表中删除包含重复email的行:

-- 步骤 1
CREATE TABLE contacts_temp
LIKE contacts;

-- 步骤 2
INSERT INTO contacts_temp
SELECT *
FROM contacts
GROUP BY email;


-- 步骤 3
DROP TABLE contacts;

ALTER TABLE contacts_temp
RENAME TO contacts;

C)使用ROW_NUMBER()函数删除重复的行

注意

从MySQL版本8.02开始支持 ROW_NUMBER() 函数,因此在使用该函数之前,请检查您的MySQL版本。

下面的语句使用ROW_NUMBER()函数为每一行指定一个连续整数,如果email重复,行号将大于1。

SELECT 
id,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
email
) AS row_num
FROM
contacts;

以下语句返回重复行的id列表:

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

用在where子句中带有子查询的删除语句从contacts表中删除重复的行:

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

mysql输出

Query OK, 4 rows affected (0.009 sec)

在本教程中,您学习了如何使用delete JOIN语句或中间表删除MySQL中的重复行。