Mysql删除表里的重复数据并保留一条唯一的数据

说明:工作中有时会面临需要删除数据表中的重复数。一条sql解决更方便！

子查询是根据唯一性字段分组,SUBSTRING_INDEX和GROUP_CONCAT结合使用可以得到需要删除的主键ID，GROUP_CONCAT也可以进行排序，按照自己的需求来保留一条数据。
方案一：

DELETE purchase_order_logistics 
FROM
	purchase_order_logistics
	INNER JOIN (
	SELECT
		SUBSTRING_INDEX(
			GROUP_CONCAT( pol_id ),
			',',
		count( 1 )-1) AS delete_id_str 
	FROM
		purchase_order_logistics 
	GROUP BY
		pol_po_id,
		pol_logistics_num 
	HAVING
		count( 1 )> 1 
	) AS a ON pol_id IN (
	delete_id_str)

方案二:

DELETE 
v2_unify_no
FROM
	v2_unify_no
WHERE
	un_id NOT IN ( select un_id from (SELECT min(un_id) as un_id FROM v2_unify_no
GROUP BY un_ruku_no HAVING count( un_id )> 1) as a ) 
	AND un_ruku_no IN (select un_ruku_no from (SELECT un_ruku_no FROM v2_unify_no
GROUP BY un_ruku_no HAVING count( un_id )> 1) as b)

注意
group_concat()函数有最大长度限制,默认是1024个字符。可以修改mysql的系统变量

show variables like 'group_concat_max_len';

在[mysqld]下新增配置：

group_concat_max_len = 102400;

这个变量的数字是字符长度,默认是1024;大家可以根据实际的使用环境进行调整

欢迎大家交流!!!