mysql清理重复数据，并保留最新一条

背景

之前，数据是通过表单提交
后来增加了excel批量导入的功能，但是这个接口没有判断数据重复，覆盖更新
导致线上环境出现大量重复数据
如果要保证数据库不出现重复数据，可以添加唯一索引来限制

参考：Mysql重复数据查重保留一条(id自增)

• 只适合id自增的表，我的表id是uuid，所以需要借助其他的手段实现min函数

-- 连表删除delete t_userfrom t_user,(SELECT   min(id) id,   username  FROM   t_user  GROUP BY   username  HAVING   count(*) > 1) t2where t_user.username = t2.username-- 删除重复的比最小id大的数据，即保留id最小的那条数据and t_user.id > t2.id

改造：主键为uuid

• 数据唯一判断，4个字段确定一条记录：type产品类型，name产品名称，customer客户，year_month年月
• created_time 创建时间，据此排序分辨新旧

简单版

• 一次清除每组一条重复数据，需要多执行几次，适合数据量少的情况

-- 每次都是取的最早的id-- 多执行几次DELETE from test_table_02 where id in (SELECT SUBSTRING_INDEX(ids, ',', 1) id from (select GROUP_CONCAT(id ORDER BY created_time) ids, type, name, customer, `year_month`, count(1) c from test_table_02 GROUP BY type, name, customer, `year_month`having c > 1) aaaa)

• GROUP_CONCAT(id ORDER BY created_time) 聚合每组id，并根据创建时间正序，第一个即为最早的。倒序，则第一个为最新的。
• SUBSTRING_INDEX(ids, ',', 1) 截取第一个id

扩展：截取第一个id的2种方式

方式1：使用SUBSTRING_INDEX() 函数

set @ids = '12,3,4,5,6';-- SUBSTRING_INDEX() 函数返回一个字符串中指定分隔符出现在指定次数【之前】的子字符串-- SUBSTRING_INDEX(str, delim, count)-- 从头开始，截取到第几个分隔符select SUBSTRING_INDEX(@ids, ',', 1); -- 12select SUBSTRING_INDEX(@ids, ',', 2); -- 12,3

方式2：INSTR() 和 LEFT() 函数结合

set @ids = '12,3,4,5,6';-- INSTR() 函数返回一个子字符串在一个字符串中【第一次】出现的位置的数字索引。 -- INSTR() 函数是不区分大小写的。-- 与java不同，下标从1开始，即为到这个字符位置的字符串长度select INSTR(@ids,','); -- 3-- LEFT() 函数从指定字符串的左侧 返回指定数量的字符组成的字符串。-- LEFT(string, length)-- 从头开始，截取指定长度select left(@ids, INSTR(@ids,',')-1); -- 12

加强版：一次性全部删除

• 要注意的是，group_concat有长度限制，超过会被截掉，可能会删不完全

  • 解决group_concat默认长度限制
  • 28_mysql的group_concat函数设置最大长度

  -- 查看数据库中group_concat_max_len的大小 默认1024show variables like 'group_concat_max_len';-- 临时修改当前连接SET SESSioN group_concat_max_len=102400; 

select a.id from test_table_02 aINNER JOIN (select ids from(-- 保留第一个 截取第2个到最后一个idselect SUBSTR(ids,INSTR(ids,',')+1) ids from (-- 保留最后一个 截取第1个到倒数第2个id-- select left(ids, (CHAR_LENGTH(ids)-INSTR(reverse(ids),',') ) ) from (select GROUP_CONCAT(id ORDER BY created_time) ids, type, name, customer, `year_month`, count(1) c from test_table_02 GROUP BY type, name, customer, `year_month`having c > 1) aaaa) bbb) b on FIND_IN_SET(a.id, b.ids)

• SUBSTR(ids,INSTR(ids,',')+1) 保留第一个 截取第2个到最后一个id
• left(ids, (CHAR_LENGTH(ids)-INSTR(reverse(ids), ',') ) ) 保留最后一个 截取第1个到倒数第2个id
  • reverse(ids) 反转字符串
  • INSTR(reverse(ids), ',') 获取最后一个id的长度，包括了逗号的长度
  • CHAR_LENGTH(ids) 获取字符长度
  • CHAR_LENGTH(ids)-INSTR(reverse(ids), ',') 字符总长度 - 最后一个id+逗号的长度 = 第1个到倒数第2个id的长度

扩展

求长度时，注意length()、char_length()的区别

• MySQL中length()、char_length()的区别和用法

• char_length(str) 字符长度

  • 不管汉字还是数字或者是字母都算是一个字符

• length(str) 计算单位：字节

  • utf8编码：一个汉字三个字节，一个数字或字母一个字节。
  • gbk编码：一个汉字两个字节，一个数字或字母一个字节。

• 没有中文之类的，LENGTH和CHAR_LENGTH结果大概是一致的

  -- 6 计算字节select LENGTH('你好')-- 2 计算字符select CHAR_LENGTH('你好')

保留第一个：使用SUBSTR() 函数

set @ids = '12,3,4,5,6';-- Mysql SUBSTR() 函数从一个字符串中返回一个【从指定位置开始】的【指定长度】的子字符串。-- 可以传2个参数，也可以传3个，不传第3个参数len，指定长度，就是到结尾-- SUBSTR(str, pos)-- SUBSTR(str, pos, len)-- 从第2个id开始取到结尾 保留第一个select SUBSTR(@ids, INSTR(@ids,',') + 1) -- 3,4,5,6

保留最后一个：left() 函数

set @ids = '12,3,4,5,6';-- 从头取到倒数第二个 保留最后一个-- REVERSE() 函数返回反转后的字符串-- CHAR_LENGTH() 返回给定字符串的长度，注意与 LENGTH() 区别，LENGTH()函数返回指定字符串的以字节为单位的长度 -- UTF-8，一个中文字占用 3 个字节select left(@ids, (CHAR_LENGTH(@ids)-INSTR(reverse(@ids),','))) -- 12,3,4,5

来源地址：https://blog.csdn.net/weixin_44174211/article/details/129412634