# 找重复值

在本教程中，您将学习如何在MySQL中找到一个或多个列的重复值。

## 在开始之前

由于原因很多，数据库中的重复事件发生很多。查找重复值是使用数据库时必须处理的重要任务之一。

对于演示，我们将[创建](http://www.yiibai.com/mysql/create-table.html)一个名为`contacts`表，其中包含四个列：`id`，`first_name`，`last_name`和`email`。

```sql
USE testdb;

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
);
```

以下语句将行插入到`contacts`表中：

```sql
INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@qq.com'),
       ('Jean','King','jean.king@yiibai.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@qq.com'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@qq.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.comt'),
       ('Roland','Keitel','roland.keitel@yahoo.com');
```

然后，查询表中的数据如下 -

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

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

```sql
+----+------------+-----------------+--------------------------------+
| id | first_name | last_name       | email                          |
+----+------------+-----------------+--------------------------------+
|  1 | Carine     | Schmitt         | carine.schmitt@qq.com          |
|  2 | Jean       | King            | jean.king@yiibai.com               |
|  3 | Peter      | Ferguson        | peter.ferguson@google.com      |
|  4 | Janine     | Labrune         | janine.labrune@aol.com         |
|  5 | Jonas      | Bergulfsen      | jonas.bergulfsen@mac.com       |
|  6 | Janine     | Labrune         | janine.labrune@aol.com         |
|  7 | Susan      | Nelson          | susan.nelson@qq.com            |
|  8 | Zbyszek    | Piestrzeniewicz | zbyszek.piestrzeniewicz@qq.com |
|  9 | Roland     | Keitel          | roland.keitel@yahoo.com        |
| 10 | Julie      | Murphy          | julie.murphy@yahoo.com         |
| 11 | Kwai       | Lee             | kwai.lee@google.com            |
| 12 | Jean       | King            | jean.king@qq.com               |
| 13 | Susan      | Nelson          | susan.nelson@qq.comt           |
| 14 | Roland     | Keitel          | roland.keitel@yahoo.com        |
+----+------------+-----------------+--------------------------------+
14 rows in set
```

在`contacts`表中，有一些行在`first_name`，`last_name`和`email`列中具有重复的值，下面来看看如何查询它们。

**在一列中找到重复的值**

在基于一列的表中找到重复值，则使用以下语句：

```sql
SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;
```

如果表中出现多个值，则该值将被视为重复。在这个语句中，使用[COUNT](http://www.yiibai.com/mysql/count.html)函数的[GROUP BY](http://www.yiibai.com/mysql/group-by.html)子句来计算指定列(col)的值。[HAVING](http://www.yiibai.com/mysql/having.html)子句中的条件仅包含值`count`大于`1`的行，这些行是重复的行。

可以使用此查询在`contacts`表中查找具有重复`email`的所有行，如下所示：

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

以下显示查询的输出：

```sql
+-------------------------+--------------+
| email                   | COUNT(email) |
+-------------------------+--------------+
| janine.labrune@aol.com  |            2 |
| roland.keitel@yahoo.com |            2 |
+-------------------------+--------------+
2 rows in set
```

如上查询结果中可以看到，有一些行具有相同的电子邮件。

**在多个列中查找重复值**

有时，希望基于多个列而不是一个查找重复。在这种情况下，您可以使用以下查询：

```sql
SELECT 
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...

FROM
    table_name
GROUP BY 
    col1, 
    col2, ...
HAVING 
       (COUNT(col1) > 1) AND 
       (COUNT(col2) > 1) AND 
       ...
```

只有当列的组合重复时，行才被认为是重复的，所以在[HAVING](http://www.yiibai.com/mysql/having.html)子句中使用了[AND](http://www.yiibai.com/mysql/and.html)运算符。

例如，要使用`first_name`，`last_name`和`email`列中的重复值在`contacts`表中查找行，请使用以下查询：

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

执行上面查询后，得到以下输出：

```sql
+------------+-------------------+-----------+------------------+-------------------------+--------------+
| first_name | COUNT(first_name) | last_name | COUNT(last_name) | email                   | COUNT(email) |
+------------+-------------------+-----------+------------------+-------------------------+--------------+
| Janine     |                 2 | Labrune   |                2 | janine.labrune@aol.com  |            2 |
| Roland     |                 2 | Keitel    |                2 | roland.keitel@yahoo.com |            2 |
+------------+-------------------+-----------+------------------+-------------------------+--------------+
2 rows in set
```

在本教程中，您已经学会了如何根据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/zhao-zhong-fu-zhi.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.