利用 MySQL 进行数据清洗的操作方法

目录

• 1. 去除重复数据
• 2. 处理缺失值
• 3. 数据标准化（大小写、格式统一）
• 4. 处理无效数据
• 5. 数据类型转换
• 6. 合并 / 拆分字段
• 7. 异常值处理
• 执行建议
• 索引：

利用 mysql 进行数据清洗是数据预处理的重要环节，以下是常见的数据清洗操作及对应 SQL 示例：

1. 去除重复数据

使用 ROW_NUMBER() 或 GROUP BY 识别并删除重复记录。

-- 查找重复记录（以 user_id 和 email 为例）
WITH Duplicates AS (
  SELECT 
    user_id, 
    email, 
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
  FROM users
)
SELECT * FROM Duplicates WHERE rn > 1;
-- 删除重复记录（保留最新的一条）
DELETE FROM users
WHERE user_id NOT IN (
  SELECT MAX(user_id) 
  FROM users 
  GROUP BY email
);

2. 处理缺失值

• 填充默认值：使用 COALESCE() 或 IFNULL()。
• 删除缺失值：使用 WHERE 过滤。

-- 填充缺失值（将 NULL 替换为默认值）
UPDATE products
SET price = COALESCE(price, 0),  -- 价格为 NULL 时填充 0
    category = IFNULL(category, '未知')  -- 分类为 NULL 时填充 '未知'
WHERE price IS NULL OR category IS NULL;
-- 删除包含缺失值的记录
DELETE FROM orhttp://www.devze.comders
WHERE customer_id IS NULL;

3. 数据标准化（大小写、格式统一）

• 转换大小写：使用 UPPER() 或 LOWER()。
• 去除空格：使用 TRIM()。
• 日期格式化：使用 STR_TO_DATE() 或 DATE_FORMAT()。

-- 统一邮箱为小写
UPDATE users
SET email = LOWER(TRIM(email));
-- 标准化日期格式（将 '2023-12-31' 转为 '31-12-2023'）
UPDATE orders
SET order_date = DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%d-%m-%Y');

4. 处理无效数据

• 范围过滤：检查数值是否在合理区间。
• 正则匹配：验证格式（如邮箱、手机号）。

-- 删除年龄小于 0 或大于 12js0 的记录
DELETE FROM users
WHERE age < 0 OR age > 120;
-- 查找不符合邮箱格式的记录
SELECT * FROM users
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

5. 数据类型转换

使用 CAST() 或 CONVERT() 转换字段类型。

-- 将字符串类型的价格转为数值类型
ALTER TABLE products
MODIFY price DECIMAL(10, 2) AFTER CAST(price AS DECIMAL(10, 2));

6. 合并 / 拆分字段

• 合并字段：使用 CONCAT()。
• 拆分字段：使用 SUBSTRING() 或 SUBSTRING_INDEX()。

-- 合并姓名（first_name 和 last_name）
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name);
-- 拆分地址（以逗号分隔）
ALTER TABLE customers
ADD street VARCHAR(100),
ADD city VARCHAR(50);
UPDATE customers
SET 
  street = SUBSTRING_INDEX(address, ',', 1),
  city = SUBSTRING_INDEX(address, ',', -1);

7. 异常值处理

通过统计方法（如 Z-score）识别并处理异常值。

-- 计算平均价格和标准差
WITH Stats AS (
  SELECT 
    AVG(price) AS avg_price,
    STDDEV(price) AS std_price
  FROM products
)
-- 删除价格超过 3 个标准差的异常值
DELETE FROM products
WHERE ABS(price - (SELECT avg_price FROM Stats)) > 3 * (SELECT std_price FROM Stats);

执行建议

1. 备份数据：清洗前先备份，避免误操作。
2. 测编程客栈试逻辑：先用 SELECT 验证清洗逻辑javascript，再执行 UPDATE 或 DELETE。
3. 分批处理：大数据量时使用 LIMIT 分批更新，避免锁表。

-- 示例：分批删除重复记录
DELETE F编程客栈ROM users
WHERE user_id IN (
  SELECT user_id FROM (
    SELECT user_id, 
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
    FROM users
  ) t
  WHERE rn > 1
)
LIMIT 1000;  -- 每次处理 1000 条

索引：

索引是数据库中用于提高查询效率的关键工具，它类似书籍的目录，可以快速定位到数据的位置。

1. 索引的作用

• 加速查询：通过索引，数据库无需扫描全量数据，直接定位到符合条件的记录。

• 优化排序：索引通常已排序，可避免额外的排序操作。

• 强制唯一性：唯一索引（如主键）可防止重复数据。

2. 索引的原理

• 数据结构：常见的索引使用 B-Tree（MySQL InnoDB）或哈希表（Memory 引擎）。

• 存储方式：索引单独存储，包含键值和指向数据行的物理地址。

到此这篇关于利用 MySQL 进行数据清洗的操作方法的文章就介绍到这了,更多相关mysql数据清洗内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！