正則表達(dá)式(REGEXP)與通配符(LIKE)的超詳細(xì)對比
1. REGEXP (正則表達(dá)式匹配)
用途與特點
REGEXP (正則表達(dá)式) 是一種強(qiáng)大的字符串匹配工具,用于執(zhí)行高級模式匹配操作。它提供了比簡單通配符更精確、更靈活的文本搜索能力,特別適合處理復(fù)雜的字符串匹配場景。
主要特點:
- 支持復(fù)雜的模式匹配(如開頭、結(jié)尾、字符集等)
- 可以實現(xiàn)精確的字符次數(shù)控制
- 提供分組、或操作等高級功能
- 適用于驗證、提取和替換文本中的特定模式
基本語法
SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';
其中:
column_name
是要進(jìn)行匹配的列名table_name
是要查詢的表名pattern
是要匹配的正則表達(dá)式模式
正則表達(dá)式元素詳解
基本元字符
.
:匹配任意單個字符(除了換行符)- 示例:
a.c
匹配 "abc"、"aac"、"a1c" 等
- 示例:
^
:匹配字符串的開始位置- 示例:
^a
匹配以"a"開頭的字符串
- 示例:
$
:匹配字符串的結(jié)束位置- 示例:
a$
匹配以"a"結(jié)尾的字符串
- 示例:
字符類
[]
:指定一個字符集合,匹配其中的任意字符- 示例:
[abc]
匹配"a"、"b"或"c" - 范圍模式:
[a-z]
匹配任意小寫字母
- 示例:
[^]
:指定一個不匹配的字符集合- 示例:
[^abc]
不匹配"a"、"b"或"c"
- 示例:
數(shù)量限定符
*
:匹配前面的模式零次或多次- 示例:
ab*c
匹配"ac"、"abc"、"abbc"等
- 示例:
+
:匹配前面的模式一次或多次- 示例:
ab+c
匹配"abc"、"abbc",但不匹配"ac"
- 示例:
?
:匹配前面的模式零次或一次- 示例:
ab?c
匹配"ac"或"abc"
- 示例:
{n}
:精確匹配前面的模式n次- 示例:
a{2}
匹配"aa"
- 示例:
{n,}
:匹配前面的模式至少n次- 示例:
a{2,}
匹配"aa"、"aaa"等
- 示例:
{n,m}
:匹配前面的模式至少n次且不超過m次- 示例:
a{2,4}
匹配"aa"、"aaa"或"aaaa"
- 示例:
特殊構(gòu)造
|
:邏輯"或"操作符- 示例:
abc|def
匹配"abc"或"def"
- 示例:
()
:用于組合模式- 示例:
a(bc)*
匹配"a"、"abc"、"abcbc"等
- 示例:
實際應(yīng)用示例
基本匹配
-- 查找包含字母"a"的所有行 SELECT * FROM products WHERE product_name REGEXP 'a';
位置匹配
-- 查找以"Pro"開頭的產(chǎn)品 SELECT * FROM products WHERE product_name REGEXP '^Pro'; -- 查找以"2023"結(jié)尾的訂單號 SELECT * FROM orders WHERE order_id REGEXP '2023$';
字符集匹配
-- 查找包含數(shù)字的電話號碼 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 (通配符匹配)
用途與特點
LIKE 是SQL中用于簡單字符串匹配的操作符,使用通配符進(jìn)行模式匹配。它適合簡單的字符串搜索和匹配需求。
主要特點:
- 語法簡單,易于使用
- 性能通常優(yōu)于正則表達(dá)式
- 適合簡單的模式匹配需求
- 支持兩種基本通配符:
%
和_
基本語法
SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';
其中:
%
匹配任意數(shù)量的字符(包括零個字符)_
匹配單個任意字符
通配符詳解
%
:匹配任意數(shù)量的字符(零個或多個)a%
:以"a"開頭的字符串%a
:以"a"結(jié)尾的字符串%a%
:包含"a"的字符串
_
:匹配單個任意字符a_c
:匹配"abc"、"a1c"、"a c"等_a%
:第二個字符是"a"的字符串
實際應(yīng)用示例
基本匹配
-- 查找包含"book"的產(chǎn)品 SELECT * FROM products WHERE product_name LIKE '%book%';
位置匹配
-- 查找以"Mr."開頭的人名 SELECT * FROM customers WHERE name LIKE 'Mr.%'; -- 查找以".com"結(jié)尾的郵箱 SELECT * FROM users WHERE email LIKE '%.com';
精確匹配
-- 查找5個字符長度的產(chǎn)品代碼 SELECT * FROM products WHERE product_code LIKE '_____'; -- 查找第二和第三個字符為"12"的訂單號 SELECT * FROM orders WHERE order_id LIKE '_12%';
特殊字符匹配
-- 查找包含百分號(%)的記錄(使用轉(zhuǎn)義) SELECT * FROM data WHERE notes LIKE '%\%%' ESCAPE '\'; -- 查找包含下劃線(_)的記錄 SELECT * FROM data WHERE notes LIKE '%\_%' ESCAPE '\';
性能優(yōu)化技巧
避免在模式開頭使用通配符:
-- 避免這樣寫(無法使用索引) SELECT * FROM users WHERE name LIKE '%smith'; -- 改為這樣寫(可以使用索引) SELECT * FROM users WHERE name LIKE 'smith%';
對于固定前綴的查詢,考慮使用范圍查詢:
-- 不如 SELECT * FROM products WHERE product_name LIKE 'A%'; -- 可以考慮 SELECT * FROM products WHERE product_name >= 'A' AND product_name < 'B';
對于簡單的相等匹配,使用
=
而非LIKE:-- 更好 SELECT * FROM users WHERE username = 'admin'; -- 不如 SELECT * FROM users WHERE username LIKE 'admin';
3. REGEXP與LIKE的區(qū)別與選擇
主要區(qū)別
特性 | REGEXP | LIKE |
---|---|---|
功能 | 強(qiáng)大,支持復(fù)雜模式 | 簡單,僅支持基本通配符 |
性能 | 通常較慢 | 通常較快 |
通配符 | 豐富的元字符(. ^ $ []等) | 僅%和_ |
學(xué)習(xí)曲線 | 較陡峭 | 較簡單 |
索引使用 | 一般不能使用索引 | 前綴匹配可以使用索引 |
適用場景 | 復(fù)雜模式匹配 | 簡單字符串搜索 |
選擇建議
使用LIKE的情況:
- 只需要簡單的字符串包含、開頭或結(jié)尾匹配
- 查詢性能是關(guān)鍵考慮因素
- 對大型表進(jìn)行前綴搜索(可以使用索引)
- 模式簡單且不需要復(fù)雜邏輯
-- 適合使用LIKE的示例 SELECT * FROM customers WHERE name LIKE 'John%';
使用REGEXP的情況:
- 需要復(fù)雜的模式匹配(如特定格式驗證)
- 需要匹配字符集合或排除特定字符
- 需要精確控制重復(fù)次數(shù)
- 需要"或"邏輯等高級功能
-- 適合使用REGEXP的示例 SELECT * FROM products WHERE product_code REGEXP '^[A-Z]{2}[0-9]{4}$';
混合使用場景: 在某些情況下,可以結(jié)合使用兩者以獲得最佳性能和靈活性:
-- 先用LIKE縮小范圍,再用REGEXP精確匹配 SELECT * FROM logs WHERE message LIKE '%error%' AND message REGEXP 'error [0-9]{3}';
性能對比示例
假設(shè)有一個包含100萬條記錄的用戶表:
-- LIKE查詢(較快,可以使用索引) SELECT * FROM users WHERE username LIKE 'admin%'; -- 等效的REGEXP查詢(較慢,無法使用索引) SELECT * FROM users WHERE username REGEXP '^admin';
對于這個簡單的前綴匹配,LIKE通常比REGEXP快10-100倍,特別是在有適當(dāng)索引的情況下。
4. 高級技巧與注意事項
正則表達(dá)式優(yōu)化
避免過度復(fù)雜的模式:復(fù)雜的正則表達(dá)式會顯著降低查詢速度
使用非貪婪匹配:在支持的情況下,使用
*?
或+?
進(jìn)行非貪婪匹配-- 匹配最短的可能結(jié)果 SELECT * FROM text WHERE content REGEXP '<div>.*?</div>';
預(yù)編譯正則表達(dá)式:某些數(shù)據(jù)庫支持預(yù)編譯正則表達(dá)式以提高性能
通配符使用技巧
ESCAPE子句:處理包含通配符本身的字符串
SELECT * FROM data WHERE notes LIKE '50\%%' ESCAPE '\';
字符集處理:考慮數(shù)據(jù)庫的字符集和排序規(guī)則對匹配的影響
大小寫敏感:大多數(shù)數(shù)據(jù)庫的LIKE是大小寫敏感的,但可以通過設(shè)置改變
數(shù)據(jù)庫特定差異
MySQL:
- REGEXP是RLIKE的同義詞
- 支持基本的正則表達(dá)式功能
- REGEXP默認(rèn)不區(qū)分大小寫,使用REGEXP BINARY進(jìn)行區(qū)分大小寫匹配
PostgreSQL:
- 使用
~
運(yùn)算符進(jìn)行正則匹配 - 支持更完整的正則表達(dá)式功能
- 提供
!~
運(yùn)算符進(jìn)行不匹配操作
- 使用
SQL Server:
- 沒有內(nèi)置的REGEXP功能
- 可以使用CLR集成添加正則表達(dá)式支持
- LIKE功能與其他數(shù)據(jù)庫類似
Oracle:
- 提供REGEXP_LIKE、REGEXP_REPLACE等函數(shù)
- 支持高級的正則表達(dá)式功能
5. 實際應(yīng)用場景
數(shù)據(jù)驗證
-- 驗證電子郵件格式 SELECT * FROM users WHERE email NOT REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$'; -- 驗證電話號碼格式 SELECT * FROM customers WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';
數(shù)據(jù)清洗
-- 查找并替換無效字符 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}';
日志分析
-- 查找特定錯誤模式的日志記錄 SELECT * FROM server_logs WHERE log_message REGEXP 'ERROR (500|503|504):'; -- 按錯誤類型分類統(tǒng)計 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)品目錄搜索
-- 高級產(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é)與最佳實踐
基本原則:
- 簡單匹配使用LIKE
- 復(fù)雜模式使用REGEXP
- 考慮查詢性能影響
性能最佳實踐:
- 對頻繁搜索的列創(chuàng)建適當(dāng)索引
- 避免在大型表上使用復(fù)雜的正則表達(dá)式
- 考慮將正則匹配放在應(yīng)用層處理
可讀性建議:
- 為復(fù)雜的正則表達(dá)式添加注釋
- 考慮將常用模式存儲為視圖或函數(shù)
- 在團(tuán)隊中建立一致的編碼標(biāo)準(zhǔn)
測試策略:
- 測試正則表達(dá)式與各種輸入數(shù)據(jù)的匹配情況
- 比較不同方法的性能表現(xiàn)
- 監(jiān)控生產(chǎn)環(huán)境中的查詢性能
通過合理選擇和使用REGEXP與LIKE,可以高效地處理各種字符串匹配需求,同時保持查詢性能和代碼可維護(hù)性。
總結(jié)
到此這篇關(guān)于正則表達(dá)式(REGEXP)與通配符(LIKE)超詳細(xì)對比的文章就介紹到這了,更多相關(guān)REGEXP與LIKE對比內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Window環(huán)境下MySQL?UDF提權(quán)
本文章僅記錄某次內(nèi)網(wǎng)滲透過程中遇到的MySQL?采用UDF提權(quán)等方式進(jìn)行獲取權(quán)限,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧<BR>2023-03-03Docker搭建MySQL并掛載數(shù)據(jù)的全過程
環(huán)境搭建費(fèi)時費(fèi)力,但要必不可少,這篇文章主要給大家介紹了關(guān)于Docker搭建MySQL并掛載數(shù)據(jù)的相關(guān)資料,文中通過圖文以及實例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2022-01-01mysql 5.6.14 win32 解壓縮版(免安裝)安裝配置教程
這篇文章主要介紹了mysql 5.6.14 win32 解壓縮版(免安裝)安裝配置教程,感興趣的小伙伴們可以參考一下2016-11-11mysql could not be resolved: Name or service not known
今天查看mysql日志的時候發(fā)現(xiàn)[Warning] IP address '10.0.0.220' could not be resolved: Name or service not known,原來是mysql DNS反解:skip-name-resolve的原因,屏蔽一下就可以了2015-08-08從入門到精通MySQL 數(shù)據(jù)庫索引(實戰(zhàn)案例)
索引是數(shù)據(jù)庫的目錄,提升查詢速度,主要類型包括BTree、Hash、全文、空間索引,需根據(jù)場景選擇,建議用于高頻查詢、關(guān)聯(lián)字段、排序等,避免重復(fù)率高或頻繁更新字段,本文給大家介紹MySQL 數(shù)據(jù)庫索引實戰(zhàn)案例,感興趣的朋友一起看看吧2025-06-06