杨公子的个人博客

醒来啊麦克白!把沉睡赶走!

  • :
  • :
  menu
13 文章
4 评论
4551 浏览
0 当前访客
ღゝ◡╹)ノ❤️

【MySQL笔记】一场模糊查询引发的血案

一、引言

最近有个任务是做数据质量监控,需要尽可能的从更多的维度去对数据进行统计,提炼出规则模板。

前几天已经写了一部分的规则模板,今天在查看以往出现过的数据问题的时候发现,由于上游数据的问题,有些本该只出现数字的字段出现了中文或者特殊符号。

我想是不是可以从异常字符入手来做几个监控的统计规则呢?于是说干就干,开始动起手做。

首先去库里面看了看监控所涉及的表的字段的类型,发现有一些特殊类型的字段可以做异常字符的监控:

  • 数字类型的字段
  • 日期类型的字段
  • 证件ID和机构代码这种组合字符串类型的字段

二、模糊匹配

于是我就开始思考,怎么对这几种类型的数据进行异常字符的监控呢?当然是要用模糊匹配啦,那么要模糊匹配些什么呢?

首先要确定这几种类型的数据本身包含的字符类型,那就把它们列出来吧:

  • 数字类型——包含0-9这几个数字字符,还可能会出现小数,那就还会有小数点了
  • 日期类型——一般得日期格式是这样的yyyy-mm-dd hh:mm:ss或者是这样的yyyymmdd 。不管是哪一种,基本都包含0-9的数字字符、短横线'-'、冒号':'还有中间的空格。
  • ID和代码类型——这种类型的数据一般有几种情况,纯数字的、数字和字母组合的、数字字母组合再加上短横线'-'的。

确定了要模糊匹配的内容,接下来只需要用正则表达式表示出来,然后用SQL的 like 子句实现就好了。

1、like 与 rlike(regexp)

于是我马上就写出了三条SQL来统计异常字符的数据量:

select * from test_table where col1 like '%[^0-9\.]%' ;
select * from test_table where col2 like '%[^0-9\:\s-]%' ;
select * from test_table where col3 like '%[^0-9a-zA-Z-]%' ;

接下来就是根据这几种数据的特点,建个测试表,造几条测试数据跑跑SQL看一下吧。
图片.png

但是当我得意的把这几条SQL拿去执行的时候,却发现完全没有像我想象的那样,竟然什么都没有匹配到,全都返回的是null。
图片.png

结果不是预想的样子,那一定就是SQL写的有问题了,而且正则表达式又都不一样,应该不会都写错了吧。那问题就只可能是出在 like 上面了。于是马上去百度,一顿疯狂百度之后才发现,在MySQL中模糊匹配是用 like 操作符,但是如果是使用正则表达式做模糊匹配的话,就需要用regexp(和 rlike作用相同)操作符了,而且不需要通配符 '%' 了。

可能是由于平时较少使用正则表达式的原因吧,这个知识点完全不知道,竟然连 rlike 这个操作符都没有见过,这是罪过。

既然找到了问题所在,我马上修改了之前的SQL:

select * from test_table where col1 rlike '[^0-9\.]' ;
select * from test_table where col2 rlike '[^0-9\:\s-]' ;
select * from test_table where col3 rlike '[^0-9a-zA-Z-]' ;

2、空格要不要转义

再次将SQL拿去执行,发现第二条SQL又不对了,id=1的这条数据是不应该出现在结果中的,但是它出现了,这又是什么原因呢。
图片.png

前面已经改过了操作符的错误,现在再出错就只可能是正则表达式的原因了。到底是哪一个有问题呢?其他的两条SQL没有问题,那么[^0-9-]这一部分就是没有错的,那错误只可能是冒号':'和空格'\s'的这两个转义的问题了。

于是又是一顿百度,但是发现网上说的冒号和空格的转义就是我写的那种格式啊。难道是电脑的问题吗?在否定了电脑坏掉的原因后我开始冷静的分析。

因为冒号':'是具体的字符,使用转义应该不会再变成其他的样子,貌似错误就是出现空格的转义上了。那就测试一下吧,于是我将第二条SQL改成了下面的形式(空格不转义):

select * from test_table where col2 rlike '[^0-9\: -]' ;

再次执行这条SQL,发现结果是对的。

三、总结

经过一波三折的折腾,终于将这三个监控的模板写出来了

select count(*) from test_table where col1 rlike '[^0-9\.]' ;
select count(*) from test_table where col2 rlike '[^0-9\: -]' ;
select count(*) from test_table where col3 rlike '[^0-9a-zA-Z-]' ;

最后来总结一下:

  • 在MySQL中,如果是单纯的字符串模糊匹配,就使用操作符 like,通配符是'%'(和Linux种不同的,Linux中的通配符是*);如果是使用正则表达式来做模糊匹配,那就要使用操作符 rlike(或者regexp),不需要通配符。
  • 在其他的环境中使用正则表达式或者需要使用空格的时候,可能需要用'\s'来做转义处理,但是在MySQL中使用正则表达式的时候,空格是不需要转义的,直接使用[ ]。
  • 另外提示,在正则表达式中,由于数字和字母的匹配中会使用短横线'-',那么如果需要匹配字符'-',要将其放在正则表达式的最后,就像这样'[^0-9a-zA-Z-]'

标题:【MySQL笔记】一场模糊查询引发的血案
作者:Ronnie-Yang
地址:http://www.yanggongzi.top/articles/2019/11/14/1573720773232.html

评论