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

背景

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

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

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

-- 连表删除delete t_user
from 
t_user,(SELECTmin(id) id,
       username
      FROM
       t_user
      GROUPBY
       username
      HAVINGcount(*)>1) t2
where t_user.username = t2.username
-- 删除重复的比最小id大的数据，即保留id最小的那条数据and t_user.id > t2.id

改造：主键为uuid

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

简单版

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

-- 每次都是取的最早的id-- 多执行几次DELETEfrom test_table_02 where id in(SELECT SUBSTRING_INDEX(ids,',',1) id from(select GROUP_CONCAT(id ORDERBY created_time) ids,type, name, customer,`year_month`,count(1) c 
        from test_table_02 
        GROUPBYtype, 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)-- 从头开始，截取指定长度selectleft(@ids, INSTR(@ids,',')-1);-- 12

加强版：一次性全部删除

• 要注意的是，group_concat有长度限制，超过会被截掉，可能会删不完全- 解决group_concat默认长度限制- 28_mysql的group_concat函数设置最大长度-- 查看数据库中group_concat_max_len的大小 默认1024show variables like'group_concat_max_len';-- 临时修改当前连接SETSESSION group_concat_max_len=102400;

select a.id from test_table_02 a
INNERJOIN(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 ORDERBY created_time) ids,type, name, customer,`year_month`,count(1) c 
                    from test_table_02 
                    GROUPBYtype, 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 个字节selectleft(@ids,(CHAR_LENGTH(@ids)-INSTR(reverse(@ids),',')))-- 12,3,4,5