暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL重复数据处理的技巧

MySQL重复数据处理的技巧

bisal的个人杂货铺 2023-07-07
608

日常工作中会碰到处理数据库中重复数据的场景，技术社群的这篇文章《技术分享 | 快速处理 MySQL 重复数据小妙招》给我们介绍了一些方案，值得借鉴学习。

存放在数据库中的数据分为三种：
1. 一种是经过严格意义过滤出来的数据。例如程序端过滤数据源、数据库端在表字段上设置check标记过滤数据源、设置触发器过滤、调用存储过程过滤等等；

2. 另一种是原始的没有经过任何处理的数据。例如程序端代码异常导致产生非正常的想要的数据、数据库端没有设置任何过滤规则的数据保留等等。这样会产生一系列垃圾数据，当然也包含了我今天要说的重复的数据。

3. 最后一种是 SQL 语句在执行过程中可能产生的重复数据。例如两张表进行了外关联，就会产生一系列NULL。

今天要说的重复数据，不包含SQL语句在执行中产生的重复数据，只包含了原始重复数据的处理。接下来，用几个经典的场景来说下。

第一种，记录完全重复，这其实是最最简单的去重场景。

比如无主键的表d1，
  1. mysql-(ytt/3305)->show create table d1\G
  2. *************************** 1. row ***************************
  3.        Table: d1
  4. Create Table: CREATE TABLE `d1` (
  5.   `r1` int(11) DEFAULT NULL,
  6.   `r2` int(11) DEFAULT NULL
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  8. 1 row in set (0.00 sec)
记录数总共为四百万，
  1. mysql-(ytt/3305)->select count(*) from d1 limit 2;

  3. +----------+
  4. | count(*) |
  5. +----------+
  6. |  4000000 |
  7. +----------+
  8. 1 row in set (0.18 sec)
可以看到足足有四分之三的记录是重复的，
  1. mysql-(ytt/3305)->select count(distinct r1,r2) from d1 ;
  2. +-----------------------+
  3. | count(distinct r1,r2) |
  4. +-----------------------+
  5. |               1000000 |
  6. +-----------------------+
  7. 1 row in set (2.68 sec)

例如记录（1，1）就有四条，

  1. mysql-(ytt/3305)-> select * from db1 order by r1,r2 limit 5;
  2. +------+------+
  3. | r1   | r2   |
  4. +------+------+
  5. |    1 |    1 |
  6. |    1 |    1 |
  7. |    1 |    1 |
  8. |    1 |    1 |
  9. |    2 |    2 |
  10. +------+------+
  11. 5 rows in set (1.65 sec)
这种去重非常简单，要么在数据库层做，要么把数据导出来筛选好在导到数据库里来。
在数据库里做，无非就是新建一张克隆表，将正常的数据筛选出来，再重新命名后，删掉原来的表，步骤也不是非常繁琐，例子如下，
  1. mysql-(ytt/3305)->create table d2 like d1;
  2. Query OK, 0 rows affected (0.01 sec)
时间主要耗费在去重并且插入新表这里，
  1. mysql-(ytt/3305)->insert into d2 select distinct r1,r2 from d1;
  2. Query OK, 1000000 rows affected (19.40 sec)
  3. Records: 1000000  Duplicates: 0  Warnings: 0

  5. mysql-(ytt/3305)->alter table d1 rename to d1_bak;
  6. Query OK, 0 rows affected (0.00 sec)

  8. mysql-(ytt/3305)->alter table d2 rename to d1;
  9. Query OK, 0 rows affected (0.00 sec)

  11. mysql-(ytt/3305)->drop table d1_bak;
  12. Query OK, 0 rows affected (0.00 sec)
上面总共花了大概 20 秒的样子，再来看看在系统层面上去重，先导出数据，
  1. mysql-(ytt/3305)->select * from db1 into outfile '/var/lib/mysql-files/d1.txt';
  2. Query OK, 4000000 rows affected (1.84 sec)
系统层面去重，用OS自带的工具sort和uniq。
  1. root@ytt-pc:/var/lib/mysql-files# time cat d1.txt |sort -g  |uniq > d1_uniq.txt

  3. real    0m7.345s
  4. user    0m7.528s
  5. sys     0m0.272s

导入到原表，

  1. mysql-(ytt/3305)->truncate table d1;
  2. Query OK, 0 rows affected (0.05 sec)

  4. root@ytt-pc:/var/lib/mysql-files# mv d1_uniq.txt  d1.txt
将处理好的数据直接导入到数据库，
  1. root@ytt-pc:/home/ytt/scripts# time mysqlimport -uytt -pytt -P3305 -h 127.0.0.1   --use-threads=2 -vvv ytt var/lib/mysql-files/d1.txt
  2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.
  3. Connecting to 127.0.0.1
  4. Selecting database ytt
  5. Loading data from SERVER file: var/lib/mysql-files/d1.txt into d1
  6. ytt.d1: Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0
  7. Disconnecting from 127.0.0.1

  9. real    0m3.272s
  10. user    0m0.012s
  11. sys     0m0.008s
看下处理好的记录，
  1. mysql-(ytt/3305)->select * from d1 where 1 order by r1,r2 limit 2;
  2. +------+------+
  3. | r1   | r2   |
  4. +------+------+
  5. |    1 |    1 |
  6. |    2 |    2 |
  7. +------+------+
  8. 2 rows in set (0.40 sec)
OS层面稍微效率高些，总体包括数据导出，数据去重，数据导入，差不多是数据库层时间的一半。
第二种，其实和第一种类似，不同的是表有主键，但是其他的字段记录值是重复的。
举个例子，表d4除了加了主键，其它的记录和之前的一模一样。记录如下，
  1. mysql-(ytt/3305)->select * from d4 order by r1,r2 limit 5;
  2. +---------+------+------+
  3. | id      | r1   | r2   |
  4. +---------+------+------+
  5. |       1 |    1 |    1 |
  6. | 3000001 |    1 |    1 |
  7. | 2000001 |    1 |    1 |
  8. | 1000001 |    1 |    1 |
  9. |       2 |    2 |    2 |
  10. +---------+------+------+
  11. 5 rows in set (1.08 sec)
但是这种一般就得需要和具体的业务商量了，例如我需要留下重复记录的最大主键值，例如上面这个，留下最大的id为3000001这条记录。这样的去重一条SQL就搞定了，
  1. mysql-(ytt/3305)->delete a from d4 a left join (select max(id) id from d4 group by r1, r2) b using(id) where b.id is null;

  3. Query OK, 3000000 rows affected (23.29 sec)
去掉了300W行重复记录，剩下四分之一的正常数据，
  1. mysql-(ytt/3305)->select count(*) from d4;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |  1000000 |
  6. +----------+
  7. 1 row in set (0.06 sec)
来看下效果，保留了最大值，其它的删掉了。
  1. mysql-(ytt/3305)->select * from d4 order by r1,r2 limit 5;
  2. +---------+------+------+
  3. | id      | r1   | r2   |
  4. +---------+------+------+
  5. | 3000001 |    1 |    1 |
  6. | 3000002 |    2 |    2 |
  7. | 3000003 |    3 |    3 |
  8. | 3000004 |    4 |    4 |
  9. | 3000005 |    5 |    5 |
  10. +---------+------+------+
  11. 5 rows in set (0.25 sec)
第三种，不同于前面两种，这种体现在字段值里多余的字符，例如空格，多余的换行符等。依然看看几个例子，
1. 去掉字段值内前后的空白字符，是这类里最简单的。这种MySQL有现成的函数，一条基础的SQL即可，
  1. 表y11 有500W行示例数据
  2. mysql-(ytt/3305)->select count(*) from y11;
  3. +----------+
  4. | count(*) |
  5. +----------+
  6. |  5242880 |
  7. +----------+
  8. 1 row in set (0.30 sec)

  10. 利用trim函数。
  11. mysql-(ytt/3305)->update y11 set r1 = trim(r1), r2 = trim(r2);
  12. Query OK, 5242880 rows affected (2 min 1.56 sec)
  13. Rows matched: 5242880  Changed: 5242880  Warnings: 0

  15. mysql-(ytt/3305)->select * from y11 limit 5;
  16. +----+------------------------+------------------------+
  17. | id | r1                     | r2                     |
  18. +----+------------------------+------------------------+
  19. |  1 | sql server             | sql server             |
  20. |  2 | sql server             | sql server             |
  21. |  3 | sql server             | sql server             |
  22. |  6 | db2 mysql oracle mysql | db2 mysql oracle mysql |
  23. |  7 | db2 mysql oracle mysql | db2 mysql oracle mysql |
  24. +----+------------------------+------------------------+
  25. 5 rows in set (0.00 sec)
2. 去掉单词中间的各种空白字符（空格，换行，制表符等)；单词前后，中间都有空格的场景。

