一、LIKE / NOT LIKE:標準 SQL 模式匹配
| 通配符 | 含義 |
_ | 任意單字符 |
% | 任意多字符(含 0 個) |
常用模式
WHERE col LIKE 'm%' -- 以 m/M 開頭
WHERE col LIKE '%m%' -- 包含 m/M
WHERE col LIKE '%m' -- 以 m/M 結尾
WHERE col LIKE '_m_' -- 3 字符、中間是 m/M
轉義通配符
WHERE col LIKE '%50\%%' ESCAPE '\' -- 包含 "50%"
WHERE col LIKE '%\_get%' ESCAPE '\' -- 包含 "_get"
二、REGEXP / RLIKE:擴展正則表達式
| 元字符 | 含義 |
. | 任意單字符 |
^ | 行首 |
$ | 行尾 |
[abc] | 字符集合 |
[a-z] | 範圍 |
* | 0 次或多次 |
+ | 1 次或多次 |
? | 0 次或 1 次 |
{n} | 恰好 n 次 |
{m,n} | m 到 n 次 |
例子
WHERE col REGEXP '^50%{1,3}' -- 以 "50%"、"50%%"、"50%%%" 開頭
三、FULLTEXT 全文索引:海量文本的專業級方案
建表與索引
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
catname CHAR(255) NOT NULL,
FULLTEXT KEY ft_catname (catname)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
自然語言模式(默認)
SELECT * FROM category WHERE MATCH(catname) AGAINST('phpjs');
-- 忽略大小寫,按相關度排序
兩大隱形門檻
- 最小詞長:默認
ft_min_word_len=4,詞長 <4 被忽略。改小需修 my.cnf 重啟:ft_min_word_len=2
- 50% 閾值:出現在超過 50% 行的詞被視為停用詞,**直接不返回**。
布爾模式繞過 50% 限制
SELECT * FROM category WHERE MATCH(catname) AGAINST('you' IN BOOLEAN MODE);
-- 支援 + - * "" 等操作符,不計算相關度,不受 50% 限制
選型建議
- 簡單前綴/後綴/包含 →
LIKE(有索引前綴可用)
- 複雜模式、提取、替換 →
REGEXP(不走索引,全掃)
- 大文本、多關鍵詞、相關度排序 →
FULLTEXT(MyISAM/InnoDB 5.6+)
- 生產級搜尋建議接入 Elasticsearch / OpenSearch