使用GROUP BY和HAVING子句查找和删除MySQL中的重复行

有时,我们可能会在MySQL数据库表中看到相同记录的多个副本。这通常发生在从不具有关系数据库特性的外部数据源(如电子表格应用程序)导入数据之后。拥有相同记录的相同副本可能会对应用程序和业务逻辑产生负面影响。例如,如果客户在我们的发票软件中多次注册,这可能会使分配信用限额的过程复杂化。要解决这个问题,我们必须从数据库中删除重复的客户记录。

mysql

 

 

在本文中,我们将创建一个test_db数据库和一个示例customers表。然后,我们将使用包含一些重复项的客户详细信息的随机列表填充该表。最后,我们将使用MySQL GROUP BYHAVING子句来查找和删除重复的行。

创建一个test_db数据库

首先,SSH到您的服务器并运行下面的命令以root身份登录到MySQL数据库服务器。

$ sudo mysql -u root -p

当出现提示时,输入MySQL服务器的root密码并按enter继续。然后,运行下面的命令创建test_db数据库。

mysql> CREATE DATABASE test_db;

使用test_db数据库。

mysql> USE test_db;

接下来,设置一个customers表。在这个表中,您将通过引用customer_id列来惟一地标识客户,该列应该是自动填充的,因为我们将使用一个主键语句来定义它。然后,该表将记录客户的first_name、last_name和电话号码。

创建clients表。

mysql> CREATE TABLE customers (       customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,       first_name VARCHAR(50),       last_name VARCHAR(50),       phone VARCHAR(15)       ) ENGINE = InnoDB;

接下来,用一些记录填充customers表。请注意,我们可以在该表中多次插入同一个客户的详细信息。如前所述,我们可能要从缺乏关系数据库功能的外部数据源导入一些记录。

一个接一个地运行插入命令来填充表。

mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('JOHN', 'DOE', '111111');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('MARY', 'SMITH', '222222');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('JACOB', 'JAMES', '444444');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('STEVE', 'JACKES', '888888');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('MARY', 'SMITH', '222222');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('RIAN', 'WHITE', '101010');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('APPLE', 'GRABRIEL', '242424');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('RIAN', 'WHITE', '101010');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('JACOB', 'JAMES', '444444');mysql> INSERT INTO customers(first_name, last_name, phone) VALUES ('JOHN', 'DOE', '111111');

在每个INSERT命令之后,我们应该会得到以下输出,表明成功插入了记录。

...Query OK, 1 row affected (0.01 sec)

通过对customers表运行以下SELECT语句,确保客户的详细信息已经到位。

mysql> SELECT       customer_id,       first_name,       last_name,       phone       FROM customers;

确认下面的输出。

+-------------+------------+-----------+--------+| customer_id | first_name | last_name | phone  |+-------------+------------+-----------+--------+|           1 | JOHN       | DOE       | 111111 ||           2 | MARY       | SMITH     | 222222 ||           3 | JACOB      | JAMES     | 444444 ||           4 | STEVE      | JACKES    | 888888 ||           5 | MARY       | SMITH     | 222222 ||           6 | RIAN       | WHITE     | 101010 ||           7 | APPLE      | GRABRIEL  | 242424 ||           8 | RIAN       | WHITE     | 101010 ||           9 | JACOB      | JAMES     | 444444 ||          10 | JOHN       | DOE       | 111111 |+-------------+------------+-----------+--------+10 rows in set (0.00 sec)

从上面的输出中可以看到,JOHN DOE, MARY SMITH, RIAN WHITE和JACOB JAMES的记录被重复了。我们可以通过物理检查上面SELECT语句的输出来注意到这个差异,因为您的表中只有几条记录。

在生产环境中,我们的表可能包含数千或数百万条记录,手动查找并消除重复可能不是一个可行的解决方案。在下一步中,我们将学习如何使用单个SQL语句完成此操作。

确定重复行

在MySQL中,可以通过对目标列执行GROUP by子句来找到重复的行,然后使用HAVING子句检查组中有多于1条记录。

