初始化实验环境,定义需求,找到重复数据,找到要保留的数据,删除重复数据,方法1,方法2,方法3,写作1,写作2总结
MySQL中经常会遇到重复的数据,那么当我们遇到重复的时候的时候,如果定位哪些数据是有重复的记录?如何删除重复的数据?我们该怎么做呢?接下来我们一步步来分析一下遇到这样的情况后,该如何处理。
MySQL经常会遇到重复数据,那么当我们遇到重复数据时,应该定位哪些数据才会有重复记录呢?如何删除重复数据?我们做什么呢接下来,我们一步步来分析如何应对这样的情况。
我该怎么办?
初始化实验环境
我们创建一个简单的表user_info,然后基于这个表分析重复数据的处理。的id是自增主键,姓名、性别、年龄三列是我们判断是否为重复数据的关键。如果这三列的值相同,则该行数据被视为重复数据。表构建语句如下:
CREATE TABLE `user_info` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `***` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `re***rk` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
初始化数据如下:
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (1, 'A', '男', 22, '第一个A');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (2, 'B', '女', 33, '第一个B');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (3, 'C', '男', 44, '第一个C');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (4, 'D', '女', 55, '第一个D');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (5, 'A', '男', 22, '第二个A');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (6, 'B', '女', 33, '第二个B');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (7, 'C', '男', 44, '第二个C');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (8, 'D', '女', 55, '第二个D');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (9, 'E', '男', 18, '第一个E');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (10, 'A', '男', 22, '第三个A');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (11, 'B', '女', 33, '第三个B');INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `***`, `age`, `re***rk`) VALUES (12, 'F', '男', 15, '第一个F');
最后表中数据如下:
最终表格中的数据如下:
初始化测试数据
明确需求
假设我们的要求是保留第一次出现的重复数据,但不保留后面的重复数据。
也就是我们的上面的这个表中每一组重复数据中id最小的一行数据需要保留,其他比较大的id的重复的数据行需要被删除。当然如果是要保留id行最大的一行数据最为最后的数据行也是可以了,只要在查询的时候,稍微修改一下SQL语句的min(id)或***x(id)函数即可。
即我们上表中每组重复数据中id最小的数据需要保留,其他id较大的重复数据行需要删除。当然,如果想保留id行的最大数据行和最后一个数据行,只要在查询时稍微修改一下SQL语句的min(id)或***x(id)函数就可以了。
郑恺
查找重复数据
基于我们之前初始化的实验数据,首先我们要找出哪些数据是有重复数据的行。通过下面的SQL语句可以得到结果:其中有四类数据有重复数据,分别是名称值A、B、C、d,使用下面的SQL找出哪些数据行有重复记录,统计重复出现的次数。
select name, ***, age, count(*) as count -- 数据重复出现的次数from user_info group by name, ***, age having count(*) > 1;
重复数据在表中的统计结果如下:
表中重复数据的统计结果如下:
重复数据的统计
找到要保留的数据。
现在我们知道了如何查询哪些数据是重复的,那么我们需要保留哪些数据呢?使用以下SQL获取我们想要保留的数据行:
select * from user_info where id in (select min(id) from user_info group by name, ***, age);
结果如下:
结果如下:
在每组重复数据中,id值最小的数据行
上面的结果是我们需要最后剩下的数据。包含非重复时间内id最小的数据线和每组重复数据。
删除的数据
方法一
这是最笨的一种方式,也是最容易理解的一种方式,效率也比较低。思路如下:
这是最笨的方法,最容易理解,效率也比较低。想法如下:
步骤一
第一步
步骤二
第二步
第三步
从上面的过程中,我们一步一步地找到了需要删除的数据。定位这些数据后,删除时,只需将查询语句改为delete语句即可。所以当我们最终以这种方式删除我时,我们的删除语句如下:
delete from user_info where (name,***,age) in ( select x.* from ( -- 删除的时候,这里要在包裹一层子查询select -- 查询重复数据中,name, ***, age的值name, ***, agefrom user_infogroup by name, ***, agehaving count(*) > 1) as x)and id not in (select min_id from ( -- 删除的时候,这里要在包裹一层子查询select -- 查询重复数据中,最小的id值min(id) as min_idfrom user_info group by name, ***, agehaving count(*) > 1) as y);
注意:在上面的delete语句中,我们在两个where条件中的子查询之外包装了一层子查询,即上面SQL语句中的as x和as y查询语句。包装图层的原因是程序中出现以下错误提示:
1093 - You can't specify target table 'user_info' for update in FROM clause, Time: 0.084000s
出现上述错误的原因是:修改表时,子查询不能是被修改的表。因此,我们的解决方案是在子查询之外设置一层查询语句。
方法2
上面方法一的思路是想办法找到我们要删除的数据是哪些,然后我们在删除的时候,使用where条件去匹配这些查询出来要删除的数据行,以此来达到删除重复数据的目的。
上面第一种方法的思路是找出我们要删除的数据,然后在删除的时候用where条件匹配要删除的数据行,从而达到删除重复数据的目的。
换个方式解决。
此时,我们不妨换个角度思考:与其关注我们需要删除哪些重复数据,不如关注我们需要留下哪些数据。然后,当我们删除时,我们可以使用反演方法来不在我们需要保留的数据中。那不就是我们要删除的数据吗?
那么,我们需要留下哪些数据呢?在每组数据中,id值最小的行就是我们想要保留的数据行。我们不关心其他的。那么如何才能得到这样的数据行呢?使用以下SQL语句获取我们需要保留的数据行的所有id的值:
select min(id) from user_info group by name, ***, age;
结果如下:
既然获得了我们想要保留的数据行的id***,那么在我们想要删除数据的where条件中,不就是需要使用not在我们想要保留的id***中删除的数据吗?删除重复数据的语句如下:
delete from user_info where id not in(select min_id from (select min(id) as min_idfrom user_info group by name, ***, age) as x);
注意:这里为了避免MySQL的1903错误,我们还在where条件的子查询中包装了另一个子查询,也就是上面SQL中的as x查询语句。
方法3
寻找更高效、更简单的方法
通过关联两个表来删除数据,这是高效的,也是推荐的。与自己交往。关联的条件是我们判断数据是否是重复数据的关键。除此之外,最重要的条件是:两个表的id关联条件,这是删除保留数据的关键条件。查询重复数据的SQL语句如下:
select a.*,b.* from user_info as a inner join user_info as b on a.name = b.name and a.*** = b.*** and a.age = b.ageand a.id > b.id;
结果如下:
1写作方法
重复数据删除SQL语句如下:
delete a.*from user_info as a inner join user_info as b on a.name = b.name and a.*** = b.*** and a.age = b.ageand a.id > b.id;
2写作方法
除了上面的写法,还有另一种写法,如下:
查询要删除的重复数据SQL如下:
select * from user_info as a where a.id <> (select min(b.id) from user_info as b where a.name = b.nameand a.*** = b.***and a.age = b.age);
删除重复数据的SQL语句如下:
delete a.* from user_info as awhere a.id <> (selectmin(b.id)from (select * from user_info) as bwhere a.`name`= b.`name`and a.*** = b.***and a.age = b.age);
摘要
MySQL中经常使用以上方法进行重复数据删除。对于其他数据库中存在的重复数据,同样的思路用于删除,只是SQL语句的写法可能略有不同。只要掌握了思路,具体到SQL语句的编写,多试几次就能成功。
以上,希望能帮到你。
最后一个提醒:在实际删除之前,记得备份原始数据。因此在删除错误后,数据无法恢复。您可以使用下面的语句创建一个备份表,以便在删除错误后将数据恢复到原始表中。
create table user_info_bak as select * from user_info; --创建一个备份表truncate table user_info; -- 清空原始表中的数据insert into user_info select * from user_info_bak; -- 从备份表中把数据插入到原始表中
如上所述,如果数据被错误地删除,可以将user_info_bak中的数据恢复到user_info表中。
本文来自不茫然未来投稿,不代表舒华文档立场,如若转载,请注明出处:https://www.chinashuhua.cn/24/637835.html