首先创建一张表
CREATE TABLE test(
number INT,su VARCHAR(20)
);
插入几条一模一样的数据
INSERT INTO test VALUES(1,'ttt');
INSERT INTO test VALUES(1,'ttt');
INSERT INTO test VALUES(2,'sss');
INSERT INTO test VALUES(2,'sss');
INSERT INTO test VALUES(3,'rrr');
修改表,添加一列自增列
ALTER TABLE test ADD COLUMN id BIGINT(20) AUTO_INCREMENT PRIMARY KEY;
然后删除相同记录:
DELETE FROM test WHERE id IN (SELECT id FROM (SELECT id FROM test GROUP BY number HAVING COUNT(*)>1)s)
然后把表里面的新增的id删除掉:
ALTER TABLE test DROP COLUMN id
搞定
当然这是一种解决方案,暂时还没想到更好的解决方案