Skip to main content

MySQL中查找重复数据

数据重复的发生有很多原因，日常工作中查找重复数据也是很重要的一项技能。

创建一张测试表​

首先，创建一张名为 contacts 的表，它有四列：first_name、last_name 和 email 。

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表中插入一些记录

INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
       ('Jean','King','jean.king@me.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@comcast.net'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@me.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

从contacts表中查询数据

SELECT * FROM contacts
ORDER BY email;

在contacts表中，有一些行在first_name、last_name和email列中有重复的值。让我们学习如何找到它们。

在一列中查找重复值​

在表的一列中查找重复值时，可以按照以下步骤：

  1. 首先，使用GROUP BY子句按目标列对所有行进行分组，目标列是要检查是否重复的列。
  2. 然后，使用HAVING子句中的COUNT()函数检查是否有包含超过1个元素的组。

用sql语句描述就是：

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

通过使用此查询模板，可以在 contacts 中查找包含重复email的行，如下所示：

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

以上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子句中使用AND运算符。

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

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语句的执行结果如下图

在本教程中，学习了如何根据MySQL中一列或多列的值查找重复行。