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

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

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

文章目錄

        準備工作
        LIKE 運算符
            轉義字符
            數(shù)據(jù)庫擴展
        MySQL 正則表達式
        Oracle 正則表達式
        SQL Server 正則表達式
        PostgreSQL 正則表達式
        SQLite 正則表達式
        參考文檔

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

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

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

我們首先創(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ā)中常見的郵箱地址驗證問題，我們首先需要定義一個合法電子郵箱的規(guī)則。電子郵箱一般格式為：用戶名@域名；以下是一個簡單的規(guī)則：

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

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

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

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

??關于正則表達式的詳細介紹，可以參考 GitHub 上的正則表達式教程。

LIKE 運算符

SQL 標準定義了 LIKE運算符，可以用于簡單的模式匹配。其中有兩個特殊的通配符：

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

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

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

– MySQL、SQL Server 和 SQLite

– Oracle 和 PostgreSQL

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

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

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

另外，NOT LIKE運算符可以進行反向匹配，也就是查找不匹配某個模式的字符串。
轉義字符

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

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

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

除了 SQL 標準中定義的行為之外，許多數(shù)據(jù)庫對LIKE運算符進行了擴展：

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

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

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

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

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

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

REGEXP_LIKE(expr, pat[, match_type])

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

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

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

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

查詢結果顯式只有 2 個電子郵箱是合法的。注意，模式中的反斜杠需要寫雙份，因為 MySQL 使用 C 語言風格的轉義語法。

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

expr REGEXP pat
expr RLIKE pat

Oracle 正則表達式

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

REGEXP_LIKE(source_char, pattern[, match_param ])

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

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

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

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

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

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

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

PostgreSQL 提供了一組相關的正則表達式匹配運算符：

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

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

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

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

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

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

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

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