如何在 MySQL / MariaDB 中查找和删除重复记录? - 4 种 MySQL 数据去重法

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

如何在 MySQL 中查找和删除重复记录? - 4 种 MySQL 数据去重法

MySQL 数据去重可是 MySQL 必会技能之一。比如,在数据库中找出使用相同 email 但不同 username 的用户等应用场景。

本教程将介绍 4 种删除重复数据的方法。SELECT DISTINCTGROUP BYINNER JOINROW_NUMBER() ,它们各有各的特点,请复制教程代码到你的 MySQL sell 中,边学边看效果,实践出真知。

先决条件

  • 一台安装好 MySQL Server 的服务器(如果你还没有配置 MySQL,可按照我写的教程部署 MySQL
  • 拥有适当权限的 Root 账号

准备数据

请登录你的 MySQL 服务器,然后将以下代码贴进去执行。我们先来创建教程示例数据:

CREATE DATABASE kalacloud_record;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO users (first_name,last_name,email) 
VALUES ('Chuan ','Jiang','HiJiangChuan@gmail.com'),
       ('Chuan ','Jiang','HiJiangChuan@gmail.com'),
       ('Ch. ','Jiang','HiJiangChuan@gmail.com'),
       ('Ke','Xie','xieke@sina.com'),
       ('Ke','Xie','xieke@qq.com'),
       ('Amei','Song','amei@163.com');

select * from users;
# kalacloud.com

创建教程示例数据

我们可以看到,示例数据中有多组不同程度的重复数据,接下来我们使用 4 种方法演示如何去重。

1. 使用 SELECT DISTINCT 命令去重

需求:查找 users 表中的数据,将记录中 first_namelast_nameemail 这三列均重复的记录删掉,并重新整理 ID,使 ID 连续。

我们先新建一个表,然后使用 SELECT DISTINCT 去重,并把去重后的数据存进新表。

CREATE TABLE users_copy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO users_copy (first_name,last_name,email) 
SELECT DISTINCT first_name,last_name,email FROM users;

我们用 select 来查看一下去重的效果:

select * from users_copy;

SELECT DISTINCT 去重

从返回结果看,与 ID 1 一模一样的 ID 2 被删掉,然后表格的 ID 进行了重置。

最后我们删掉 users 表,将 users_copy 改名为 users ,使用这种方法达到将原表去重的目的。

drop tables users;
alter table users_copy rename to users;
select * from users;

SELECT DISTINCT 返回值

在这个示例中,我们删除了记录中,姓名和邮箱完全一致的重复用户信息,即删除所有列均是重复的记录。如果我们只想查某几个列是否重复应该怎么做呢?

2.使用 group by 命令去重

问题:只想查同一个姓和名,但邮箱不同(用户使用多个邮箱注册)的记录,然后删掉最有一条。

用上文的数据继续操作,我们使用 group by 去重,同样用创建中间表的方案:

CREATE TABLE users_group_by (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO users_group_by (first_name,last_name,email) 
SELECT first_name,last_name,ANY_VALUE(email)
FROM users
group by first_name,last_name;

我们来看一下效果:

SELECT * FROM users_group_by;

group_by 去重法

最后我们删掉 users 表,将 users_group_by 改名为 users ,使用这种方法达到将原表去重的目的。

drop tables users;
alter table users_group_by rename to users;
select * from users;

3. 使用 INNER JOIN 删除重复行

我们使用 DELETEINNER JOIN 语句的组合对 MySQL 进行去重。使用这个组合时,我们的表需要有至少一个「唯一」的列(例如主键)

先来重置一下示例数据:

DROP TABLE users;
DROP TABLE users_copy;
DROP TABLE users_group_by;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO users (first_name,last_name,email) 
VALUES ('Chuan ','Jiang','HiJiangChuan@gmail.com'),
       ('Chuan ','Jiang','HiJiangChuan@gmail.com'),
       ('Ch. ','Jiang','HiJiangChuan@gmail.com'),
       ('ke','xie','xieke@caowutech.com'),
       ('ke','xie','xieke@qq.com'),
       ('amei','song','amei@163.com');

select * from users;
# kalacloud.com

需求:找出 users 表中重复 first_namelast_name ,并将这一行记录删掉:

DELETE t1 FROM users t1
	INNER JOIN users t2
WHERE
	t1.id < t2.id
AND t1.first_name = t2.first_name AND t1.last_name = t2.last_name;
select * from users;

注意看 t1.id < t2.id ,SQL 会删掉两组记录对比中,ID 较小的记录,即重复记录中第一组数据,把这个符号反过来,则会保留重复记录中最后一组数据。

inner_join 去重法

4. 使用 ROW_NUMBER() 删除重复记录

最后,我们来说说 ROW_NUMBER() 函数,从 MySQL 8.0 开始才支持此函数。

需求:找出 users 表中重复的 last_nameemail,并将这一行记录删掉:

DELETE FROM users
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id, ROW_NUMBER () Over (PARTITION BY last_name,email ORDER BY id) as r 
        from users
    ) t
    WHERE r > 1
);
select * from users;

ROW_NUMBER 去重法

最后,我们获得了一组非常干净的 users 表。 另外返回结果的自增 ID 是乱序,除了前文所写的新建表再冲刺你导入数据,还有其他方法可以重置自增 ID , 具体可看我这篇教程《MySQL 重置自增 ID (AUTO_INCREMENT)教程 - 完美保留表数据的终极解决方案

5.总结

至此,MySQL 中 4 种记录去重的方法就演示完了,希望大家能根据本教程的示例举一反三。

  • SELECT DISTINCT
  • group by
  • INNER JOIN
  • ROW_NUMBER()

接着推荐一下卡拉云,只要你会写 MySQL ,就能使用卡拉云搭建自己的数据工具,比如,数据看板,企业 CRM、ERP,权限管理后台,对账系统等。

卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎免费试用卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API

卡拉云可根据公司工作流需求,轻松搭建数据看板或其他内部工具,并且可一键分享给组内的小伙伴。

卡拉云快速搭建内部工具

下图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。欢迎免费试用卡拉云

使用卡拉云快速搭建内部工具

更多数据库相关教程可访问 卡拉云 查看。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9