删除重复行

本教程将向您展示在MySQL中删除重复行的各种方法。

在上一个教程中，我们向您展示了如何在表中找到重复的值。当确定了表中有重复的行，您可能需要删除它们来清理这些不必要的数据。

准备示例数据

以下脚本创建contacts表，并将示例数据插入到用于演示的contacts表中。

USE testdb;
DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) DEFAULT NULL,
    last_name VARCHAR(50) DEFAULT NULL, 
    email VARCHAR(255) NOT NULL
);

INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','[e-mail]'),
       ('Jean','King','[e-mail]'),
       ('Peter','Ferguson','[e-mail]'),
       ('Janine ','Labrune','[e-mail]'),
       ('Jonas ','Bergulfsen','[e-mail]'),
       ('Janine ','Labrune','[e-mail]'),
       ('Susan','Nelson','[e-mail]'),
       ('Zbyszek ','Piestrzeniewicz','[e-mail]'),
       ('Roland','Keitel','[e-mail]'),
       ('Julie','Murphy','[e-mail]'),
       ('Kwai','Lee','[e-mail]'),
       ('Jean','King','[e-mail]'),
       ('Susan','Nelson','[e-mail]'),
       ('Roland','Keitel','[e-mail]');

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

以下查询返回contacts表中的重复email值：

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

执行上面查询语句，得到以下结果 -

+-------------------------+--------------+
| email                   | COUNT(email) |
+-------------------------+--------------+
| [e-mail]   |            2 |
| [e-mail] |            2 |
| [e-mail]     |            2 |
+-------------------------+--------------+
3 rows in set

可以看到，表中有重复email行记录。

使用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;

Query OK, 3 rows affected

如上所示，有3行记录已被删除。我们再次执行查找重复的电子邮件的查询：

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

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

我们来查询验证contacts表中的数据：

SELECT 
    *
FROM
    contacts;

ID为4，7和9的行记录已被删除。

如果要删除重复的行并保留最低的ID，则可以使用以下语句：

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

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

使用直接表删除重复的行

以下是使用直接表删除重复行的步骤：

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

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

步骤1 -

CREATE TABLE source_copy FROM source;

步骤2 -

INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values

步骤3 -

DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;

例如，以下语句从contacts表中删除具有重复电子邮件(email)的行记录：

-- step 1
CREATE TABLE contacts_temp 
LIKE contacts;

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

-- step 3
DROP TABLE contacts;

ALTER TABLE contacts_temp 
RENAME TO contacts;

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