MariaDB教程FG033-MariaDB批量数据导入清洗去重实战项目

内容简介：本文主要介绍MariaDB批量数据导入、清洗和去重的实战方法，包括数据导入的基本概念、数据清洗的重要性、数据去重的方法等核心内容。通过实际案例讲解CSV文件导入、数据清洗和去重的完整流程，以及如何优化导入性能。风哥教程参考MariaDB官方文档Data Import/Export、SQL Syntax等相关内容。

Part01-基础概念与理论知识

1.1 数据导入的基本概念

数据导入是将外部数据加载到MariaDB数据库的过程，常见的导入方式包括：

LOAD DATA INFILE：从文本文件导入数据
mysqlimport：命令行工具导入数据
INSERT语句：逐条插入数据
批量插入：使用INSERT INTO … VALUES (…)语句
外部工具：如Navicat、phpMyAdmin等

数据导入的挑战：

数据量庞大：处理大量数据时性能问题
数据格式不一致：需要转换和清洗
数据质量问题：重复数据、无效数据等
导入速度：需要优化导入性能

1.2 数据清洗的重要性

数据清洗是确保数据质量的关键步骤，主要包括：

去除无效数据：空值、错误格式等
标准化数据：统一格式、单位等
修复错误数据：纠正明显的错误
转换数据类型：确保数据类型正确
验证数据完整性：确保数据符合业务规则

数据清洗的好处：

提高数据质量：确保数据准确、完整
减少错误：避免因数据问题导致的错误
提高分析准确性：基于高质量数据的分析更可靠
降低存储成本：去除冗余数据

1.3 数据去重的方法

数据去重是消除重复数据的过程，常见的去重方法包括：

使用DISTINCT关键字：查询时去除重复行
使用GROUP BY：按关键字分组去重
使用UNIQUE索引：防止插入重复数据
使用临时表：通过临时表去重
使用ROW_NUMBER()：按条件排序后去重

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 数据导入策略

生产环境数据导入策略建议：

选择合适的导入方式：根据数据量和格式选择
预处理数据：在导入前进行初步清洗
分批导入：将大文件分成多个小文件导入
禁用索引：导入前禁用索引，导入后重建
调整参数：优化导入相关的参数设置

2.2 数据清洗方案

生产环境数据清洗方案建议：

建立清洗规则：根据业务需求制定清洗规则
使用ETL工具：如Kettle、Talend等
编写清洗脚本：使用SQL或其他语言编写清洗脚本
验证清洗结果：确保清洗后的数据质量
建立数据质量监控：定期检查数据质量

2.3 性能优化建议

更多学习教程公众号风哥教程itpux_com

生产环境性能优化建议：

增大max_allowed_packet：允许更大的数据包
启用local_infile：允许从本地文件导入
调整innodb_buffer_pool_size：增加缓冲池大小
使用批量插入：减少网络往返次数
禁用外键检查：导入时临时禁用外键检查
使用事务：批量提交，减少日志写入

学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 批量数据导入实施

批量数据导入的实施步骤：

准备数据文件：确保数据格式正确
创建目标表：设计合适的表结构
优化表结构：添加适当的索引
执行导入操作：使用合适的导入方式
验证导入结果：检查数据是否正确导入

3.2 数据清洗流程

数据清洗的流程：

数据质量评估：分析数据质量问题
制定清洗规则：根据评估结果制定规则
执行清洗操作：应用清洗规则
验证清洗结果：检查清洗后的数据
记录清洗过程：保存清洗日志

3.3 数据去重处理

数据去重的处理步骤：

识别重复数据：确定重复的标准
选择去重方法：根据数据特点选择
执行去重操作：应用去重方法
验证去重结果：确保去重后的数据正确
防止重复数据：建立防止重复的机制

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 CSV文件导入实战

场景描述：从CSV文件导入用户数据到MariaDB数据库。

# 创建目标表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

执行结果：

Query OK, 0 rows affected (0.01 sec)

# 创建CSV文件
# 内容如下：
# name,email,phone,age
# 张三,zhangsan@fgedu.net.cn,13800138001,25
# 李四,lisi@fgedu.net.cn,13900139001,30
# 王五,wangwu@fgedu.net.cn,13700137001,35
# 张三,zhangsan@fgedu.net.cn,13800138001,25

