table去掉td重复的边框_技术分享|快速处理MySQL重复数据小妙招

作者：蔡蔡蔡蔡贝特 | 来源：互联网 | 2023-09-23 19:49

正好最近在帮客户从达梦数据库迁移到MySQL。我也来简单说说重复数据的处理。存放在数据库中的数据分为三种：1.一种是经过严格意义过滤出来的数据。比如程序端过滤数据源、

正好最近在帮客户从达梦数据库迁移到 MySQL。我也来简单说说重复数据的处理。

存放在数据库中的数据分为三种&＃xff1a;1. 一种是经过严格意义过滤出来的数据。比如程序端过滤数据源、数据库端在表字段上设置 check 标记过滤数据源、设置触发器过滤、调用存储过程过滤等等&＃xff1b;2. 另一种是原始的没有经过任何处理的数据。比如程序端代码异常导致产生非正常的想要的数据、数据库端没有设置任何过滤规则的数据保留等等。这样会产生一系列垃圾数据&＃xff0c;当然也包含了我今天要说的重复的数据。

3. 最后一种是 SQL 语句在执行过程中可能产生的重复数据。比如两表外联&＃xff0c;总会产生一系列 NULL。

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

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

比如无主键的表 d1

mysql-(ytt/3305)->show create table d1\G

*************************** 1. row ***************************

Table: d1

