MySQL 字串比較函數完全指南：LIKE、REGEXP、STRCMP 實戰詳解

前言：字串比較的隱性規則

MySQL 字串比較看似簡單，實則暗藏「隱式轉換」、「大小寫敏感度」、「二進位比較」等關鍵細節。本文深度解析 LIKE、REGEXP、STRCMP 核心函數與運算符，並釐清常見誤區，助你精準掌控字串匹配邏輯。

一、隱式類型轉換與二進位語境

• 數字與字串自動互轉：SELECT 1 + '1'; -- 2，SELECT CONCAT(2, ' test'); -- '2 test'
• 顯式轉字串：CAST(38.8 AS CHAR) 或 CONCAT(38.8)，推薦 CAST()。
• 關鍵規則：若任一運算元為二進位字串（含 BINARY、BLOB、數字轉字串），比較將以位元組級別進行，區分大小寫、依編碼權重排序。

二、LIKE 模式匹配：通配符與轉義藝術

語法

expr LIKE pat [ESCAPE 'escape_char']

通配符

• %：匹配任意長度字元（含 0 個）
• _：匹配單一字元

轉義機制

SELECT 'David_' LIKE 'David\_';        -- 1 (預設 \ 為轉義)
SELECT 'David_' LIKE 'David|_' ESCAPE '|'; -- 1 (自定義 | 為轉義)

注意：MySQL 字串常值以 \ 轉義，LIKE 再解析一次，故匹配字面 \ 需寫 \\\\（四個反斜線）。

大小寫敏感度控制

SELECT 'abc' LIKE 'ABC';        -- 1 (預設不區分，依排序規則)
SELECT 'abc' LIKE BINARY 'ABC'; -- 0 (強制二進位比較，區分大小寫)

也可用 COLLATE 指定排序規則：'abc' LIKE 'ABC' COLLATE utf8mb4_bin。

數值語境下的 LIKE

SELECT 10 LIKE '1%'; -- 1 (數值隱式轉字串 '10' 再匹配)

三、REGEXP / RLIKE 正則表達式：強大但需謹慎

語法

expr REGEXP pat
expr RLIKE pat   -- 同義詞，兼容 mSQL

核心特性

• 支援 POSIX 正則語法（.、*、+、?、[]、^$、|、() 等）。
• 預設不區分大小寫，除非搭配 BINARY：'a' REGEXP BINARY 'A' -- 0。
• 使用當前字符集（預設 latin1/cp1252）判定字元類別，不支援多位元組字元（如中文）。
• 同樣受 \ 雙重轉義影響：'new*\n*line' REGEXP 'new\\\\*\\\\.\\\\*line'。

常用範例

SELECT 'Monty!' REGEXP '.*';           -- 1
SELECT 'a' REGEXP '^[a-d]';            -- 1
SELECT 'new*\n*line' REGEXP 'new\\\\*.\\\\*line'; -- 1

四、STRCMP() 字串比較函數：排序邏輯的基石

回傳值

• 0：兩字串相等
• -1：第一參數 < 第二參數（依當前排序規則）
• 1：第一參數 > 第二參數

SELECT STRCMP('text', 'text2');  -- -1
SELECT STRCMP('text2', 'text');  -- 1
SELECT STRCMP('text', 'text');   -- 0

同樣遵循「二進位字串優先、排序規則決定大小寫敏感度」原則。

五、NOT LIKE / NOT REGEXP 否定形式

expr NOT LIKE pat [ESCAPE ...]   -- 等價 NOT (expr LIKE pat)
expr NOT REGEXP pat              -- 等價 NOT (expr REGEXP pat)

六、效能與最佳實踐建議

1. 避免前導通配符：LIKE '%abc' 無法使用索引，全表掃描；LIKE 'abc%' 可用索引。
2. 大量文本搜尋改用全文索引：MATCH() AGAINST() 或外部引擎。
3. REGEXP 效能較差：無法使用索引，複雜正則極慢；簡單模式優先用 LIKE。
4. 明確指定排序規則：表/欄位定義 COLLATE utf8mb4_bin 或查詢時 COLLATE，避免隱式行為差異。
5. 儲存二進位資料用 BLOB/BINARY，非文本勿用 TEXT/CHAR。