正则表达式(Regular Expression, 简称 Regex 或 RegExp)是用于匹配字符串模式的强大工具。在 SQL 中,正则表达式主要用于字符串的检索和匹配。通过正则表达式,我们可以实现更复杂和灵活的查询,特别是在数据清洗和复杂数据查询场景中。
正则表达式在程序开发中给我们提供了非常大的帮助,尤其是在对数据进行过滤的时候帮助更大,如果SQL查询也能直接使用正则表达式,那么会减少代码过滤数据的操作,对程序员来说是个非常方便的功能,经过我测试,目前mysql6.2以上的版本是支持正则查询的,其他版本比如5.7版本支持正则比较弱,很多查询都会抛错,无法完成执行。
1. 正则表达式简介
正则表达式是一种字符串匹配的模式,可以用来查找、替换、验证字符串。虽然每个数据库对正则表达式的支持程度不同,但 MySQL 提供了相对完善的正则表达式支持,使用 REGEXP
或 RLIKE
关键字来实现正则匹配。
2. MySQL 中的正则表达式函数
MySQL中常见的正则表达式有like/not like 、 regexp/not regexp 、 regexp_replace、 regexp_like、regexp_substr、regexp_instr、regexp_instr 等,接下来我们就拿最常用的regexp来演示如何使用正则表达式
2.1.基本语法:
SELECT column_name
FROM table_name
WHERE column_name REGEXP 'pattern';
-
pattern
:要匹配的正则表达式模式。 -
REGEXP
会返回布尔值,匹配返回1
,不匹配返回0
。
例如,查询名字包含“张”的数据:
SELECT name
FROM users
WHERE name REGEXP '张';
看起来和使用like没什么区别,但是接下来我们会引入正则表达式进行查询,方能体现其威力
2.2 正则表达式规则
MySQL regexp匹配正则表达式与常规的正则表达式相同,规则如下:
模式 | 模式匹配什么 |
---|---|
^ | 匹配字符串开头 |
$ | 匹配字符串结尾 |
. | 匹配任意单个字符 |
[...] | 匹配方括号间列出的任意字符 |
[^...] | 匹配方括号间未列出的任意字符 |
p1|p2|p3 | 交替匹配任意 p1 或 p2 或 p3 |
* | 匹配前面的元素的零次或多次 |
+ | 匹配前面的元素的一次或多次 |
{n} | 匹配前面的元素 n 次 |
{m,n} | 匹配前面的元素 m 至 n 次 |
3.示例
接下来通过示例来看MySQL的正则是如何使用的
3.1 基本字符匹配
SELECT name
FROM users
WHERE name REGEXP '张';
同like相同的功能
3.2 字符集匹配
查找名字中包含任意数字的用户:
SELECT name
FROM users
WHERE name REGEXP '[0-9]';
解释:[0-9]
表示匹配名字中含有数字的记录。
3.3 边界匹配
查找以字母 "A" 开头的名字:
SELECT name
FROM users
WHERE name REGEXP '^A';
解释:^A
表示匹配以 "A" 开头的名字。
3.4 重复匹配
查找名字中含有至少两个连续 "o" 字母的用户:
SELECT name
FROM users
WHERE name REGEXP 'o{2,}';
解释:o{2,}
表示匹配包含至少两个连续 "o" 字符的名字。
4.利用正则查询实战
示例 1:匹配电子邮件地址,要从用户表中查找所有合法的电子邮件地址,可以使用如下的正则表达式:
SELECT email
FROM users
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
解释:此正则表达式匹配标准的电子邮件格式,确保邮箱地址以字母或数字开头,中间包含 "@",最后是域名。
示例 2:匹配手机号(中国大陆),筛选出格式为 11 位的手机号:
SELECT phone
FROM users
WHERE phone REGEXP '^1[3-9][0-9]{9}$';
解释:正则表达式 ^1[3-9][0-9]{9}$
用于匹配中国大陆的手机号,1
开头,第二位为 3-9
之间的数字,且总长度为 11 位。
示例 3:匹配身份证号,要筛选出有效的 18 位身份证号码:
SELECT id_card
FROM users
WHERE id_card REGEXP '^[1-9][0-9]{5}(18|19|20)[0-9]{2}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01])[0-9]{3}[0-9Xx]$';
解释:该正则表达式确保身份证号是 18 位,且日期部分为合法日期。
5.小结一下
正则表达式是 SQL 中非常强大的工具,尤其在字符串匹配和数据清洗过程中能够发挥极大的作用。