在开发测试中,常常遇到重复数据过多,自增的ID随着数据的删除变得凌乱了起来。今天分享一下,我在开发测试中使用的MySQL删除重复数据并重置ID自增SQL语句。

删除重复数据只保留一条

1
DELETE FROM userlist WHERE (username,password) IN (SELECT username,password FROM (SELECT username,password FROM userlist GROUP BY username,password HAVING COUNT(*)>1) s1) AND id NOT in (SELECT id FROM (SELECT id FROM userlist GROUP BY username,password HAVING COUNT(*)>1)s2);

重置ID自增

1
2
3
4
alter table userlist drop id;
alter table userlist add id int(255) not null first;
alter table userlist modify column id int(255) not null auto_increment,add primary key(id);
select * from userlist order by id;