如何在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','[email protected]'),
       ('Jean','King','[email protected]'),
       ('Peter','Ferguson','[email protected]'),
       ('Janine ','Labrune','[email protected]'),
       ('Jonas ','Bergulfsen','[email protected]'),
       ('Janine ','Labrune','[email protected]'),
       ('Susan','Nelson','[email protected]'),
       ('Zbyszek ','Piestrzeniewicz','[email protected]'),
       ('Roland','Keitel','[email protected]'),
       ('Julie','Murphy','[email protected]'),
       ('Kwai','Lee','[email protected]'),
       ('Jean','King','[email protected]'),
       ('Susan','Nelson','[email protected]'),
       ('Roland','Keitel','[email protected]');

執行DELETE語句後，可以執行此指令碼來重新建立測試資料。

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

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

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

+-------------------------+--------------+
| email                   | COUNT(email) |
+-------------------------+--------------+
| [email protected]   |            2 |
| [email protected] |            2 |
| [email protected]     |            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中的重複行。