Mysql字符串切分的处理
前段时间做了一个对于字符串的切分,对于网页爬取的数据或者不规范的数据来源,常常会有这种需求。
由于在处理的过程中,sql语句中对字符串的出里函数以及方法不太了解,走了不少弯路。
这里记录一下。
Mysql 对字符串解切分处理,主要用到了4个函数,
SUBSTRING_INDEX(str,delim,count)
Str 是要处理字符串,
Delim是切分字符如:’,’
Count 是截取的字段数,如3,截取第三个,前的字段
这是一个很有趣的函数,当你的count,但你的count给到足够大了时候它不起任何作用,不如其他语言中的split()那么好用。
SUBSTRING(str,pos)
Str 字符串源,
Pos 开始截取的位置,pos可以为负,表示从后开始计数的位置。
CHAR_LENGTH(str)
REPLACE(str,from_str,to_str)
REPLACE(SUBSTRING(SUBSTRING_INDEX(`major_tax_violatio`,',',i),CHAR_LENGTH(SUBSTRING_INDEX(`major_tax_violatio`,',',i-1))+1),',','')
用来获取两个’,’分隔符之间的数据。
由于mysql没有在一行数据做出循环,或者保存数据办法(我不知道),所以我们能够控制截取的结果的只有count,以及where匹配。
这里我使用的是count++的方式,第一次截取所有的第一个’,’前的数据,第二次截取第二个’,’与第一个’,’之间的字符
where
SUBSTRING(SUBSTRING_INDEX(major_tax_violatio,',',i),CHAR_LENGTH(SUBSTRING_INDEX(major_tax_violatio,',',i-1))+1) regexp 'public_date' 使用正则表达式来筛选。
对于循环的跳出条件
- 当所有的元组的该字段都已经匹配上了(用一个rows+=ROW_COUNT(),来累加update的元组之和等于所有元组数)
- 当一个所有的已经匹配完毕(所有的,分割符分割的内容都已经匹配到了),但是有部分元组没有匹配到我们想要的数据。
--REPLACE(SUBSTRING(SUBSTRING_INDEX(`major_tax_violatio`,',',i),CHAR_LENGTH(SUBSTRING_INDEX(`major_tax_violatio`,',',i-1))+1),',','')
写入一个tmp表中,当这个表的所有字段都为null,或者’’时表示改字段已经匹配完毕。