mysql 删除重复记录

首先创建一张表

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

搞定

当然这是一种解决方案，暂时还没想到更好的解决方案