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

背景

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

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

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

-- 连表删除
delete t_user
from 
t_user,
(
	SELECT
	   min(id) id,
	   username
	  FROM
	   t_user
	  GROUP BY
	   username
	  HAVING
	   count(*) > 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
-- 多执行几次
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); -- 12
select 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的大小 默认1024
  show variables like 'group_concat_max_len';
  -- 临时修改当前连接
  SET SESSION group_concat_max_len=102400; 
  

select a.id from test_table_02 a
INNER JOIN (
	select ids from
		(
			-- 保留第一个 截取第2个到最后一个id
			select 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