在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

【SQL 模式匹配】如何驗(yàn)證電子郵箱的合法性？

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        準(zhǔn)備工作
        LIKE 運(yùn)算符
            轉(zhuǎn)義字符
            數(shù)據(jù)庫擴(kuò)展
        MySQL 正則表達(dá)式
        Oracle 正則表達(dá)式
        SQL Server 正則表達(dá)式
        PostgreSQL 正則表達(dá)式
        SQLite 正則表達(dá)式
        參考文檔

字符串的模式匹配（pattern matching）是指給定一個模式，然后判斷某個字符串是否滿足該模式。常見的應(yīng)用包括敏感詞的檢測、身份證校驗(yàn)、IP 地址驗(yàn)證等。

本文以驗(yàn)證電子郵箱的合法性為例，介紹如何在 SQL 中實(shí)現(xiàn)字符串的模式匹配，涉及的數(shù)據(jù)庫包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。下表列出了這些數(shù)據(jù)庫對于模式匹配的支持情況：

接下來我們詳細(xì)討論不同數(shù)據(jù)庫的具體實(shí)現(xiàn)細(xì)節(jié)。
準(zhǔn)備工作

我們首先創(chuàng)建一個測試表，并且生成一些測試數(shù)據(jù)：

CREATE TABLE test (
email VARCHAR(50)
);

INSERT INTO test VALUES (‘TEST@qq.com’);
INSERT INTO test VALUES (‘test@qq’);
INSERT INTO test VALUES (’.123@qq.com’);
INSERT INTO test VALUES (‘test+email@sina.cn’);
INSERT INTO test VALUES (‘me.qq.com’);
INSERT INTO test VALUES (‘123.test@sql.org’);

對于 Web 開發(fā)中常見的郵箱地址驗(yàn)證問題，我們首先需要定義一個合法電子郵箱的規(guī)則。電子郵箱一般格式為：用戶名@域名；以下是一個簡單的規(guī)則：

以字母或者數(shù)字開頭，后面跟一個或者多個字母、數(shù)組或特殊字符（ . _ - ）；
然后是一個 @ 字符；
接著是一個或者多個字母、數(shù)組或特殊字符（ . - ）；
最后是域名，即 . 以及 2 到 4 個字母。

對于以上規(guī)則，使用正則表達(dá)式可以描述如下：

¹+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,4}$

簡單說明一下，^ 表示匹配字符串的開頭；[a-zA-Z0-9] 匹配大小寫字母或數(shù)字；+ 表示匹配前面的內(nèi)容一次或多次；. 匹配任何一個字符，. 匹配點(diǎn)號自身；{2,4} 匹配前面的內(nèi)容 2 次到 4次；$ 表示字符串的結(jié)束。

??關(guān)于正則表達(dá)式的詳細(xì)介紹，可以參考 GitHub 上的正則表達(dá)式教程。

LIKE 運(yùn)算符

SQL 標(biāo)準(zhǔn)定義了 LIKE運(yùn)算符，可以用于簡單的模式匹配。其中有兩個特殊的通配符：

下劃線（_）匹配一個任意字符；
百分號（%）匹配零個或者多個任意字符。

以下語句用于查找以 test 開頭的字符串：

select email
from test
where email like ‘test%’;

– MySQL、SQL Server 和 SQLite

– Oracle 和 PostgreSQL

從查詢結(jié)果可以看出，MySQL、SQL Server 和 SQLite 中的LIKE運(yùn)算符默認(rèn)不區(qū)分大小寫；Oracle 和 PostgreSQL 中的LIKE運(yùn)算符默認(rèn)區(qū)分大小寫。

以下示例返回了由 4 個字符加上 @ 符號開始的字符串：

select email
from test
where email like ‘____@%’;

另外，NOT LIKE運(yùn)算符可以進(jìn)行反向匹配，也就是查找不匹配某個模式的字符串。
轉(zhuǎn)義字符

由于下劃線和百分號被看作通配符，我們無法判斷某個字符串中是否包含這兩個字符。此時需要用到轉(zhuǎn)義字符，它的作用就是將隨后的字符看作普通字符，例如：

‘60%’ like ‘60!%’ escape ‘!’ – 匹配
‘60%’ like ‘60%’ – 匹配
‘60@’ like ‘60!%’ escape ‘!’ – 不匹配

其中，escape 用于指定轉(zhuǎn)義字符，默認(rèn)情況下為反斜杠（\）。
數(shù)據(jù)庫擴(kuò)展

除了 SQL 標(biāo)準(zhǔn)中定義的行為之外，許多數(shù)據(jù)庫對LIKE運(yùn)算符進(jìn)行了擴(kuò)展：

MySQL 和 SQLite 允許對數(shù)字類型進(jìn)行模式匹配：10 LIKE '1%'；
Oracle 提供了LIKEC、LIKE2、LIKE4用于不同字符集的字符匹配；
SQL Server 支持使用 [ ] 匹配某個范圍內(nèi)的字符，[^ ] 匹配某個范圍之外的字符；
PostgreSQL 提供了不區(qū)分大小寫的ILIKE運(yùn)算符；
SQLite 提供了區(qū)分大小寫的編譯選項(xiàng)PRAGMA case_sensitive_like = true;，同時還提供了基于 Unix 文件路徑匹配的 GLOB 運(yùn)算符。

我們主要來看一下 SQL Server 中的范圍匹配，例如：

– SQL Server
select email
from test
where email like ‘[0-9]%’;

其中，[0-9] 表示任意數(shù)字；所以上面的查詢返回了以數(shù)字開頭的字符串。同理，’[^0-9]%'匹配不以數(shù)字開頭的字符串，%[ace]匹配以 a、c 或者 e 結(jié)束的字符串。

