《数据清洗系列》 - 03 数据清洗方法之MySQL

本篇主要汇总数据清洗过程经常用到的一些方法，持续更新，以备后用。

1. 查找带中文字符的数据

 SELECT * FROM t_table1 WHERE length(name) != char_length(name);

2. 去除多余空格

# 去除两端空格
UPDATE jigou SET name = TRIM(name)

# 去除换行符char(10)和回车符char(13)
-- UPDATE jigou SET name = REPLACE(REPLACE(name, CHAR(10), ''), CHAR(13), '');

# 去除水平制表符char(9) 
-- UPDATE jigou SET name = REPLACE(name, CHAR(9), '');

3. 查询重复数据

# 单列
select brand_name, count(*) as count from matchji_map group by brand_name having count > 1;

# 多列
 SELECT * FROM t_table1 a  group by  name,avatar HAVING count(1) > 1 ;

4. 复制数据到另外一张表

# 复制表结构
CREATE TABLE newuser LIKE user; 

# 导入数据
INSERT INTO newauser SELECT * FROM user;

5. 多表关联更新数据

 UPDATE  t_table1 t2  LEFT JOIN t_table2 t1 ON  t2.name= t1.name set t2.avatar = t1.avatar 

6. 删除重复数据

 DELETE  FROM t_table1 WHERE id in (SELECT id FROM ( 
    SELECT id FROM t_table1 a 
        WHERE ((SELECT COUNT(*) FROM t_table1 WHERE id = a.id) > 1) and id not in (
                    select min(id) from t_table1 group by id having count(*)>1
                    ) ORDER BY id DESC  ) as temp
  );

7. 替换字符串

update t_table1 set  name = replace(name,'/','+') where name LIKE '%/%';

8. 正则处理

# 匹配以小写字母a开头，以o结尾的数据（`BINARY区分大小写`）
select * from data_cleaning_demo where `name` regexp BINARY '^a(\w+)$'

# 匹配指定字符串（多个用|分隔）
select * from data_cleaning_trace_result where origin_name REGEXP '专业|之一|在册|团队'

# 匹配指定字符中的任意一个
select * from data_cleaning_demo where `name` regexp '[0-9]'

# 查询指定字符之外的字符
select "abc" REGEXP "[^a-z]"; // 返回0

# 括号属于特殊字符要转义
select '中国高科(600730)' REGEXP '\([0-9]+\)'

# 
SELECT * 
FROM users
WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$'

正则表达式常用字符匹配列表

选项	说明	例子	匹配值示例
^	匹配开头	^a	a1, a2
$	匹配结尾	b$	1b, 2b
.	匹配任何单个字符，包括回车、换行	a.b	a1b, a2b
?	匹配0次或1次	a?	b, ab
*	匹配0次或多次	a*b	b, ab
+	匹配1次或多次	a+b	ab, aab
\ (竖线)	匹配a或b	a\b	ac, bc
<字符串>	匹配含指定的字符串	<bc>	abc, abcd
[字符集合]	匹配集合中的任一字符	[ab]	ac, bc
[^]	匹配不在括号中的任何字符	[^a]	bc, bcd
字符串{n,}	字符串出现至少n次	a{2}	aa, aaa
字符串{n,m}	匹配字符串至少n次，最多m次	a{2,4}	aa, aaa

参考：

https://blog.csdn.net/moguxiansheng1106/article/details/44258499

https://blog.csdn.net/xgjianstart/article/details/76468015

https://blog.csdn.net/m493096871/article/details/89454384