# 导入CSV文件
LOAD DATA LOCAL INFILE ‘/mariadb/data/users.csv’
INTO TABLE fgedu_users
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(name, email, phone, age);

执行结果：

Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 1

# 查看导入结果
SELECT * FROM fgedu_users;

执行结果：

4.2 数据清洗实战

场景描述：清洗导入的用户数据，处理空值和格式问题。

# 查看数据质量
SELECT
COUNT(*) AS total_rows,
COUNT(name) AS non_null_name,
COUNT(email) AS non_null_email,
COUNT(phone) AS non_null_phone,
COUNT(age) AS non_null_age
FROM fgedu_users;

执行结果：

+————+—————+—————+—————+—————+
| total_rows | non_null_name | non_null_email | non_null_phone | non_null_age |
+————+—————+—————+—————+—————+
| 4 | 4 | 4 | 4 | 4 |
+————+—————+—————+—————+—————+

# 模拟添加一些有问题的数据
INSERT INTO fgedu_users (name, email, phone, age) VALUES
(”, ’empty@fgedu.net.cn’, ‘13600136001’, 28),
(‘赵六’, NULL, ‘13500135001’, 32),
(‘孙七’, ‘sunqi@fgedu.net.cn’, ”, 29),
(‘周八’, ‘zhouba@fgedu.net.cn’, ‘13400134001’, NULL);

执行结果：

Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

# 再次查看数据质量
SELECT
COUNT(*) AS total_rows,
COUNT(name) AS non_null_name,
COUNT(email) AS non_null_email,
COUNT(phone) AS non_null_phone,
COUNT(age) AS non_null_age
FROM fgedu_users;

执行结果：

+————+—————+—————+—————+—————+
| total_rows | non_null_name | non_null_email | non_null_phone | non_null_age |
+————+—————+—————+—————+—————+
| 8 | 7 | 7 | 7 | 7 |
+————+—————+—————+—————+—————+

# 清洗数据
# 1. 处理空名字
UPDATE fgedu_users SET name = ‘未知’ WHERE name = ”;
# 2. 处理空邮箱
UPDATE fgedu_users SET email = ‘unknown@fgedu.net.cn’ WHERE email IS NULL;
# 3. 处理空电话
UPDATE fgedu_users SET phone = ‘00000000000’ WHERE phone = ”;
# 4. 处理空年龄
UPDATE fgedu_users SET age = 0 WHERE age IS NULL;

执行结果：

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 查看清洗后的数据
SELECT * FROM fgedu_users;

执行结果：

+—-+——–+———————-+————-+——+———————+
| id | name | email | phone | age | created_at |
+—-+——–+———————-+————-+——+———————+
| 1 | 张三 | zhangsan@fgedu.net.cn | 13800138001 | 25 | 2023-01-01 00:00:00 |
| 2 | 李四 | lisi@fgedu.net.cn | 13900139001 | 30 | 2023-01-01 00:00:00 |
| 3 | 王五 | wangwu@fgedu.net.cn | 13700137001 | 35 | 2023-01-01 00:00:00 |
| 4 | 张三 | zhangsan@fgedu.net.cn | 13800138001 | 25 | 2023-01-01 00:00:00 |
| 5 | 未知 | empty@fgedu.net.cn | 13600136001 | 28 | 2023-01-01 00:00:00 |
| 6 | 赵六 | unknown@fgedu.net.cn | 13500135001 | 32 | 2023-01-01 00:00:00 |
| 7 | 孙七 | sunqi@fgedu.net.cn | 00000000000 | 29 | 2023-01-01 00:00:00 |
| 8 | 周八 | zhouba@fgedu.net.cn | 13400134001 | 0 | 2023-01-01 00:00:00 |
+—-+——–+———————-+————-+——+———————+

4.3 数据去重实战

场景描述：去除重复的用户数据。

# 识别重复数据
SELECT
name, email, phone, age,
COUNT(*) AS duplicate_count
FROM fgedu_users
GROUP BY name, email, phone, age
HAVING COUNT(*) > 1;

执行结果：

+——–+———————-+————-+——+—————–+
| name | email | phone | age | duplicate_count |
+——–+———————-+————-+——+—————–+
| 张三 | zhangsan@fgedu.net.cn | 13800138001 | 25 | 2 |
+——–+———————-+————-+——+—————–+

