在执行之前看看重复数据的id:
mysql> select t1.id from t_user t1,(select username,min(id) as minid from t_user group by username having count(username)>1) t2 where t1.username=t2.username and t1.id<>t2.minid;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
+----+
执行删除:
mysql> delete user1 from t_user user1,(select t1.id from t_user t1,(select username,min(id) as minid from t_user group by username having count(username)>1) t2 where t1.username=t2.username and t1.id<>t2.minid) user2 where user1.id=user2.id
查询删除后的数据
mysql> select * from t_user;
+----+----------+----------+
| id | userName | password |
+----+----------+----------+
| 1 | huanglq | aaaaaa |
| 2 | assdfdf | 123 |
+----+----------+----------+
2 rows in set (0.00 sec)