删除重复数据保留最新记录_MySQL大量脏数据,如何只保留最新的一条？

系统存在一个缺陷，造成数据库记录出现重复现象，必须执行删除操作，仅保留其中记录时间最新的那条信息。

具体场景是这样的

有一笔订单需要参照附加费用清单,而一个订单编号的记录只能对应一个费用标识一次,然而在数据系统中,某个订单编号却与同一费用标识关联了多次

有人可能会提议，针对前述情况，可以构建一个包含多个字段的组合唯一索引，这样一来，即便程序存在缺陷，数据表内部也不会产生错误记录。

似乎这样就可以了，然而事情并没有那么简单。

我们的数据表在处理删除操作时，并不会永久移除记录，而是通过逻辑删除的方式进行管理，借助一个标记字段，用0和1来区分记录是否已被移除的状态。

当然 可以将 + + 合并成一个组合唯一索引

这样就ok了吗？

其实会出现另一个状况，就是针对同一个订单和同一笔费用，一旦初次删除操作完成，随后再次尝试删除时便会遭遇障碍，原因是该条记录已经被移除，无法进行第二次删除。

所以当时我们并没有建立联合唯一索引，才导致脏数据的产生。

事实上，对于之前提到的那个情况，互联网上存在一个相当不错的应对方法，具体来说，我们依然能够将订单编号、费用标识以及删除标记这三个元素拼接在一起，构建成一个复合型的唯一索引
但是移除时deleted不再恒为1，而是变为此刻的主键编号，只要deleted与0不相同就意味着处于移除情形，一旦执行移除操作那么deleted字段便取值为id

言归正传,接下来我们来讲下该如何修复脏数据的问题

我们先创建一张订单关联费用表

建立名为order_cost_detail的表格，包含以下内容，通过字段区分各个数据项，使用适当的数据类型存储信息，确保数据完整性和准确性，以便后续查询和管理。
标识符为整数类型，不允许为空，且自动递增，该字段作为主要依据
订单编号采用32位字符类型，不允许为空，并标注注释为订单号，
费用编号为整数类型，不可为空，并标注其含义为费用标识
费用代号是五十个字符的字符串，不能为空，默认值为空字符串，该字段用于记录费用标识。
资金数额固定为十位十进制数，其中两位用于表示小数部分，该字段不允许为空，并标注用途为记录金额，
生成时间 数据类型为日期时间 不允许为空 备注 创建时刻
删除状态，以二进制位表示，不允许为空，注释说明该字段代表是否已被移除，其中零代表未删除，一代表已删除，
主键是id, 采用B树索引方式
) 引擎为InnoDB, 自增起始值为1, 注释为'订单 - 费用表';

插入一些模拟数据

新增记录至`order_cost_detail`表时，需包含以下字段信息，即`id`字段，`order_no`字段，`cost_id`字段，`cost_name`字段，`money`字段，`create_time`字段，以及`deleted`字段
VALUES
编号为EX202208160000012-3的条目属于类型2，内容为停车费，金额是100.00元，记录时间是2022年8月19日11点30分48秒，状态码为0。
编号为EX202208160000012-4的条目，其类型为停车费，金额为100.00元，记录时间为2023年2月17日11时25分27秒，状态为未处理。
编号为三，标识码是EX202208160000012-4，类别是三，项目名称是停车费，金额是二百元，记录时间是二零二三年零二月十七日十一点二十五分二十八秒，状态码是零
编号为四，记录标识是EX202208170000002-1，状态为第一，费用类别为路桥费，金额为三百元，登记时间是二零二二年八月十九日十一点三十一分五十七秒，关联编号为零
编号为EX202208170000002-1的条目，类别标记为1，费用性质为路桥费，金额为450.00元，记录时间为2022-08-19 11:32:57，状态码为0
编号六，凭证号是EX202208180000002-1，类型是二号，费用类别为高速费，金额等于225.00元，记录时间是2022年8月19日11点35分41秒，状态码为零。

我们的目标清晰，需要去除重复且金额一致的订单记录，并且要留下最新的那条信息。

我们可以先用sql看下是否有重复数据

查询订单编号，商品名称，以及数量统计结果
FROM order_cost_detail
WHERE deleted = 0
GROUP BY order_no, cost_name
HAVING num > 1

运行结果

察觉到存在两条数据存在瑕疵，倘若实际制造环节仅发现此两条存在瑕疵，则处理起来较为容易，只需针对这两个订单进行查询，将重复信息予以清除即可。

但如果有几十条甚至上百条数据呢，总不能一条一条的删吧。

一般我们删除重复数据都会保留最新的那条，所以我们可以这样做

当主键具备自动增长特性，进行数据去重操作时，应保留主键值最大的那条记录，倘若主键不具备自动增长特性，则可依据生成时间，保留生成时间最晚的该条数据

我们先看下，我们需要删除的记录

select *
from order_cost_detail
where id not in (
	select max(id) as num
	from order_cost_detail
	where deleted = 0
	group by order_no, cost_name
)

查询结果

从结果分析出，确实需要移除这两个条目，接下来我们将实施移除步骤

sql如下

此处属于逻辑移除，即针对需移除的信息，添加标记deleted设为1
update order_cost_detail
set deleted = 1
where id in (
挑选编号，出自订单成本明细，这些编号不属于
查询订单成本明细表，筛选未删除的数据，按照订单号和成本名称分组，计算每组中的最大ID值，并将该值命名为数字
		)
	)

执行的时候发现报错了

无法在FROM子句中指定目标表order_cost_detail进行更新操作

这表示，不可以先从某个列表中取出部分数据，紧接着又去操作这个列表，也就是说，不能先根据某个属性的数据来进行判断，然后才去改变这个属性的数据。

这个问题在MySQL官网中有提到解决方案：拉到文档下面

处理办法：借助一个过渡表格，进行二次关联，能够防止此类偏差

update order_cost_detail
set deleted = 1
where id in (
select t.id
from
( 
select id from order_cost_detail where id not in (
查询订单成本明细表，筛选未删除的记录，按照订单号和成本名称分组，计算每组的最大ID值，并将该值命名为编号
	) t
)

执行成功

阿里巴巴手册索引规范，第一条就是

业务方面具备独有标志的指标，即便是由多个部分构成的字段，也务必创建唯一索引。

不要以为索引优化拖慢了效率，这种效率降低可以不必在意，但提升检索效率是显而易见的，而且即便在程序层面进行了周密处理

如果缺少唯一索引，对数据进行核对和管理，按照墨菲定律，必定会出现错误数据。