# 使用临时表去重
CREATE TABLE fgedu_users_temp LIKE fgedu_users;
INSERT INTO fgedu_users_temp
SELECT DISTINCT * FROM fgedu_users;
# 清空原表
TRUNCATE TABLE fgedu_users;
# 将去重后的数据导回原表
INSERT INTO fgedu_users
SELECT * FROM fgedu_users_temp;
# 删除临时表
DROP TABLE fgedu_users_temp;

执行结果：

Query OK, 0 rows affected (0.01 sec)
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)

# 查看去重后的数据
SELECT * FROM fgedu_users;

执行结果：

+—-+——–+———————-+————-+——+———————+
| id | name | email | phone | age | created_at |
+—-+——–+———————-+————-+——+———————+
| 1 | 张三 | zhangsan@fgedu.net.cn | 13800138001 | 25 | 2023-01-01 00:00:00 |
| 2 | 李四 | lisi@fgedu.net.cn | 13900139001 | 30 | 2023-01-01 00:00:00 |
| 3 | 王五 | wangwu@fgedu.net.cn | 13700137001 | 35 | 2023-01-01 00:00:00 |
| 4 | 未知 | empty@fgedu.net.cn | 13600136001 | 28 | 2023-01-01 00:00:00 |
| 5 | 赵六 | unknown@fgedu.net.cn | 13500135001 | 32 | 2023-01-01 00:00:00 |
| 6 | 孙七 | sunqi@fgedu.net.cn | 00000000000 | 29 | 2023-01-01 00:00:00 |
| 7 | 周八 | zhouba@fgedu.net.cn | 13400134001 | 0 | 2023-01-01 00:00:00 |
+—-+——–+———————-+————-+——+———————+

风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 数据导入最佳实践

风哥提示：在进行批量数据导入时，应根据数据量大小选择合适的导入方式，并优化相关参数以提高导入速度。

使用LOAD DATA INFILE：对于大量数据，这是最快的导入方式
分批导入：将大文件分成多个小文件，避免一次性导入过大的数据
禁用索引：导入前禁用索引，导入后重建，可显著提高导入速度
调整参数：增大max_allowed_packet、bulk_insert_buffer_size等参数
使用事务：批量提交，减少日志写入次数
监控导入进度：对于大型导入，监控进度并及时处理异常

5.2 数据清洗技巧

建立清洗规则：根据业务需求制定明确的清洗规则
使用正则表达式：处理复杂的格式问题
利用SQL函数：如TRIM、REPLACE、SUBSTRING等
分步清洗：将复杂的清洗任务分解为多个步骤
验证清洗结果：确保清洗后的数据符合业务规则
自动化清洗：编写脚本自动执行清洗任务

5.3 常见问题与解决方案

导入速度慢：优化参数、禁用索引、使用批量导入
数据格式错误：在导入前预处理数据
重复数据：使用UNIQUE索引、去重操作
内存不足：分批导入、调整缓冲区大小
权限问题：确保用户有FILE权限
字符集问题：确保数据文件和数据库字符集一致

# 优化导入速度的参数设置
SET GLOBAL bulk_insert_buffer_size = 16777216;
— 16MB
SET GLOBAL max_allowed_packet = 67108864;
— 64MB
SET SESSION foreign_key_checks = 0;
SET SESSION unique_checks = 0;

通过以上措施，可以有效提高MariaDB批量数据导入、清洗和去重的效率，确保数据质量，为业务分析和决策提供可靠的数据支持。

from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

MariaDB教程FG033-MariaDB批量数据导入清洗去重实战项目

Part01-基础概念与理论知识

1.1 数据导入的基本概念

1.2 数据清洗的重要性

1.3 数据去重的方法

Part02-生产环境规划与建议

2.1 数据导入策略

2.2 数据清洗方案

2.3 性能优化建议

Part03-生产环境项目实施方案

3.1 批量数据导入实施

3.2 数据清洗流程

3.3 数据去重处理

Part04-生产案例与实战讲解

4.1 CSV文件导入实战

4.2 数据清洗实战

4.3 数据去重实战

Part05-风哥经验总结与分享

5.1 数据导入最佳实践

5.2 数据清洗技巧

5.3 常见问题与解决方案

相关推荐

联系我们