如何在MySQL表中刪除重複行

本教程將向您展示在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','carine.schmitt@xuhuhu.com'),
       ('Jean','King','jean.king@gmail.com'),
       ('Peter','Ferguson','peter.ferguson@google.com'),
       ('Janine ','Labrune','janine.labrune@qq.com'),
       ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       ('Janine ','Labrune','janine.labrune@qq.com'),
       ('Susan','Nelson','susan.nelson@qq.com'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.com'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@qq.com'),
       ('Susan','Nelson','susan.nelson@qq.com'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

執行DELETE語句後，可以執行此腳本來重新創建測試數據。

以下查詢返回contacts表中的重複email值：

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

執行上面查詢語句，得到以下結果 -

+-------------------------+--------------+
| email                   | COUNT(email) |
+-------------------------+--------------+
| janine.labrune@qq.com   |            2 |
| roland.keitel@yahoo.com |            2 |
| susan.nelson@qq.com     |            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中的重複行。