我们之前已初步介绍过如何利用SQL对文本进行检索和编辑,而今天,我们将要介绍一种更为“高大上”并且可以与Python通用的方式来高效地匹配文本——正则表达式。
包罗万象
正则表达式的作用是匹配文本,将一个“模式”利用正则表示出来后与一个文本串进行匹配。在MySQL中,WHERE子句为正则表达式提供了初步的支持,以便我们在用SELECT检索出数据后进行过滤。
注意:MySQL仅支持多数正则表达式实现的一个很小的子集。
我们之前有介绍过用关键字LIKE来进行文本匹配的方法,那么今天,我们先从简单的开始,将LIKE替换为REGEP(Regular Expression,正则表达式)。
LIKE和REGEXP有什么区别呢?
LIKE匹配整个列,而REGEXP匹配的是列值。
如果被匹配的文本在列值中出现:
- LIKE将不会找到它,相应的行也不被返回(除非使用通配符)。
- REGEXP将会找到它,相应的行将被返回
当然,借助于^和$定位符(anchor)(后文会有说明),REGEXP也是可以用来匹配整个列值的。
#包含value的所有行
Where col_name REGEXP 'value'
正则与LIKE对比
本篇建表代码请移至此处进行下载:析海:目录·索引
找出在列“prod_name”中包含文本“1000”的所有行,并针对名称进行排序:
SELECT prod_name AS 单纯用REGEXP
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name
SELECT prod_name AS 单纯用LIKE
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name
因为LIKE必须要整个列都与之匹配,所以在这个情况中,在列值中锁定了1000之后,REGEXP返回了“JetPack 1000“,而LIKE没有返回任何值。
但若是我们利用通配符的话:
SELECT prod_name AS 用了通配符的LIKE
FROM products
WHERE prod_name LIKE '%1000'
ORDER BY prod_name
返回的结果便是一样的了。
MySQL中的正则表达式匹配(自版本 3.23.4后)不区分大小写,即大写和小写被视作一样。为区分大小写,可使用BINARY关键字:
WHERE prod_name REGEXP BINARY 'JetPack .000'
若是
WHERE prod_name REGEXP 'JetPack .000'
那么无论”jEtpACK“还是”JeTPack“等都会被匹配返回。
齐头并进
为了同时对多个串进行匹配,我们可以用OR(或者),即为符号“|”。
WHERE col_name REGEXP 'value_1|value_2|value_3'
同匹举例
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000|3000'
ORDER BY prod_name
这里,SQL将会返回列值中含有“1000”或者“2000”或者“3000”的列。
在这个例子中,产品名称里不存在“3000”的值。
但是按照上面的方法,我们在遇到拥有大量的相同列值时,代码会变得单调而繁琐,这个时候,我们可以借助方括号([])来偷懒【不是】。
WHERE col_name REGEXP '[value1value2value3] value'
它可以定义一组字符。
字符组举例
找出列值中含有1000或2000或3000的列:
SELECT prod_name AS 利用方括号偷懒
FROM products
WHERE prod_name REGEXP '[123]000'
ORDER BY prod_name
然而,若是我们不用方括号,而是直接写成'1|2|3000'会怎么样呢?
SELECT prod_name AS 直接偷懒
FROM products
WHERE prod_name REGEXP '1|2|3000'
ORDER BY prod_name
嗯,似乎返回了很多奇奇怪怪【?】的东西呢。
那是因为,直接利用“|”时,SQL实则在匹配1或者2或者3000的列值。
小杠精专场:
“^”在字符集合中表示否定。
[123]匹配数字1、2和3,但是[^123]匹配所有除了1、2和3的任何东西。
大范围式偷懒
集合可用来定义要匹配的一个或多个字符。例如,下面的集合将匹配数字0到9:
[0123456789]
但是想要简化写法的话,可以直接写作:
[0-9]
当然,我们也是可以匹配任意的集合:
[3-9]
甚至字母(按照字母表顺序):
[a-z]
我们也甚至可以利用字符类来匹配:
大范围式偷懒举例
比如我们想要找出含有任意吨数 (ton)铁砧 (anvil)的产品名:
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9] ton'
ORDER BY prod_name
当然,我们也可以在不使用数据库表的情况下用SELECT和带文字串的REGEXP来测试正则表达式:
- 返回0:没有匹配;
- 返回1:文本中有匹配。
SELECT '文本' REGEXP '想要匹配的文本'
比如:
SELECT '这是个测试' REGEXP '不',
'我真的是个测试' REGEXP '是',
'不骗你,我就只是个测试' REGEXP ','
在这个例子里:
- “这是个测试” 中并没有出现“不”字,因此返回0;
- “我真的是个测试”中出现了“是”,因此返回1;
- “不骗你,我就只是个测试”中出现了逗号,因此返回1。
小结
今天我们入门了正则表达式以及如何利用REGEXP进行匹配。接下来,我们还会学习到如何对特殊字符进行匹配、只匹配文本重点,以及如何进行全文本搜索的方法。
希望上文可以帮助大家了解如何在SQL中进行正则匹配,如果还是有什么疑问或是建议的话,欢迎留言询问~
祝各位学习愉快!