MySQL正则表达式匹配:语法、应用与性能优化

251 阅读5分钟

需求

最近帮产品写几个SQL脚本,分析项目的一些关键指标,统计需要利用某个字段中的值的状态进行判断,该字段结构是一个json串,关键字段用字母取代,结构如下:

{"a": [], "b": [], "c": [], "d": {"e": [], "f": 0, "g": 0}}

其中a、b、c字段都是列表,d字段是一个对象。a、b、c字段中列表项还有可能是列表,如此大概。

假设我给这个字段取名为nature,表名为test,需要判断的条件是该字段为空即符合,SQL如下:

select count(*) from test where nature is null;

看起来能满足条件,但是实际场景是,该字段由于一些历史原因在为空的场景下有两种状态值,一种是NULL,一种是类似于上面json串的形式。即便是空,d字段也会初始化几个值,所以仅用上面的SQL就很难过滤出想要的条件了,这时正则匹配就用上了

拆分

根据上面第二种值的形式,分析出什么情况下满足该字段有值写入,分析出两点:

  1. 前三个字段(a,b,c)若有值,则结构应为[{对象1},{对象2},{对象n}]
  2. 若d字段有值,则e字段中也必定会有如上结构的值,即[{..},{..},{..}]

综上:满足这个有值场景的条件即该字段包含中包含[{字符

注意,以上推导是根据业务场景来的,如果有需要提取字符的直接指定字符即可。

处理

在分析完需要判断的字符后,写下SQL,如下

select count(*) from test where nature REGEXP '[{';

执行完就报了下面的错

image.png

翻译过来啥意思呢,大概就是说从正则表达式获得的错误,括号的不平衡。这是咋回事呢?"["即方括号是属于特殊字符,需要使用转义字符,知道了原因以后更改SQL。

select count(*) from test where nature REGEXP '\[\{';

执行完毕发现依旧是报了上面的错误,

image.png

正则匹配在很多编程语言中都有用,且转义字符是一个反斜杠,但是在Mysql中是使用了两个反斜杠的表示法,因为Mysql自己会利用一个转义字符来识别,剩下的一个才会交由正则库去处理

更改上面的SQL语句。

select count(*) from test where nature REGEXP '\\[\\{';

可以发现这回执行成功了。这部分内容希望提醒大家在使用涉及到特殊字符的时候,记得要加两个斜杠,下面是Mysql中一些常见的特殊字符:

特殊字符转义后解释
[\\[匹配左方括号
]\\]匹配右方括号
.\\.匹配点号
\\\\匹配反斜杠自己

只是列举几个常用的特殊字符并不全,在一些不确定的字符出错时可以想想是不是特殊字符,来定位问题。

实际到这里已经可以筛选出来nature字段值不为null的数据了,但是需求是统计该字段为空的数据(也就是包含null和刚刚利用正则过滤的这个),首先想到了查询否定,接下来试试可不可行,如下SQL:

select count(*) from test where nature REGEXP '[^\[\{]';

发现结果不如预期,比预期的数量要多很多,经过分析发现,这里需要匹配的是两个字符组合在一起的[{,而利用上面的方式,其实是等价于or的情况的,也就是匹配到[或者{都符合条件,那这数量就很多了。

Mysql除了提供REGEXP外,还提供了NOT REGEXP操作符,表示了一种不包含的意思。所以最终的SQL就是

select count(*) from test where nature not REGEXP '\[\{' or nature is null;

其他用法

正则匹配功能非常强大,从实用性角度挑选几个演示。

数据准备

准备一张单词表,插入一万条记录做演示数据。


// 1.建表
CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

// 2.存储过程
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

// 3.调用
call idata();

数据源参考:如何正确地显示随机消息?

接下来,开始测试。

匹配以某一字符开头的字符串

用法:^

例子:匹配以he开头的字符串

select * from words where word REGEXP '^he';

结果:

image.png

匹配以某一字符结尾的字符串

用法:$

例子:匹配以d结尾的字符串

select * from words where word REGEXP 'f$';

结果:

image.png

匹配包含某一字符的字符串

用法:直接输入需要匹配的字符

例子:匹配包含ai的字符串

select * from words where word REGEXP 'ai';

结果:

image.png

匹配任意单个字符

比如有这么几个单词,boot,shoot,root,现在需要匹配包含这种形式的字符,可以使用.来匹配,那么不管前面是什么都可以。

select * from words where word REGECP '.oo';

匹配或查询

类似于执行select查询语句的or条件,理解为并入到这个正则的表达式中。

用法:|

例子:匹配包含ai或者bi的字符串

select * from words where word REGEXP 'ai|bi';

结果:

image.png

还有一个匹配多个字符之一,比如匹配包含a或者b中的任意一个,可以用[ab]来表示。

匹配范围

可以包含数字范围和字母范围,比如从上个例子中匹配多个字符中任意一个来看,如果想要匹配数字0-9的任意一个,也可以表示为[0123456779],在正则里可以表示为[0-9]。

如果想继续匹配字母可以接着拼接[0-9a-zA-Z]表示,这样使用可能还是不够简洁,正则表达式来提供了预定义的字符来简化,比如[0-9]可以用[:digit:]替代表示任意数字,[0-9a-zA-Z]可以用[:alnum:]相当于任意数字和字母,还有很多。

结语

正则表达式很强大,这里仅列了一些日常开发用的比较多的例子,如果遇到复杂的情况也可以针对性的查询,相信只要掌握了使用技巧,上手其他的匹配规则也容易很多。