1. REGEXP (正則表達(dá)式匹配)

用途與特點(diǎn)

REGEXP (正則表達(dá)式) 是一種強(qiáng)大的字符串匹配工具，用于執(zhí)行高級(jí)模式匹配操作。它提供了比簡(jiǎn)單通配符更精確、更靈活的文本搜索能力，特別適合處理復(fù)雜的字符串匹配場(chǎng)景。

主要特點(diǎn)：

• 支持復(fù)雜的模式匹配（如開(kāi)頭、結(jié)尾、字符集等）
• 可以實(shí)現(xiàn)精確的字符次數(shù)控制
• 提供分組、或操作等高級(jí)功能
• 適用于驗(yàn)證、提取和替換文本中的特定模式

基本語(yǔ)法

SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';

其中：

• column_name是要進(jìn)行匹配的列名
• table_name是要查詢的表名
• pattern是要匹配的正則表達(dá)式模式

正則表達(dá)式元素詳解

基本元字符

1. .：匹配任意單個(gè)字符（除了換行符）
   • 示例：a.c 匹配 "abc"、"aac"、"a1c" 等
2. ^：匹配字符串的開(kāi)始位置
   • 示例：^a 匹配以"a"開(kāi)頭的字符串
3. $：匹配字符串的結(jié)束位置
   • 示例：a$ 匹配以"a"結(jié)尾的字符串

字符類

1. []：指定一個(gè)字符集合，匹配其中的任意字符
   • 示例：[abc] 匹配"a"、"b"或"c"
   • 范圍模式：[a-z] 匹配任意小寫字母
2. [^]：指定一個(gè)不匹配的字符集合
   • 示例：[^abc] 不匹配"a"、"b"或"c"

數(shù)量限定符

1. *：匹配前面的模式零次或多次
   • 示例：ab*c 匹配"ac"、"abc"、"abbc"等
2. +：匹配前面的模式一次或多次
   • 示例：ab+c 匹配"abc"、"abbc"，但不匹配"ac"
3. ?：匹配前面的模式零次或一次
   • 示例：ab?c 匹配"ac"或"abc"
4. {n}：精確匹配前面的模式n次
   • 示例：a{2} 匹配"aa"
5. {n,}：匹配前面的模式至少n次
   • 示例：a{2,} 匹配"aa"、"aaa"等
6. {n,m}：匹配前面的模式至少n次且不超過(guò)m次
   • 示例：a{2,4} 匹配"aa"、"aaa"或"aaaa"

特殊構(gòu)造

1. |：邏輯"或"操作符
   • 示例：abc|def 匹配"abc"或"def"
2. ()：用于組合模式
   • 示例：a(bc)* 匹配"a"、"abc"、"abcbc"等

實(shí)際應(yīng)用示例

基本匹配

-- 查找包含字母"a"的所有行
SELECT * FROM products WHERE product_name REGEXP 'a';

位置匹配

-- 查找以"Pro"開(kāi)頭的產(chǎn)品
SELECT * FROM products WHERE product_name REGEXP '^Pro';

-- 查找以"2023"結(jié)尾的訂單號(hào)
SELECT * FROM orders WHERE order_id REGEXP '2023$';

字符集匹配

-- 查找包含數(shù)字的電話號(hào)碼
SELECT * FROM customers WHERE phone REGEXP '[0-9]';

-- 查找不包含元音字母的產(chǎn)品名稱
SELECT * FROM products WHERE product_name REGEXP '[^aeiou]';

復(fù)雜模式

-- 查找包含"error"或"warning"的日志記錄
SELECT * FROM system_logs WHERE message REGEXP 'error|warning';

-- 查找標(biāo)準(zhǔn)的電子郵件格式
SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$';

-- 查找重復(fù)字母的單詞
SELECT * FROM words WHERE word REGEXP '([a-zA-Z])\\1';

2. LIKE (通配符匹配)

用途與特點(diǎn)

LIKE 是SQL中用于簡(jiǎn)單字符串匹配的操作符，使用通配符進(jìn)行模式匹配。它適合簡(jiǎn)單的字符串搜索和匹配需求。

主要特點(diǎn)：

• 語(yǔ)法簡(jiǎn)單，易于使用
• 性能通常優(yōu)于正則表達(dá)式
• 適合簡(jiǎn)單的模式匹配需求
• 支持兩種基本通配符：%和_

基本語(yǔ)法

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';

其中：

• % 匹配任意數(shù)量的字符（包括零個(gè)字符）
• _ 匹配單個(gè)任意字符

通配符詳解

1. %：匹配任意數(shù)量的字符（零個(gè)或多個(gè)）

   • a%：以"a"開(kāi)頭的字符串
   • %a：以"a"結(jié)尾的字符串
   • %a%：包含"a"的字符串

