mysql清除表重复数据

表信息

CREATE TABLE `rmb_item_material` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `store_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商店id',
  `item_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id',
  `sequence` int(11) NOT NULL DEFAULT '0' COMMENT '排序序号',
  `pin_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '置顶标识',
  `pin_time` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '置顶时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_item_material_sid_iid` (`store_id`,`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1048 DEFAULT CHARSET=utf8mb4 COMMENT='商品物料表';

 因为索引idx_item_material_sid_iid并不是唯一索引，导致上次执行数据迁移sql时使生产库出现了部分脏数据；现要清理重复数据。

先查看有哪些数据重复

select store_id, item_id,GROUP_CONCAT(id) from rmb_item_material GROUP BY store_id, item_id HAVING count(1) > 1

如图，store_id和item_id都相等的数据id有这些，现要保留每组最小id的数据，其余重复数据删除

SELECT
    a.id
FROM
    rmb_item_material a
INNER JOIN 
    ( SELECT store_id, item_id FROM rmb_item_material GROUP BY store_id, item_id HAVING count( 1 ) > 1 ) b 
ON 
    ( a.store_id = b.store_id AND a.item_id = b.item_id )
WHERE
    id NOT IN ( SELECT MIN( id ) AS id FROM rmb_item_material GROUP BY store_id, item_id HAVING count( 1 ) > 1 )

DELETE
FROM
	rmb_item_material
WHERE
	id IN (
	SELECT temp.id FROM
	(
		SELECT
			a.id
		FROM
			rmb_item_material a
			INNER JOIN ( SELECT store_id, item_id FROM rmb_item_material GROUP BY store_id, item_id HAVING count( 1 ) > 1 ) b ON ( a.store_id = b.store_id AND a.item_id = b.item_id )
		WHERE
			id NOT IN ( SELECT MIN( id ) AS id FROM rmb_item_material GROUP BY store_id, item_id HAVING count( 1 ) > 1 )
	) temp
	);

再drop掉原来的索引，重新创建成唯一索引

ALTER TABLE rmb_item_material DROP INDEX `idx_item_material_sid_iid`;

ALTER TABLE `rmb_item_material` ADD UNIQUE `udx_item_material_sid_iid` ( `store_id`, `item_id` );