一、Mysql中，like與regexp的區(qū)別

1、適用對象不同

LIKE 函數(shù)適用于簡單的通配符匹配，例如使用百分號（%）匹配任意字符，或使用下劃線（_）匹配單個字符。與此相反，REGEXP 函數(shù)支持更靈活、更強大的正則表達式模式匹配功能。

2、速度不同

LIKE 函數(shù)比 REGEXP 函數(shù)更快，因為它通常能夠使用索引優(yōu)化查詢操作，即使查詢中使用了通配符。但對于復雜的模式匹配，REGEXP 函數(shù)的性能通常也非常出色。

3、語法不同

LIKE 函數(shù)和 REGEXP 函數(shù)還存在一些語法差異，例如使用LIKE函數(shù)時可以使用轉義字符來表示通配符字符本身，而使用REGEXP函數(shù)時需要使用反斜杠來表示正則表達式字符本身。

二、MySQL的模糊查詢

為了便于描述和測試不同模糊查詢方式結果，首先給出一個簡單的測試用數(shù)據(jù)表tests如下：

其中，tests表僅含有一個名為words的字段，并對該字段添加全文索引。表中共有6條記錄。

1、Like

Like算作MySQL中的謂詞，其應用與is、=、>和<等符號用法類似。Like主要支持兩種通配符，分別是”_”和”%”，其中前者代表匹配1個任意字符，常用于充當占位符；而后者代表匹配0個或多個任意字符。從某種意義上講，Like可看作是一個精簡的正則表達式功能。

例如，在如上表中查找所有以”hello”開頭的記錄，則其SQL語句為：

SELECT words FROM tests WHERE words LIKE 'hello%';

查詢結果：

如果想查找所有以”hello”開頭且至少含有6個字符的記錄，則可簡單修改SQL語句如下：

SELECT words FROM tests WHERE words LIKE 'hello_%';

查詢結果：

另外，當在Like模式字段中，若不包含任何”_”和”%”通配符，則等價于”=”，表示精確匹配，例如查詢語句……Like “hello”，則僅返回hello一條記錄；還可在Like前加限定詞Not，表示結果取反。

2、RegExp

正則表達式具有龐大而豐富的語法，MySQL語法中支持絕大部分正則表達式功能，幾乎可以滿足所有需求。本文不過多展開正則表達式相關介紹，僅在Like的基礎上，簡單介紹其與Like模糊搜索方式的區(qū)別。

如前所述，Like匹配原則是要求模式串與整個目標字段匹配時，才返回該條記錄；而RegExp中則是當目標字段包含模式串時即返回該條記錄。例如如下SQL語句將返回所有包含”hello”的記錄：

SELECT words FROM tests WHERE words REGEXP 'hello';

而在Like中這樣的寫法僅返回記錄=”hello”的記錄。為了限定正則表達式以某個模式串開頭或者結尾，可以通過添加”^”和”$”標識符來限定，例如仍然搜索以”hello”開頭的目標字段，則其SQL語句為：

SELECT words FROM tests WHERE words REGEXP '^hello';

3、內(nèi)置函數(shù)

對于包含某些特定模式串的模糊搜索，可以通過MySQL內(nèi)置函數(shù)實現(xiàn)。可以完成這一功能的函數(shù)包括Instr()、Locate()和Position()等，其功能語法很相近，均是返回子串在字符串中的索引，且索引下標從1開始，當子串不存在是返回0。需要注意的是三個函數(shù)中子串和字符串的先后順序是不一致的。例如以下語句均成功檢索，且返回目標索引1。

SELECT INSTR("hello,world", 'hello');-- 1 SELECT LOCATE('hello', "hello,world");-- 1 SELECT POSITION('hello' in "hello, world"); -- 1

應用以上3個內(nèi)置函數(shù)，搜索上述測試表中包含”hello”的記錄，則相應SQL語句為：

SELECT words  FROM tests WHERE INSTR(words, 'hello'); SELECT words  FROM tests WHERE LOCATE('hello', words); SELECT words  FROM tests WHERE POSITION('hello' in words);

4、全文索引

全文索引是MySQL中索引的一種，曾經(jīng)僅在引擎為MyISAM的表中支持，從5.6版本開始在InnoDB中也開始支持全文索引，支持的字段格式包括CHAR、VARCHAR和TEXT。在如上已經(jīng)添加了全文索引的tests表中，仍然查詢包含”hello”的記錄，應用全文索引查詢的SQL語句為：

SELECT words FROM tests WHERE MATCH(words) against('hello');