2. _：匹配單個(gè)任意字符

   • a_c：匹配"abc"、"a1c"、"a c"等
   • _a%：第二個(gè)字符是"a"的字符串

實(shí)際應(yīng)用示例

基本匹配

-- 查找包含"book"的產(chǎn)品
SELECT * FROM products WHERE product_name LIKE '%book%';

位置匹配

-- 查找以"Mr."開(kāi)頭的人名
SELECT * FROM customers WHERE name LIKE 'Mr.%';

-- 查找以".com"結(jié)尾的郵箱
SELECT * FROM users WHERE email LIKE '%.com';

精確匹配

-- 查找5個(gè)字符長(zhǎng)度的產(chǎn)品代碼
SELECT * FROM products WHERE product_code LIKE '_____';

-- 查找第二和第三個(gè)字符為"12"的訂單號(hào)
SELECT * FROM orders WHERE order_id LIKE '_12%';

特殊字符匹配

-- 查找包含百分號(hào)(%)的記錄(使用轉(zhuǎn)義)
SELECT * FROM data WHERE notes LIKE '%\%%' ESCAPE '\';

-- 查找包含下劃線(_)的記錄
SELECT * FROM data WHERE notes LIKE '%\_%' ESCAPE '\';

性能優(yōu)化技巧

1. 避免在模式開(kāi)頭使用通配符：

   -- 避免這樣寫（無(wú)法使用索引）
   SELECT * FROM users WHERE name LIKE '%smith';
   
   -- 改為這樣寫（可以使用索引）
   SELECT * FROM users WHERE name LIKE 'smith%';
   

2. 對(duì)于固定前綴的查詢，考慮使用范圍查詢：

   -- 不如
   SELECT * FROM products WHERE product_name LIKE 'A%';
   
   -- 可以考慮
   SELECT * FROM products WHERE product_name >= 'A' AND product_name < 'B';
   

3. 對(duì)于簡(jiǎn)單的相等匹配，使用=而非LIKE：

   -- 更好
   SELECT * FROM users WHERE username = 'admin';
   
   -- 不如
   SELECT * FROM users WHERE username LIKE 'admin';
   

3. REGEXP與LIKE的區(qū)別與選擇

主要區(qū)別

選擇建議

1. 使用LIKE的情況：

   • 只需要簡(jiǎn)單的字符串包含、開(kāi)頭或結(jié)尾匹配
   • 查詢性能是關(guān)鍵考慮因素
   • 對(duì)大型表進(jìn)行前綴搜索（可以使用索引）
   • 模式簡(jiǎn)單且不需要復(fù)雜邏輯

   -- 適合使用LIKE的示例
   SELECT * FROM customers WHERE name LIKE 'John%';
   

2. 使用REGEXP的情況：

   • 需要復(fù)雜的模式匹配（如特定格式驗(yàn)證）
   • 需要匹配字符集合或排除特定字符
   • 需要精確控制重復(fù)次數(shù)
   • 需要"或"邏輯等高級(jí)功能

   -- 適合使用REGEXP的示例
   SELECT * FROM products WHERE product_code REGEXP '^[A-Z]{2}[0-9]{4}$';
   

3. 混合使用場(chǎng)景： 在某些情況下，可以結(jié)合使用兩者以獲得最佳性能和靈活性：

   -- 先用LIKE縮小范圍，再用REGEXP精確匹配
   SELECT * FROM logs 
   WHERE message LIKE '%error%' 
   AND message REGEXP 'error [0-9]{3}';
   

性能對(duì)比示例

假設(shè)有一個(gè)包含100萬(wàn)條記錄的用戶表：

-- LIKE查詢（較快，可以使用索引）
SELECT * FROM users WHERE username LIKE 'admin%';

-- 等效的REGEXP查詢（較慢，無(wú)法使用索引）
SELECT * FROM users WHERE username REGEXP '^admin';

對(duì)于這個(gè)簡(jiǎn)單的前綴匹配，LIKE通常比REGEXP快10-100倍，特別是在有適當(dāng)索引的情況下。

4. 高級(jí)技巧與注意事項(xiàng)

正則表達(dá)式優(yōu)化

1. 避免過(guò)度復(fù)雜的模式：復(fù)雜的正則表達(dá)式會(huì)顯著降低查詢速度

2. 使用非貪婪匹配：在支持的情況下，使用*?或+?進(jìn)行非貪婪匹配

   -- 匹配最短的可能結(jié)果
   SELECT * FROM text WHERE content REGEXP '<div>.*?</div>';
   

3. 預(yù)編譯正則表達(dá)式：某些數(shù)據(jù)庫(kù)支持預(yù)編譯正則表達(dá)式以提高性能

