admin管理员组文章数量:1634853
文章目录
- 准备工作
- LIKE 运算符
- 转义字符
- 数据库扩展
- MySQL 正则表达式
- Oracle 正则表达式
- SQL Server 正则表达式
- PostgreSQL 正则表达式
- SQLite 正则表达式
- 参考文档
字符串的模式匹配(pattern matching)是指给定一个模式,然后判断某个字符串是否满足该模式。常见的应用包括敏感词的检测、身份证校验、IP 地址验证等。
本文以验证电子邮箱的合法性为例,介绍如何在 SQL 中实现字符串的模式匹配,涉及的数据库包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。下表列出了这些数据库对于模式匹配的支持情况:
模式匹配 | MySQL | Oracle | SQL Server | PostgreSQL | SQLite |
---|---|---|---|---|---|
LIKE 运算符 | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
正则表达式 | REGEXP_LIKE() | REGEXP_LIKE() | ~ |
接下来我们详细讨论不同数据库的具体实现细节。
准备工作
我们首先创建一个测试表,并且生成一些测试数据:
CREATE TABLE test (
email VARCHAR(50)
);
INSERT INTO test VALUES ('TEST@qq');
INSERT INTO test VALUES ('test@qq');
INSERT INTO test VALUES ('.123@qq');
INSERT INTO test VALUES ('test+email@sina');
INSERT INTO test VALUES ('me.qq');
INSERT INTO test VALUES ('123.test@sql');
对于 Web 开发中常见的邮箱地址验证问题,我们首先需要定义一个合法电子邮箱的规则。电子邮箱一般格式为:用户名@域名;以下是一个简单的规则:
- 以字母或者数字开头,后面跟一个或者多个字母、数组或特殊字符( . _ - );
- 然后是一个 @ 字符;
- 接着是一个或者多个字母、数组或特殊字符( . - );
- 最后是域名,即 . 以及 2 到 4 个字母。
对于以上规则,使用正则表达式可以描述如下:
^[a-zA-Z0-9]+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$
简单说明一下,^ 表示匹配字符串的开头;[a-zA-Z0-9] 匹配大小写字母或数字;+ 表示匹配前面的内容一次或多次;. 匹配任何一个字符,\. 匹配点号自身;{2,4} 匹配前面的内容 2 次到 4次;$ 表示字符串的结束。
📝关于正则表达式的详细介绍,可以参考 GitHub 上的正则表达式教程。
LIKE 运算符
SQL 标准定义了 LIKE
运算符,可以用于简单的模式匹配。其中有两个特殊的通配符:
- 下划线(_)匹配一个任意字符;
- 百分号(%)匹配零个或者多个任意字符。
以下语句用于查找以 test 开头的字符串:
select email
from test
where email like 'test%';
-- MySQL、SQL Server 和 SQLite
email |
------------------|
TEST@qq |
test@qq |
test+email@sina|
-- Oracle 和 PostgreSQL
EMAIL |
------------------|
test@qq |
test+email@sina|
从查询结果可以看出,MySQL、SQL Server 和 SQLite 中的LIKE
运算符默认不区分大小写;Oracle 和 PostgreSQL 中的LIKE
运算符默认区分大小写。
以下示例返回了由 4 个字符加上 @ 符号开始的字符串:
select email
from test
where email like '____@%';
email |
-----------|
TEST@qq|
test@qq |
.123@qq|
另外,NOT LIKE
运算符可以进行反向匹配,也就是查找不匹配某个模式的字符串。
转义字符
由于下划线和百分号被看作通配符,我们无法判断某个字符串中是否包含这两个字符。此时需要用到转义字符,它的作用就是将随后的字符看作普通字符,例如:
'60%' like '60!%' escape '!' -- 匹配
'60%' like '60\%' -- 匹配
'60@' like '60!%' escape '!' -- 不匹配
其中,escape 用于指定转义字符,默认情况下为反斜杠(\)。
数据库扩展
除了 SQL 标准中定义的行为之外,许多数据库对LIKE运算符进行了扩展:
- MySQL 和 SQLite 允许对数字类型进行模式匹配:
10 LIKE '1%'
; - Oracle 提供了
LIKEC
、LIKE2
、LIKE4
用于不同字符集的字符匹配; - SQL Server 支持使用 [ ] 匹配某个范围内的字符,[^ ] 匹配某个范围之外的字符;
- PostgreSQL 提供了不区分大小写的
ILIKE
运算符; - SQLite 提供了区分大小写的编译选项
PRAGMA case_sensitive_like = true;
,同时还提供了基于 Unix 文件路径匹配的 GLOB 运算符。
我们主要来看一下 SQL Server 中的范围匹配,例如:
-- SQL Server
select email
from test
where email like '[0-9]%';
email |
----------------|
123.test@sql|
其中,[0-9] 表示任意数字;所以上面的查询返回了以数字开头的字符串。同理,'[^0-9]%'
匹配不以数字开头的字符串,%[ace]
匹配以 a、c 或者 e 结束的字符串。
LIKE
运算符适合简单的模式匹配。当我们需要匹配复杂的模式时,例如合法的电子邮箱,无法使用LIKE
运算符实现。此时,需要使用更强大的正则表达式。
MySQL 正则表达式
MySQL 提供了 REGEXP_LIKE 函数,用于实现正则表达式匹配:
REGEXP_LIKE(expr, pat[, match_type])
如果表达式 expr 匹配模式 pat,该函数返回 1,否则返回 0;如果 expr 或者 pat 为 NULL,该函数返回 NULL。可选的参数 match_type 可以指定多个额外的匹配选项:
- c:区分大小写;
- i:不区分大小写;
- m:多行模式,^ 和 $ 能够匹配字符串中的行终止符。默认情况下它们只匹配 expr 的开头和结尾;
- n:字符 . 能够匹配行终止符。默认情况下 . 遇到行尾将会匹配;
- u:只匹配 Unix 行终止符,只有换行符(\n)能够匹配 .、^ 以及 $。
我们使用正则表达式实现上文中的电子邮箱校验:
select email
from test
where regexp_like(email, '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\\.[a-z]{2,4}$');
email |
----------------|
TEST@qq |
123.test@sql|
查询结果显式只有 2 个电子邮箱是合法的。注意,模式中的反斜杠需要写双份,因为 MySQL 使用 C 语言风格的转义语法。
另外,REGEXP 和 RLIKE 运算符是 REGEXP_LIKE 函数的同义词:
expr REGEXP pat
expr RLIKE pat
Oracle 正则表达式
Oracle 提供了与 MySQL 类似的 REGEXP_LIKE 函数,用于执行正则表达式匹配:
REGEXP_LIKE(source_char, pattern[, match_param ])
Oracle 中的 REGEXP_LIKE 只能作为条件使用;如果字符串 source_char 匹配模式 pat 就返回结果;否则不返回结果。可选的参数 match_param 可以指定多个额外的匹配选项:
- c 表示区分大小写和重音;
- i 表示不区分大小写和重音;
- n 表示符号 . 能够匹配换行符(\n)。默认情况下不匹配换行符;
- m 表示多行模式,^ 和 $ 能够匹配字符串中的任何行的开始和结尾,而不仅仅是整个字符串的开头和结尾;
- x 表示忽略模式中的空白字符,默认情况下空白字符匹配它们自身。
如果省略 match_param 参数,是否区分大小写和重音取决于数据的字符集排序规则;同时符号 . 不会匹配换行符,并且字符串 source_char 被看做单行字符串。
对于上文中的电子邮箱校验,Oracle 中的实现如下:
select email
from test
where regexp_like(email, '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,4}$', 'i');
email |
----------------|
TEST@qq |
123.test@sql|
选项 i 表示不区分大小写。
SQL Server 正则表达式
SQL Server 目前没有提供类似其他数据库的正则表达式函数或者运算符,可以通过 CLR 编写自定义函数实现。
PostgreSQL 正则表达式
PostgreSQL 提供了一组相关的正则表达式匹配运算符:
expr ~ pattern
,正则表达式匹配,区分大小写;expr ~* pattern
,正则表达式匹配,不区分大小写;expr !~ pattern
,不匹配正则表达式,区分大小写;expr !~* pattern
,不匹配正则表达式,区分大小写;
对于电子邮箱的合法性验证,可以使用以下查询语句:
select email
from test
where email ~* '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,4}$';
email |
----------------|
TEST@qq |
123.test@sql|
上例中使用了不区分大小写的匹配运算符。
另外,PostgreSQL 还提供了一个运算符SIMILAR TO
,用于执行一种特殊的 SQL 正则表达式匹配。不过,SQL 标准已经删除了这个运算符,不推荐使用。
SQLite 正则表达式
SQLite 默认没有提供了正则表达式匹配的运算符,但是预定义了 X REGEXP Y 运算符接口,它实际上是调用了 regexp(Y,X) 用户函数。因此,我们可以通过创建一个函数 regexp(Y,X),例如安装并加载 sqlite3-pcre 模块,就可以获得基于 Perl 的正则表达式。
参考文档
以下是各种数据库关于字符串模式匹配的官方文档,使用时可以参考:
- MySQL 官方文档;
- Oracle 官方文档;
- SQL Server 官方文档;
- PostgreSQL 官方文档;
- SQLite 官方文档。
定期更新数据库领域相关文章,欢迎关注❤️、评论📝、点赞👍!
版权声明:本文标题:【SQL 模式匹配】如何验证电子邮箱的合法性? 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/xitong/1729200016a1189481.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论