依然是表y11，从结果来看，各种换行符，空格已经让结果无法正常显示了，

  1. mysql-(ytt/3305)->select * from y11　limit 5;
  2. +----+-----------------------------------------------------+------------------------------------------------------+
  3. | id | r1                                                  | r2                                                   |
  4. +----+-----------------------------------------------------+------------------------------------------------------+
  5. |  1 | sql server                                          | sql server                                           |
  6. |  2 | sql       server                                    | sql            server                                |
  7. server                                         | sql      server                                         |
  8.  |           mysql | db2         mysql         oracle
  9.  | 7 | db2         mysql         oracle                 mysql | db2         mysql         oracle                mysql
  10. +----+-----------------------------------------------------+------------------------------------------------------+
  11. 5 rows in set (0.00 sec)
可能最先想到的方法是将数据导出为文本文件，用linux上的各种工具处理完成再导入，例如，
  1. mysql-(ytt/3305)->select * from y11 into outfile '/var/lib/mysql-files/y11.txt'  fields terminated by ',' enclosed by '"';
  2. Query OK, 5242880 rows affected (3.54 sec)

  4. mysql-(ytt/3305)->truncate y11;
  5. Query OK, 0 rows affected (0.23 sec)

用sed处理下，替换掉所有的空白字符，

  1. root@ytt-pc:/var/lib/mysql-files# time sed -i 's/\s\+/ /g' y11.txt

  3. real    0m27.476s
  4. user    0m20.105s
  5. sys     0m7.233s

