Mysql 去除重复表中数据

查看表结构:

CREATE TABLE `netshopgoods` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) DEFAULT NULL,
  `goods_desc_y` mediumtext COLLATE utf8mb4_unicode_ci,
  `goods_desc` mediumtext COLLATE utf8mb4_unicode_ci,
  `goods_replace` mediumtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=212084 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

1.根据goods_id 列查询重复的数据(根据单列判断重复)

SELECT * FROM netshopgoods 
WHERE goods_id IN (SELECT goods_id FROM netshopgoods GROUP BY goods_id HAVING COUNT(goods_id) > 1)

结果:

2删除表中的重复数据(根据多列进行删除且保留ID最小的一条)

DELETE
FROM netshopgoods
WHERE (goods_id,XXX,字段名)IN(SELECT
                         goods_id,XXX,字段名
                        FROM (SELECT
                                goods_id,XXX,字段名
                              FROM netshopgoods
                              GROUP BY goods_id,XXX,字段名
                              HAVING COUNT( * ) > 1) a)
    AND ID NOT IN(SELECT
                    MIN(ID)
                  FROM (SELECT
                          MIN(ID) AS id
                        FROM netshopgoods
                        GROUP BY goods_id,XXX,字段名
                        HAVING COUNT( * ) > 1) b)

结果