How To Delete Duplicate Rows in MySQL

摘要：在本教程中，您将学习在 MySQL 中删除重复行的各种方法。

在之前的教程中，我们向您展示了如何查找表中的重复值。识别出重复行后，您可能需要删除它们以清理数据。

准备样本数据

以下脚本创建contacts表并将示例数据插入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');
Code language: SQL (Structured Query Language) (sql)

请注意，您可以在执行DELETE语句后执行此脚本来重新创建测试数据。

此查询返回联系人表中的数据：

SELECT * FROM contacts
ORDER BY email;Code language: SQL (Structured Query Language) (sql)

以下查询返回contacts表中的重复电子邮件：

SELECT 
    email, COUNT(email)
FROM
    contacts
GROUP BY 
    email
HAVING 
    COUNT(email) > 1;
Code language: SQL (Structured Query Language) (sql)

如您所见，我们有四行包含重复的电子邮件。

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;Code language: SQL (Structured Query Language) (sql)

此查询两次引用联系人表，因此，它使用表别名t1 和 t2。

输出是：

Query OK, 4 rows affected (0.10 sec)Code language: CSS (css)

它表明四行已被删除。您可以再次执行查找重复电子邮件的查询来验证删除：

SELECT 
    email, 
    COUNT(email)
FROM
    contacts
GROUP BY 
    email
HAVING 
    COUNT(email) > 1;
Code language: SQL (Structured Query Language) (sql)

查询返回一个空集，这意味着重复的行已被删除。

让我们验证contacts表中的数据：

SELECT * FROM contacts;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

请注意，您可以再次执行创建contacts表的脚本并测试此查询。以下输出显示删除重复行后contacts表的数据。

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

下面显示了使用中间表删除重复行的步骤：

1. 创建一个与要删除重复行的原始表结构相同的新表。
2. 将原始表中的不同行插入到直接表中。
3. 删除原始表并将直接表重命名为原始表。

以下查询说明了这些步骤：

步骤1.创建一个新表，其结构与原表相同：

CREATE TABLE source_copy LIKE source;
Code language: SQL (Structured Query Language) (sql)

步骤 2. 将原始表中不同的行插入到新表中：

INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values
Code language: SQL (Structured Query Language) (sql)

步骤3.删除原始表并将直接表重命名为原始表

DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
Code language: SQL (Structured Query Language) (sql)

例如，以下语句从contacts表中删除包含重复电子邮件的行：

-- step 1
CREATE TABLE contacts_temp 
LIKE contacts;

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


-- step 3
DROP TABLE contacts;

ALTER TABLE contacts_temp 
RENAME TO contacts;
Code language: SQL (Structured Query Language) (sql)

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

以下语句使用ROW_NUMBER()函数为每行分配一个连续的整数。如果电子邮件重复，行号将大于 1。

SELECT 
	id, 
    email, 
    ROW_NUMBER() OVER ( 
		PARTITION BY email 
        ORDER BY email
	) AS row_num 
FROM contacts;Code language: SQL (Structured Query Language) (sql)

以下语句返回重复行的 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;Code language: SQL (Structured Query Language) (sql)

您只需使用DELETE语句和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
);
Code language: SQL (Structured Query Language) (sql)

MySQL 发出以下消息：

4 row(s) affected

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