删除数据库重复记录
测试表
create table test1(
id int primary key auto_increment comment '主键',
username varchar(20) comment '用户名',
tel varchar(20) comment '手机号'
);插入数据
insert into test1 (username,tel) values ('tom1','123'),('tom2','234'),('tom3','235');执行3次。
+----+----------+------+
| id | username | tel |
+----+----------+------+
| 1 | tom1 | 123 |
| 2 | tom2 | 234 |
| 3 | tom3 | 235 |
| 4 | tom1 | 123 |
| 5 | tom2 | 234 |
| 6 | tom3 | 235 |
| 7 | tom1 | 123 |
| 8 | tom2 | 234 |
| 9 | tom3 | 235 |
+----+----------+------+
删除
delete from
test1
where
id
not in (
select
t.id
from (
select
min(id) id
from
test1
group by
username
) t
);