首页 > 数据库 >MySQL删除重复记录并且只保留最新一条

MySQL删除重复记录并且只保留最新一条

时间:2024-08-07 14:55:45浏览次数:17  
标签:删除 查询 MySQL test table 重复记录 id SELECT 1000

目录

测试表

方式一:分组查询出每组最大的ID,其余的删除

方式二:先标记重复待清理的数据,检查后清理

附言

查询所有重复的列:这里给到MySQL5.7 和 8.0版本的查询方式


在开发过程中,因为某些问题可能会导致同一条数据在表中重复出现,此时我们需要申请权限走SQL去修复,下面介绍下具体修复流程

测试表

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `a_id` int NULL DEFAULT NULL,
  `a_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test_table
-- ----------------------------
INSERT INTO `test_table` VALUES (1, 1000, '1000名字');
INSERT INTO `test_table` VALUES (2, 1000, '1000名字');
INSERT INTO `test_table` VALUES (3, 1000, '1000名字');
INSERT INTO `test_table` VALUES (4, 2000, '2000名字');

这里可以看到a_id列有2条重复的数据,我们仅保留最新的一条数据,下面我们开始介绍

方式一:分组查询出每组最大的ID,其余的删除

# 备份表
create table test_table202408207 SELECT * from test_table;

# 执行清理
DELETE a 
FROM
	test_table a 
WHERE
	NOT EXISTS (
	SELECT
		1 
	FROM
		( SELECT MAX( id ) AS max_id FROM test_table GROUP BY a_id ) AS b 
	WHERE
		a.id = b.max_id 
	);

方式二:先标记重复待清理的数据,检查后清理

# 备份表
create table test_table_202408207 SELECT * from test_table;

# 给原表添加一个辅助列,比如叫`to_delete`
ALTER TABLE test_table ADD COLUMN to_delete BOOLEAN NOT NULL DEFAULT FALSE;

# 使用自连接和分组来标记重复记录
UPDATE test_table a
JOIN (
    SELECT 
        a_id,
		MAX(id) AS max_id
    FROM 
        test_table
    GROUP BY 
        a_id
    HAVING COUNT(*) > 1
) b ON a.a_id = b.a_id
SET a.to_delete = TRUE
WHERE a.id <> b.max_id;


# 删除被标记为删除的记录
DELETE FROM test_table WHERE to_delete = TRUE;

# 删除辅助列
ALTER TABLE test_table DROP COLUMN to_delete;

附言

查询所有重复的列:这里给到MySQL5.7 和 8.0版本的查询方式
# 5.7版本查询方式(包含所有重复行)
# 若数据量量稍大一些,这里的查询将会非常的慢
SELECT
	a.* 
FROM
	test_table a
	JOIN ( SELECT a_id FROM test_table GROUP BY a_id HAVING COUNT(*) > 1 ) b ON a.a_id = b.a_id;

# 8.0版本查询方式(不包含重复的最新行)
SELECT
	* 
FROM
	( SELECT a.*, ROW_NUMBER() OVER ( PARTITION BY a_id ORDER BY id DESC ) AS rn FROM test_table a ) AS subquery 
WHERE
	subquery.rn > 1;

标签:删除,查询,MySQL,test,table,重复记录,id,SELECT,1000
From: https://blog.csdn.net/weixin_44700876/article/details/140990656

相关文章

  • 使用python读取mysql数据,并记录到本地的文件中
    上次写过一次读取sqlserver数据,写入本地文件。今天分享一下mysql的。原理相似,希望对大家有小小的帮忙PS,我是3.6.13版本python,上一版本用包mysql-connector,一直不成功,查询官方文档,发现这个版本的PYTHON简直是奇葩的存在了。基本所有版本都支持,就是几个小版本排除在外了。......
  • MySQL 备库可以设置 sync_binlog 非 1 吗?【转】
    众所周知,防止断电丢失Binlog、故障恢复过程丢失数据,MySQL主库必须设置sync_binlog=1。那么作为备库可以例外吗?我们的第一反应当然是不行,既然主库会丢数据,备库自然一样。但其实不然,备库丢了数据是可以重新从主库上复制的,只要这个复制的位置和备库本身数据的位置一致就OK了,它......
  • mysql-基础
    目录MySQL数据库的好处数据库相关概念数据库存储数据的特点MySQL产品的介绍和安装MySQL服务的启动和停止MySQL服务的登录和退出MySQL的常见命令MySQL的语法规范SQL的语言分类SQL的常见命令DQL语言的学习进阶1:基础查询进阶2:条件查询进阶3:排序查询进阶4:常见函数进阶5:分组查询进阶6:多......
  • 【数据结构与算法】删除循环队列中第k个元素的算法 C++实现(循环队列+模运算)
    数组a[MaxSize]用作一个循环队列,front指向循环队列中队头元素的前一个位置,rear指向队尾元素的位置。设计删除队列中第k个元素的算法。思路首先,判断kkk是否在有效范围内......
  • vue|el-table表格添加一行删除一行并且验证必填
    我们在工作中,难免会遇到一些特殊的场景。比如动态表格的实现,主要的实现就是可以增加删除列,并且需要对数据进行验证。如何在vue中使用el-table添加一行删除一行并且验证必填呢?请看VCR下面是代码示例:<template><divstyle="display:flex;justify-content:center;ali......
  • Ubuntu 安装 mysql
    Ubuntu安装mysql参考教程:Ubuntu18.04安装MySQL1.安装MySQL在Ubuntu中,默认情况下,只有最新版本的MySQL包含在APT软件包存储库中,要安装它,只需更新服务器上的包索引并安装默认包apt-get。(base)simulate@simulate-VirtualBox:~$sudoapt-getupdate(base)simulate@simu......
  • 2024最新Mysql锁机制与优化实践以及MVCC底层原理剖析
    锁机制详解锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除了传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一......
  • 2024MySQL最新索引优化实战二
    分页查询优化示例表:CREATETABLE`employees`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(24)NOTNULLDEFAULT''COMMENT'姓名',`age`int(11)NOTNULLDEFAULT'0'COMMENT'年龄',`position`varchar(20)NOTN......
  • mysql实现高可用架构之MHA
    mysql实现高可用架构之MHA一、简介MHA(MasterHA)是一款开源的MySQL的高可用程序,它为MySQL主从复制架构提供了automatingmasterfailover功能。MHA在监控到master节点故障时,会提升其中拥有最新数据的slave节点成为新的master节点,在此期间,MHA会通过于其它从节......
  • MYSQL死锁分析案例二(高并发增删改同一条记录)
    1、建表CREATETABLE`t1`(`id`intNOTNULL,`name`varchar(200)DEFAULTNULL,`age`intDEFAULTNULL,PRIMARYKEY(`id`),KEY`idx111`(`name`),KEY`idx_age`(`age`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci2、数据......