查询表中的重复数据,并删除和逻辑删除

古城微笑少年丶 2022-04-17 03:43 253阅读 0赞

新建一张表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

插入数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

如果根据name,age和del\_flag三个字段来判断一条记录是否重复

首先,查询是否有重复记录

select name,age,del_flag,group_concat(id) from user group by name,age,del_flag having count(1)>1;

![20181112192202573.png][]

查询出来有重复记录,下面删除重复记录,分为物理删除和逻辑删除

逻辑删除,将del\_flag置为1,保留id最大的行

update user u1 join user u2 using(name,age,del_flag) set u1.`del_flag`=1 where u1.`id`<u2.`id`;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2][]

物理删除

delete a from user a join user b using(name,age,del_flag) where a.id<b.id;

![20181112192814111.png][]

记录下,方便自己以后查阅,也方便他人学习.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20220417/978723aff93144fcb72f3ec705d8883d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20220417/c72ea6a8d3b74ffea5147da7d635f717.png
[20181112192202573.png]: /images/20220417/b4fccd1886914068a2d5ea8ad60459fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 2]: /images/20220417/1f91dbb565ed4ff8a91dba60c277f168.png
[20181112192814111.png]: /images/20220417/a769615d81f94f6cae746c4c43c3be90.png