MySQL数据记录去除重复记录的方法
表结构
CREATE TABLE `serverfault_origin` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
查看重复
查看pid
字段重复情况。
SELECT pid, COUNT(pid) FROM hivedata GROUP BY pid HAVING COUNT(pid) > 1;
删除重复
删除pid
字段重复记录,保留id
较大的记录。
DELETE t1 FROM hivedata t1 INNER JOIN hivedata t2 WHERE t1.id < t2.id AND t1.pid = t2.pid;
效率优化
如果表非常大,使用上述删除方法效率比较低,我们可以使用临时表的方法解决。
CREATE TABLE hivedata_temp LIKE hivedata;
INSERT INTO hivedata_temp SELECT DISTINCT * FROM hivedata;
DROP TABLE hivedata;
RENAME TABLE hivedata_temp TO hivedata;