LIKE運(yùn)算符適合簡單的模式匹配。當(dāng)我們需要匹配復(fù)雜的模式時，例如合法的電子郵箱，無法使用LIKE運(yùn)算符實(shí)現(xiàn)。此時，需要使用更強(qiáng)大的正則表達(dá)式。
MySQL 正則表達(dá)式

MySQL 提供了 REGEXP_LIKE 函數(shù)，用于實(shí)現(xiàn)正則表達(dá)式匹配：

REGEXP_LIKE(expr, pat[, match_type])

如果表達(dá)式 expr 匹配模式 pat，該函數(shù)返回 1，否則返回 0；如果 expr 或者 pat 為 NULL，該函數(shù)返回 NULL?？蛇x的參數(shù) match_type 可以指定多個額外的匹配選項(xiàng)：

c：區(qū)分大小寫；
i：不區(qū)分大小寫；
m：多行模式，^ 和 $ 能夠匹配字符串中的行終止符。默認(rèn)情況下它們只匹配 expr 的開頭和結(jié)尾；
n：字符 . 能夠匹配行終止符。默認(rèn)情況下 . 遇到行尾將會匹配；
u：只匹配 Unix 行終止符，只有換行符（\n）能夠匹配 .、^ 以及 $。

我們使用正則表達(dá)式實(shí)現(xiàn)上文中的電子郵箱校驗(yàn)：

select email
from test
where regexp_like(email, ‘²+[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{2,4}$’);

查詢結(jié)果顯式只有 2 個電子郵箱是合法的。注意，模式中的反斜杠需要寫雙份，因?yàn)?MySQL 使用 C 語言風(fēng)格的轉(zhuǎn)義語法。

另外，REGEXP 和 RLIKE 運(yùn)算符是 REGEXP_LIKE 函數(shù)的同義詞：

expr REGEXP pat
expr RLIKE pat

Oracle 正則表達(dá)式

Oracle 提供了與 MySQL 類似的 REGEXP_LIKE 函數(shù)，用于執(zhí)行正則表達(dá)式匹配：

REGEXP_LIKE(source_char, pattern[, match_param ])

Oracle 中的 REGEXP_LIKE 只能作為條件使用；如果字符串 source_char 匹配模式 pat 就返回結(jié)果；否則不返回結(jié)果?？蛇x的參數(shù) match_param 可以指定多個額外的匹配選項(xiàng)：

c 表示區(qū)分大小寫和重音；
i 表示不區(qū)分大小寫和重音；
n 表示符號 . 能夠匹配換行符（\n）。默認(rèn)情況下不匹配換行符；
m 表示多行模式，^ 和 $ 能夠匹配字符串中的任何行的開始和結(jié)尾，而不僅僅是整個字符串的開頭和結(jié)尾；
x 表示忽略模式中的空白字符，默認(rèn)情況下空白字符匹配它們自身。

如果省略 match_param 參數(shù)，是否區(qū)分大小寫和重音取決于數(shù)據(jù)的字符集排序規(guī)則；同時符號 . 不會匹配換行符，并且字符串 source_char 被看做單行字符串。

對于上文中的電子郵箱校驗(yàn)，Oracle 中的實(shí)現(xiàn)如下：

select email
from test
where regexp_like(email, ‘³+[a-z0-9._-]+@[a-z0-9.-]+.[a-z]{2,4}$’, ‘i’);

選項(xiàng) i 表示不區(qū)分大小寫。
SQL Server 正則表達(dá)式

SQL Server 目前沒有提供類似其他數(shù)據(jù)庫的正則表達(dá)式函數(shù)或者運(yùn)算符，可以通過 CLR 編寫自定義函數(shù)實(shí)現(xiàn)。
PostgreSQL 正則表達(dá)式

PostgreSQL 提供了一組相關(guān)的正則表達(dá)式匹配運(yùn)算符：

expr ~ pattern，正則表達(dá)式匹配，區(qū)分大小寫；
expr ~* pattern，正則表達(dá)式匹配，不區(qū)分大小寫；
expr !~ pattern，不匹配正則表達(dá)式，區(qū)分大小寫；
expr !~* pattern，不匹配正則表達(dá)式，區(qū)分大小寫；

對于電子郵箱的合法性驗(yàn)證，可以使用以下查詢語句：

select email
from test
where email ~* ‘⁴+[a-z0-9._-]+@[a-z0-9.-]+.[a-z]{2,4}$’;

上例中使用了不區(qū)分大小寫的匹配運(yùn)算符。

另外，PostgreSQL 還提供了一個運(yùn)算符SIMILAR TO，用于執(zhí)行一種特殊的 SQL 正則表達(dá)式匹配。不過，SQL 標(biāo)準(zhǔn)已經(jīng)刪除了這個運(yùn)算符，不推薦使用。
SQLite 正則表達(dá)式

SQLite 默認(rèn)沒有提供了正則表達(dá)式匹配的運(yùn)算符，但是預(yù)定義了 X REGEXP Y 運(yùn)算符接口，它實(shí)際上是調(diào)用了 regexp(Y,X) 用戶函數(shù)。因此，我們可以通過創(chuàng)建一個函數(shù) regexp(Y,X)，例如安裝并加載 sqlite3-pcre 模塊，就可以獲得基于 Perl 的正則表達(dá)式。
參考文檔

以下是各種數(shù)據(jù)庫關(guān)于字符串模式匹配的官方文檔，使用時可以參考：

MySQL 官方文檔；
Oracle 官方文檔；
SQL Server 官方文檔；
PostgreSQL 官方文檔；
SQLite 官方文檔。