# 删除重复行

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

在上一个教程中，我们向您展示了如何在表中[找到重复的值](http://www.yiibai.com/mysql/find-duplicate-values.html)。当确定了表中有重复的行，您可能需要删除它们来清理这些不必要的数据。

## 准备示例数据

以下脚本创建`contacts`表，并将示例数据[插入](http://www.yiibai.com/mysql/insert-statement.html)到用于演示的`contacts`表中。

```sql
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@yiibai.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](http://www.yiibai.com/mysql/delete-statement.html)语句后，可以执行此脚本来重新创建测试数据。

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

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

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

```sql
+-------------------------+--------------+
| 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](http://www.yiibai.com/mysql/delete-join.html)语句。

以下语句删除重复的行并保持最高的ID：

```sql
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`行记录已被删除。我们再次执行查找重复的电子邮件的查询：

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

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

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

```sql
SELECT 
    *
FROM
    contacts;
```

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

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

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

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

**使用直接表删除重复的行**

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

* [创建一个新表](http://www.yiibai.com/mysql/create-table.html)，其结构与要删除重复行的原始表相同。
* 将原始表中的[不同行](http://www.yiibai.com/mysql/insert-statement.html)插入直接表。
* [删除原始表](http://www.yiibai.com/mysql/drop-table.html)并将直接表重命名为[原始表](http://www.yiibai.com/mysql/rename-table.html)。

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

**步骤1** -

```sql
CREATE TABLE source_copy FROM source;
```

**步骤2** -

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

**步骤3** -

```sql
DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
```

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

```sql
-- 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中的重复行。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://hezhiqiang-book.gitbook.io/mysql/di-er-zhang/shan-chu-zhong-fu-hang.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.