通配符使用技巧

1. ESCAPE子句：處理包含通配符本身的字符串

   SELECT * FROM data WHERE notes LIKE '50\%%' ESCAPE '\';
   

2. 字符集處理：考慮數(shù)據(jù)庫(kù)的字符集和排序規(guī)則對(duì)匹配的影響

3. 大小寫敏感：大多數(shù)數(shù)據(jù)庫(kù)的LIKE是大小寫敏感的，但可以通過(guò)設(shè)置改變

數(shù)據(jù)庫(kù)特定差異

1. MySQL：

   • REGEXP是RLIKE的同義詞
   • 支持基本的正則表達(dá)式功能
   • REGEXP默認(rèn)不區(qū)分大小寫，使用REGEXP BINARY進(jìn)行區(qū)分大小寫匹配

2. PostgreSQL：

   • 使用~運(yùn)算符進(jìn)行正則匹配
   • 支持更完整的正則表達(dá)式功能
   • 提供!~運(yùn)算符進(jìn)行不匹配操作

3. SQL Server：

   • 沒(méi)有內(nèi)置的REGEXP功能
   • 可以使用CLR集成添加正則表達(dá)式支持
   • LIKE功能與其他數(shù)據(jù)庫(kù)類似

4. Oracle：

   • 提供REGEXP_LIKE、REGEXP_REPLACE等函數(shù)
   • 支持高級(jí)的正則表達(dá)式功能

5. 實(shí)際應(yīng)用場(chǎng)景

數(shù)據(jù)驗(yàn)證

-- 驗(yàn)證電子郵件格式
SELECT * FROM users WHERE email NOT REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$';

-- 驗(yàn)證電話號(hào)碼格式
SELECT * FROM customers WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

數(shù)據(jù)清洗

-- 查找并替換無(wú)效字符
UPDATE products 
SET product_name = REGEXP_REPLACE(product_name, '[^a-zA-Z0-9 ]', '')
WHERE product_name REGEXP '[^a-zA-Z0-9 ]';

-- 標(biāo)準(zhǔn)化日期格式
UPDATE orders 
SET order_date = STR_TO_DATE(
    REGEXP_SUBSTR(order_date_field, '[0-9]{4}-[0-9]{2}-[0-9]{2}'),
    '%Y-%m-%d'
)
WHERE order_date_field REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}';

日志分析

-- 查找特定錯(cuò)誤模式的日志記錄
SELECT * FROM server_logs 
WHERE log_message REGEXP 'ERROR (500|503|504):';

-- 按錯(cuò)誤類型分類統(tǒng)計(jì)
SELECT 
    REGEXP_SUBSTR(error_message, '[A-Za-z]+Error') AS error_type,
    COUNT(*) AS count
FROM application_logs
WHERE error_message REGEXP '[A-Za-z]+Error'
GROUP BY error_type;

產(chǎn)品目錄搜索

-- 高級(jí)產(chǎn)品搜索（支持多種匹配方式）
SELECT * FROM products
WHERE 
    (product_name LIKE '%organic%' OR description LIKE '%organic%')
    AND product_code REGEXP '^[A-Z]{2}[0-9]{3}'
    AND price BETWEEN 10 AND 100;

6. 總結(jié)與最佳實(shí)踐

1. 基本原則：

   • 簡(jiǎn)單匹配使用LIKE
   • 復(fù)雜模式使用REGEXP
   • 考慮查詢性能影響

2. 性能最佳實(shí)踐：

   • 對(duì)頻繁搜索的列創(chuàng)建適當(dāng)索引
   • 避免在大型表上使用復(fù)雜的正則表達(dá)式
   • 考慮將正則匹配放在應(yīng)用層處理

3. 可讀性建議：

   • 為復(fù)雜的正則表達(dá)式添加注釋
   • 考慮將常用模式存儲(chǔ)為視圖或函數(shù)
   • 在團(tuán)隊(duì)中建立一致的編碼標(biāo)準(zhǔn)

4. 測(cè)試策略：

   • 測(cè)試正則表達(dá)式與各種輸入數(shù)據(jù)的匹配情況
   • 比較不同方法的性能表現(xiàn)
   • 監(jiān)控生產(chǎn)環(huán)境中的查詢性能

通過(guò)合理選擇和使用REGEXP與LIKE，可以高效地處理各種字符串匹配需求，同時(shí)保持查詢性能和代碼可維護(hù)性。

總結(jié)

到此這篇關(guān)于正則表達(dá)式(REGEXP)與通配符(LIKE)超詳細(xì)對(duì)比的文章就介紹到這了,更多相關(guān)REGEXP與LIKE對(duì)比內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論