例如,要在示例customers表中查找副本,可以使用MySQL CONCAT语句将first_name、last_name和phone字段连接到一个派生列,您将使用该派生列对记录进行分组。然后,使用HAVING子句检查有多个记录的任何结果组。

完成后,我们的SQL语法应该类似于下面的语句。

mysql> SELECT       customer_id,       first_name,       last_name,       phone,       COUNT(CONCAT(first_name, last_name, phone)) as total_count       FROM       customers       GROUP BY       CONCAT(first_name, last_name, phone)       HAVING total_count > 1 ;

执行上面的命令。然后,检查下面的输出。从下面的列表中可以看到,SQL命令检索了拥有多条记录的所有客户。

+-------------+------------+-----------+--------+-------------+| customer_id | first_name | last_name | phone  | total_count |+-------------+------------+-----------+--------+-------------+|           1 | JOHN       | DOE       | 111111 |           2 ||           2 | MARY       | SMITH     | 222222 |           2 ||           3 | JACOB      | JAMES     | 444444 |           2 ||           6 | RIAN       | WHITE     | 101010 |           2 |+-------------+------------+-----------+--------+-------------+4 rows in set (0.01 sec)

下一步是确定删除副本时应该保留的行。使用下面的命令获取这些行的主键。

mysql> SELECT MIN(customer_id)       FROM CUSTOMERS       GROUP BY CONCAT(first_name, last_name, phone);

从本质上讲,MySQL MIN()函数在按照连接的列分组时,返回每组客户记录中的第一条记录。如果一个客户名重复了几次,MIN()函数将确保只检索每个组中最先出现的记录的主键,如下所示。

+------------------+| MIN(customer_id) |+------------------+|                1 ||                2 ||                3 ||                4 ||                6 ||                7 |+------------------+6 rows in set (0.00 sec)

删除重复的行

一旦确定了customer_id的干净列表,通过告诉MySQL删除客户表(从客户....删除)中的所有记录来删除重复的行除了那些出现在白名单中的。换句话说,删除客户表中的所有内容,除了白名单记录(不在表中)。

请注意,在MySQL中,我们不能在同一个SQL语句中对同一个表使用SELECT命令。为了避免遇到错误,可以嵌套....选择MIN()…语句更深一层,如下所示。

mysql> DELETE FROM customers       WHERE customer_id NOT IN       (           SELECT           customer_id           FROM           (               SELECT MIN(customer_id) as customer_id               FROM CUSTOMERS               GROUP BY CONCAT(first_name, last_name, phone)           ) AS duplicate_customer_ids       );

一旦你执行上面的命令,MySQL服务器应该删除4个重复的记录,我们可以从下面的输出中确认。

Query OK, 4 rows affected (0.01 sec)

通过执行GROUP by并再次使用语句,确保您的customers表不再包含任何副本。

mysql> SELECT       customer_id,       first_name,       last_name,       phone,       COUNT(CONCAT(first_name, last_name, phone)) as total_count       FROM       customers       GROUP BY       CONCAT(first_name, last_name, phone)       HAVING total_count > 1 ;

这一次,应该得到一个空集,因为没有重复。

Empty set (0.00 sec)

再次查询customers表。

mysql> SELECT       customer_id,       first_name,       last_name,       phone       FROM customers;

即使通过实际检查下面的列表,我们也可以看到重复的记录已经被完全删除。

+-------------+------------+-----------+--------+| customer_id | first_name | last_name | phone  |+-------------+------------+-----------+--------+|           1 | JOHN       | DOE       | 111111 ||           2 | MARY       | SMITH     | 222222 ||           3 | JACOB      | JAMES     | 444444 ||           4 | STEVE      | JACKES    | 888888 ||           6 | RIAN       | WHITE     | 101010 ||           7 | APPLE      | GRABRIEL  | 242424 |+-------------+------------+-----------+--------+6 rows in set (0.00 sec)

在本文中,我们已经学习了如何使用MySQL GROUP BY和HAVING子句来查找和删除数据库中的重复记录。每次从非关系数据源导入数据时都使用该逻辑,以消除可能干扰业务逻辑的相同行。

未经允许不得转载:便宜VPS网 » 使用GROUP BY和HAVING子句查找和删除MySQL中的重复行