Create Table: CREATE TABLE &＃96;d1&＃96; (

&＃96;r1&＃96; int(11) DEFAULT NULL,

&＃96;r2&＃96; int(11) DEFAULT NULL

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8mb4 COLLATE&＃61;utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

记录数总共为四百万。

mysql-(ytt/3305)->select count(*) from d1 limit 2;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 4000000 |

&＃43;----------&＃43;

1 row in set (0.18 sec)

可以看到足足有四分之三的记录是重复的。

mysql-(ytt/3305)->select count(distinct r1,r2) from d1 ;

&＃43;-----------------------&＃43;

| count(distinct r1,r2) |

&＃43;-----------------------&＃43;

| 1000000 |

&＃43;-----------------------&＃43;

1 row in set (2.68 sec)

比如记录(1&＃xff0c;1)就有四条。

mysql-(ytt/3305)-> select * from db1 order by r1,r2 limit 5;

&＃43;------&＃43;------&＃43;

| r1 | r2 |

&＃43;------&＃43;------&＃43;

| 1 | 1 |

| 2 | 2 |

&＃43;------&＃43;------&＃43;

5 rows in set (1.65 sec)

这种去重非常简单&＃xff0c;要么在数据库层做&＃xff0c;要么把数据导出来筛选好在导到数据库里来。在数据库里做&＃xff0c;无非就是新建一张克隆表&＃xff0c;完了把正常数据筛选出来&＃xff0c;再重新命名后&＃xff0c;删掉原来的表&＃xff0c;步骤也不是非常繁琐&＃xff0c;例子如下&＃xff1a;

mysql-(ytt/3305)->create table d2 like d1;

Query OK, 0 rows affected (0.01 sec)

时间主要耗费在去重并且插入新表这里

mysql-(ytt/3305)->insert into d2 select distinct r1,r2 from d1;

Query OK, 1000000 rows affected (19.40 sec)

Records: 1000000 Duplicates: 0 Warnings: 0

mysql-(ytt/3305)->alter table d1 rename to d1_bak;

Query OK, 0 rows affected (0.00 sec)

mysql-(ytt/3305)->alter table d2 rename to d1;

Query OK, 0 rows affected (0.00 sec)

mysql-(ytt/3305)->drop table d1_bak;

Query OK, 0 rows affected (0.00 sec)

上面总共花了大概 20 秒的样子&＃xff0c;再来看看在系统层面上去重&＃xff0c;先导出数据&＃xff0c;

mysql-(ytt/3305)->select * from db1 into outfile &＃39;/var/lib/mysql-files/d1.txt&＃39;;

Query OK, 4000000 rows affected (1.84 sec)

系统层面去重&＃xff0c;用 OS 自带的工具 sort 和 uniq。

root&＃64;ytt-pc:/var/lib/mysql-files# time cat d1.txt |sort -g |uniq > d1_uniq.txt

real 0m7.345s

user 0m7.528s

sys 0m0.272s

导入到原表&＃xff0c;

mysql-(ytt/3305)->truncate table d1;

Query OK, 0 rows affected (0.05 sec)

root&＃64;ytt-pc:/var/lib/mysql-files# mv d1_uniq.txt d1.txt

把处理好的数据直接导入到数据库

root&＃64;ytt-pc:/home/ytt/scripts# time mysqlimport -uytt -pytt -P3305 -h 127.0.0.1 --use-threads&＃61;2 -vvv ytt /var/lib/mysql-files/d1.txt

mysqlimport: [Warning] Using a password on the command line interface can be insecure.

Connecting to 127.0.0.1

Selecting database ytt

Loading data from SERVER file: /var/lib/mysql-files/d1.txt into d1

ytt.d1: Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

Disconnecting from 127.0.0.1

real 0m3.272s

user 0m0.012s

sys 0m0.008s

看下处理好的记录&＃xff0c;

mysql-(ytt/3305)->select * from d1 where 1 order by r1,r2 limit 2;

&＃43;------&＃43;------&＃43;

| r1 | r2 |

&＃43;------&＃43;------&＃43;

| 1 | 1 |

| 2 | 2 |

&＃43;------&＃43;------&＃43;

2 rows in set (0.40 sec)

OS 层面稍微效率高些&＃xff0c;总体包括数据导出&＃xff0c;数据去重&＃xff0c;数据导入&＃xff0c;差不多是数据库层时间的一半。第二种&＃xff0c;其实和第一种类似&＃xff0c;不同的是表有主键&＃xff0c;但是其他的字段记录值是重复的。举个例子&＃xff0c;表 d4 除了加了主键&＃xff0c;其他的记录和之前的一模一样。记录如下&＃xff1a;

mysql-(ytt/3305)->select * from d4 order by r1,r2 limit 5;

&＃43;---------&＃43;------&＃43;------&＃43;

| id | r1 | r2 |

&＃43;---------&＃43;------&＃43;------&＃43;

| 1 | 1 | 1 |

| 3000001 | 1 | 1 |

| 2000001 | 1 | 1 |

| 1000001 | 1 | 1 |

| 2 | 2 | 2 |

&＃43;---------&＃43;------&＃43;------&＃43;

5 rows in set (1.08 sec)

但是这种一般就得需要和具体的业务商量了&＃xff0c;比如我需要留下重复记录的最大主键值&＃xff0c;比如上面这个&＃xff0c;留下最大的 id 为 3000001 这条记录。这样的去重一条 sql 就搞定了&＃xff0c;

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;

Query OK, 3000000 rows affected (23.29 sec)

去掉了 300W 行重复记录&＃xff0c;剩下四分之一的正常数据。

mysql-(ytt/3305)->select count(*) from d4;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 1000000 |

&＃43;----------&＃43;

1 row in set (0.06 sec)

来看下效果&＃xff0c;保留了最大值&＃xff0c;其他的删掉了。

mysql-(ytt/3305)->select * from d4 order by r1,r2 limit 5;

&＃43;---------&＃43;------&＃43;------&＃43;

| id | r1 | r2 |

&＃43;---------&＃43;------&＃43;------&＃43;

| 3000001 | 1 | 1 |

| 3000002 | 2 | 2 |

| 3000003 | 3 | 3 |

| 3000004 | 4 | 4 |

| 3000005 | 5 | 5 |

&＃43;---------&＃43;------&＃43;------&＃43;

5 rows in set (0.25 sec)

第三种&＃xff0c;不同于前面两种&＃xff0c;这种体现在字段值里多余的字符&＃xff0c;比如空格&＃xff0c;多余的换行符等。依然看看几个例子&＃xff1a;1. 去掉字段值内前后的空白字符&＃xff0c;是这类里最简单的。这种 MySQL 有现成的函数&＃xff0c;一条基础的 SQL 即可。

表y11 有500W行示例数据

mysql-(ytt/3305)->select count(*) from y11;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 5242880 |

&＃43;----------&＃43;

1 row in set (0.30 sec)

利用trim函数。

mysql-(ytt/3305)->update y11 set r1 &＃61; trim(r1), r2 &＃61; trim(r2);

Query OK, 5242880 rows affected (2 min 1.56 sec)

Rows matched: 5242880 Changed: 5242880 Warnings: 0

mysql-(ytt/3305)->select * from y11 limit 5;

&＃43;----&＃43;------------------------&＃43;------------------------&＃43;

| id | r1 | r2 |

&＃43;----&＃43;------------------------&＃43;------------------------&＃43;

| 1 | sql server | sql server |

| 2 | sql server | sql server |

| 3 | sql server | sql server |

| 6 | db2 mysql oracle mysql | db2 mysql oracle mysql |

| 7 | db2 mysql oracle mysql | db2 mysql oracle mysql |

&＃43;----&＃43;------------------------&＃43;------------------------&＃43;

5 rows in set (0.00 sec)

2. 去掉单词中间的各种空白字符(空格&＃xff0c;换行&＃xff0c;制表符等)&＃xff1b;单词前后&＃xff0c;中间都有空格的场景。

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

mysql-(ytt/3305)->select * from y11　limit 5;

&＃43;----&＃43;-----------------------------------------------------&＃43;------------------------------------------------------&＃43;

| id | r1 | r2 |

&＃43;----&＃43;-----------------------------------------------------&＃43;------------------------------------------------------&＃43;

| 1 | sql server | sql server |

| 2 | sql server | sql server |

server | sql server |

| mysql | db2 mysql oracle

| 7 | db2 mysql oracle mysql | db2 mysql oracle mysql

&＃43;----&＃43;-----------------------------------------------------&＃43;------------------------------------------------------&＃43;

5 rows in set (0.00 sec)

可能最先想到的方法是把数据导出为文本文件&＃xff0c;完了用 linux 上的各种工具处理完了再导进去&＃xff0c;比如&＃xff1a;

mysql-(ytt/3305)->select * from y11 into outfile &＃39;/var/lib/mysql-files/y11.txt&＃39; fields terminated by &＃39;,&＃39; enclosed by &＃39;"&＃39;;

Query OK, 5242880 rows affected (3.54 sec)

mysql-(ytt/3305)->truncate y11;

Query OK, 0 rows affected (0.23 sec)

用 sed 处理下&＃xff0c;替换掉所有的空白字符。

root&＃64;ytt-pc:/var/lib/mysql-files# time sed -i &＃39;s/\s\&＃43;/ /g&＃39; y11.txt

real 0m27.476s

user 0m20.105s

sys 0m7.233s

导入到表 y11

mysql-(ytt/3305)->load data infile &＃39;/var/lib/mysql-files/y11.txt&＃39; into table y11 fields terminated by &＃39;,&＃39; enclosed by &＃39;"&＃39;;

Query OK, 5242880 rows affected (30.25 sec)

Records: 5242880 Deleted: 0 Skipped: 0 Warnings: 0

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

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

mysql-(ytt/3305)->update y11 set r1 &＃61; regexp_replace(r1,&＃39;[[:space:]]&＃43;&＃39;,&＃39; &＃39;), r2 &＃61; regexp_replace(r2,&＃39;[[:space:]]&＃43;&＃39;,&＃39; &＃39;);

Query OK, 4194304 rows affected (1 min 32.05 sec)

Rows matched: 5242880 Changed: 4194304 Warnings: 0

只是时间上稍微长些&＃xff0c;不过也影响不是很大。

mysql-(ytt/3305)->select * from y11 limit 5;

&＃43;----&＃43;------------------------&＃43;-------------------------&＃43;

| id | r1 | r2 |

&＃43;----&＃43;------------------------&＃43;-------------------------&＃43;

| 1 | sql server | sql server |

| 2 | sql server | sql server |

| 3 | sql server | sql server |

| 6 | db2 mysql oracle mysql | db2 mysql oracle mysql |

| 7 | db2 mysql oracle mysql | db2 mysql oracle mysql |

&＃43;----&＃43;------------------------&＃43;-------------------------&＃43;

5 rows in set (0.00 sec)

我想可能日常数据处理难免会有数据去重的场景&＃xff0c;希望这部分内容对大家有帮助。_

社区近期动态

No.1

Mycat 问题免费诊断

诊断范围支持&＃xff1a;

Mycat 的故障诊断、源码分析、性能优化

服务支持渠道&＃xff1a;

技术交流群&＃xff0c;进群后可提问

QQ群(669663113)

社区通道&＃xff0c;邮件&电话

osc&＃64;actionsky.com

现场拜访&＃xff0c;线下实地&＃xff0c;1天免费拜访

关注“爱可生开源社区”公众号&＃xff0c;回复关键字“Mycat”&＃xff0c;获取活动详情。

No.2

社区技术内容征稿

征稿内容&＃xff1a;

格式&＃xff1a;.md/.doc/.txt

主题&＃xff1a;MySQL、分布式中间件DBLE、数据传输组件DTLE相关技术内容

要求&＃xff1a;原创且未发布过

奖励&＃xff1a;作者署名&＃xff1b;200元京东E卡&＃43;社区周边

投稿方式&＃xff1a;

邮箱&＃xff1a;osc&＃64;actionsky.com

格式&＃xff1a;[投稿]姓名&＃43;文章标题

以附件形式发送&＃xff0c;正文需注明姓名、手机号、微信号&＃xff0c;以便小编及时联系

喜欢点“分享”&＃xff0c;不行就“在看”

推荐阅读

ip
Cacti 数据库错误：SQL 查询失败，错误代码 145

在使用 Cacti 进行监控时，发现已运行的转码机未产生流量，导致 Cacti 监控界面显示该转码机处于宕机状态。进一步检查 Cacti 日志，发现数据库中存在 SQL 查询失败的问题，错误代码为 145。此问题可能是由于数据库表损坏或索引失效所致，建议对相关表进行修复操作以恢复监控功能。 ... [详细]

蜡笔小新 2024-11-11 12:57:49
default
MySQL定时清理：每10分钟自动删除数据库中的无效数据

为了确保数据库的高效运行，本文介绍了一种方法，通过编写定时任务脚本来自动清理 `order` 表中状态为 0 或为空的无效订单记录。该脚本使用 PHP 编写，并设置时区为中国标准时间，每 10 分钟执行一次，以保持数据库的整洁和性能优化。此外，还详细介绍了如何配置定时任务以及脚本的具体实现步骤。 ... [详细]

蜡笔小新 2024-11-11 14:20:31
buffer
PTArchiver工作原理详解与应用分析

PTArchiver工作原理及其应用分析本文详细解析了PTArchiver的工作机制，探讨了其在数据归档和管理中的应用。PTArchiver通过高效的压缩算法和灵活的存储策略，实现了对大规模数据的高效管理和长期保存。文章还介绍了其在企业级数据备份、历史数据迁移等场景中的实际应用案例，为用户提供了实用的操作建议和技术支持。 ... [详细]

蜡笔小新 2024-11-11 13:40:49
datetime
Oracle 数据库操作日志与 MyBatis 在 Oracle 中的增删改查实现详解

本文详细介绍了在 Oracle 数据库中使用 MyBatis 实现增删改查操作的方法。针对查询操作，文章解释了如何通过创建字段映射来处理数据库字段风格与 Java 对象之间的差异，确保查询结果能够正确映射到持久层对象。此外，还探讨了插入、更新和删除操作的具体实现及其最佳实践，帮助开发者高效地管理和操作 Oracle 数据库中的数据。 ... [详细]

蜡笔小新 2024-11-09 14:28:39
datetime
Oracle字符集详解：图表解析与中文乱码解决方案

本文详细解析了 Oracle 数据库中的字符集机制，通过图表展示了不同字符集之间的转换过程，并针对中文乱码问题提供了有效的解决方案。文章深入探讨了字符集配置、数据迁移和兼容性问题，为数据库管理员和开发人员提供了实用的参考和指导。 ... [详细]

蜡笔小新 2024-11-08 11:50:24
bit
在Linux系统中避免安装MySQL的简易指南

在Linux系统中避免安装MySQL的简易指南 ... [详细]

蜡笔小新 2024-11-11 13:22:28
import
如何在Docker环境中高效利用数据库？ | Baeldung

在本文中，我们将探讨如何在Docker环境中高效地管理和利用数据库。首先，需要安装Docker Desktop以确保本地环境准备就绪。接下来，可以从Docker Hub中选择合适的数据库镜像，并通过简单的命令将其拉取到本地。此外，我们还将介绍如何配置和优化这些数据库容器，以实现最佳性能和安全性。 ... [详细]

蜡笔小新 2024-11-09 19:34:33
sum
深入解析 SQL 数据库查询技术

本文深入探讨了SQL数据库查询技术，重点讲解了单表查询的各种方法。首先，介绍了如何从表中选择特定的列，包括查询指定列、查询所有列以及计算值的查询。此外，还详细解释了如何使用列别名来修改查询结果的列标题，并介绍了更名运算的应用场景和实现方式。通过这些内容，读者可以更好地理解和掌握SQL查询的基本技巧和高级用法。 ... [详细]

蜡笔小新 2024-11-09 18:21:57
default
MySQL与MariaDB数据库管理：约束与键操作详解

在关系型数据库中，数据约束是指在向数据表中插入数据时必须遵循的限制条件。在MySQL和MariaDB中，常见的数据约束包括主键约束、唯一键约束、外键约束以及非空约束等。这些约束确保了数据的完整性和一致性，是数据库管理中的重要组成部分。通过合理设置和使用这些约束，可以有效防止数据冗余和错误，提升数据库的可靠性和性能。 ... [详细]

蜡笔小新 2024-11-08 17:17:06
default
如何有效防御网站中的SQL注入攻击

本期文章将深入探讨网站如何有效防御SQL注入攻击。我们将从技术层面详细解析防范措施，并结合实际案例进行阐述，旨在帮助读者全面了解并掌握有效的防护策略。希望本文能为您的网络安全提供有益参考。 ... [详细]

蜡笔小新 2024-11-08 11:09:59
datetime
如何在Excel数据导入过程中通过PHP检查MySQL数据库以防止重复记录的生成？

在将Excel数据导入MySQL数据库的过程中，如何确保不会生成重复记录？本文介绍了一种方法，通过PHP脚本检查数据库中是否存在相同的“Code”字段值，从而避免重复记录的产生。该方法不仅提高了数据导入的准确性，还增强了系统的健壮性。 ... [详细]

蜡笔小新 2024-11-08 10:50:38
string
MySQL Undo空间满载的原因及Oracle Undo表空间溢出的解决策略

针对MySQL Undo空间满载及Oracle Undo表空间溢出的问题，本文详细探讨了其原因与解决策略。首先，通过启动SQL*Plus并以SYS用户身份登录数据库，查询当前数据库的UNDO表空间名称，确认当前状态。接着，分析导致Undo空间满载的常见原因，如长时间运行的事务、频繁的更新操作等，并提出相应的解决方案，包括调整Undo表空间大小、优化事务管理、定期清理历史数据等。最后，结合实际案例，提供具体的实施步骤和注意事项，帮助DBA有效应对这些问题。 ... [详细]

蜡笔小新 2024-11-08 10:46:23
default
如何在本地环境中高效连接MySQL数据库的详细指南

本指南详细介绍了在Linux环境中高效连接MySQL数据库的方法。用户可以通过安装并使用`mysql`客户端工具来实现本地连接，具体命令为：`mysql -u 用户名 -p 密码 -h 主机`。例如，使用管理员账户连接本地MySQL服务器的命令为：`mysql -u root -p pass`。此外，还提供了多种配置优化建议，以确保连接过程更加稳定和高效。 ... [详细]

蜡笔小新 2024-11-08 08:38:45
default
掌握Spring框架前不可或缺的事务管理知识（第四部分）

在深入掌握Spring框架的事务管理之前，了解其背后的数据库事务基础至关重要。Spring的事务管理功能虽然强大且灵活，但其核心依赖于数据库自身的事务处理机制。因此，熟悉数据库事务的基本概念和特性是必不可少的。这包括事务的ACID属性、隔离级别以及常见的事务管理策略等。通过这些基础知识的学习，可以更好地理解和应用Spring中的事务管理配置。 ... [详细]

蜡笔小新 2024-11-07 19:31:36
string
详解Android连接MySQL数据库的操作流程及技术要点

在Android应用开发中，实现与MySQL数据库的连接是一项重要的技术任务。本文详细介绍了Android连接MySQL数据库的操作流程和技术要点。首先，Android平台提供了SQLiteOpenHelper类作为数据库辅助工具，用于创建或打开数据库。开发者可以通过继承并扩展该类，实现对数据库的初始化和版本管理。此外，文章还探讨了使用第三方库如Retrofit或Volley进行网络请求，以及如何通过JSON格式交换数据，确保与MySQL服务器的高效通信。 ... [详细]

蜡笔小新 2024-11-07 19:11:13

蔡蔡蔡蔡贝特

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章