导入到表y11，

  1. mysql-(ytt/3305)->load data infile '/var/lib/mysql-files/y11.txt' into table y11 fields terminated by ',' enclosed by '"';
  2. Query OK, 5242880 rows affected (30.25 sec)
  3. Records: 5242880  Deleted: 0  Skipped: 0  Warnings: 0

上面虽然达成了目的，但是过程过于繁琐，如果MySQL层实在解决不了再考虑。

完全可以利用MySQL的正则替换功能直接替换掉多余的字符为一个空格，也是一条简单的SQL，

  1. mysql-(ytt/3305)->update y11 set r1 = regexp_replace(r1,'[[:space:]]+',' '), r2 = regexp_replace(r2,'[[:space:]]+',' ');
  2. Query OK, 4194304 rows affected (1 min 32.05 sec)
  3. Rows matched: 5242880  Changed: 4194304  Warnings: 0

只是时间上稍微长些，不过也影响不是很大，

  1. mysql-(ytt/3305)->select * from y11 limit 5;
  2. +----+------------------------+-------------------------+
  3. | id | r1                     | r2                      |
  4. +----+------------------------+-------------------------+
  5. |  1 | sql server             | sql server              |
  6. |  2 | sql server             | sql server              |
  7. |  3 | sql server             | sql server              |
  8. |  6 | db2 mysql oracle mysql | db2 mysql oracle mysql  |
  9. |  7 | db2 mysql oracle mysql | db2 mysql oracle mysql  |
  10. +----+------------------------+-------------------------+
  11. 5 rows in set (0.00 sec)
可能日常数据处理难免会有数据去重的场景，希望这部分内容对大家有帮助。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，




近期更新的文章：
《MySQL管理端口登录异常排查及正确使用技巧》
《MySQL的一些常用SQL》
《创建索引优化的案例一则》
《英超新球队卢顿的财政和球场》
《GreatSQL体系架构的了解》
《从月球上能看到万里长城？》

近期的热文：
《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：
《公众号1200篇文章分类和索引》

selectmysqlmysql update语句mysql创建数据库mysql创建存储过程
文章转载自bisal的个人杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论