實際上，MATCH(words) against(‘hello’)返回的是字段words對目標字符”hello”的匹配程度：當不存在任何匹配結果時，返回0；否則，根據(jù)匹配次數(shù)的多少和位置先后返回一個匹配度。例如，如下SQL語句返回表中每條記錄對目標字段”hello”的匹配度：

SELECT MATCH(words) against('hello') FROM tests;

返回結果如下：

5、查詢性能對比

為了對比以上4種模糊搜索方式的性能，我們這里構建一個規(guī)模較大且更具一般性的數(shù)據(jù)表。本文選擇采集若干條英文格言，用于創(chuàng)建目標數(shù)據(jù)庫。創(chuàng)建數(shù)據(jù)表。為簡單起見，僅創(chuàng)建一個名為says的字段，且對其添加全文索引。

CREATE TABLE IF NOT EXISTS sayings(says TEXT, FULLTEXT (says));

英文格言信息獲取：在網(wǎng)上找了個英文格言的網(wǎng)站，并寫了一個python小爬蟲爬取頁面全部300條英文格言，爬蟲源碼如下（為了增加記錄條數(shù)，將300條記錄重寫100詞，即數(shù)據(jù)庫中包含30000條記錄）：

from pyquery import PyQuery  as pq  from pymysql import connect  doc = pq(url='http://www.1juzi.com/new/43141.html', encoding = 'gb18030') items=doc("div.content>p:nth-child(2n+1)").items()  hots = [item.text() for item in items]  with connect(host="localhost", user="root", password="123456", db='teststr', charset='utf8') as cur:      sql_insert = 'insert into sayings values (%s);'      for _ in range(100):         cur.executemany(sql_insert, hots)

對爬取的英文短句寫入創(chuàng)建的數(shù)據(jù)表中。既然是英文勵志格言短句，那么我們就來查詢其中包括”success”的記錄。首先查詢語句中任意位置包含“success”的記錄，4種方式SQL語句及執(zhí)行時間為：

-- LIKE通配符  SELECT says FROM sayings WHERE says LIKE '%success%'  > OK  > 時間: 0.036s  -- REGEXP正則匹配  SELECT says FROM sayings WHERE says REGEXP 'success'  > OK  > 時間: 0.053s -- 內(nèi)置函數(shù)查找 SELECT says FROM sayings WHERE INSTR(says, 'success') > OK > 時間: 0.045s SELECT says FROM sayings WHERE LOCATE('success', says) > OK > 時間: 0.044s SELECT says FROM sayings WHERE POSITION('success' in says) > OK > 時間: 0.047s -- 全文索引 SELECT says FROM sayings WHERE MATCH(says) against('Success') > OK > 時間: 0.006s

可見，全文索引速度最寬，名列前茅其他方式接近一個量級；Like通配符速度其次，但與其他幾種查詢方式效率相差不大。通過Explain查詢計劃，我們可以發(fā)現(xiàn)全文索引方式由于應用了索引而無需全表查詢，所以執(zhí)行速度快，而其他三種模糊查詢方式均為執(zhí)行全表查詢。

實際上，對于添加索引的字段應用Like查詢時，可以應用索引加速查詢，為勒驗證全文索引條件下是否仍然可以應用索引，我們進行第二組性能測試：查詢語句中以“success”開頭的記錄（全文索引方式不支持指定單詞開頭的查詢?nèi)蝿眨鄳猄QL語句即執(zhí)行時間如下：

SELECT says FROM sayings WHERE says LIKE 'success%'  > OK  > 時間: 0.015s  SELECT says FROM sayings WHERE says REGEXP '^success'  > OK  > 時間: 0.046s  SELECT says FROM sayings WHERE INSTR(says, 'success')=1 > OK > 時間: 0.042s SELECT says FROM sayings WHERE LOCATE('success', says)=1 > OK > 時間: 0.051s SELECT says FROM sayings WHERE POSITION('success' in says)=1 > OK > 時間: 0.049s 20 21SELECT says FROM sayings WHERE MATCH(says) against('Success') > OK > 時間: 0.007s

可以看到，修改后的Like查詢效率提升明顯，并大幅超過其他方式。但解釋查詢計劃發(fā)現(xiàn)，雖然possible_key顯示了索引字段，但實際仍然未應用任何索引（key為null），即仍然進行全表查詢（Type = All）。之所以帶來速度上的大幅提升，僅僅是因為對’success%’要比’%success%’執(zhí)行字符串匹配要快得多（后者要整列匹配，前者僅需匹配開頭的單詞即可），而與索引無關。

所以，得到的結論是Like通配符無法有效利用全文索引加速查詢，但在特定模式下的查詢速度可快于通配符%模式下的查詢。

延伸閱讀1：MySQL的優(yōu)勢