SQL LIKE 模糊查詢實戰：數字類型字段前綴匹配與多條件組合

SQL LIKE 模糊查詢實戰：數字類型字段前綴匹配與多條件組合

LIKE 操作符是 SQL 模糊匹配的核心，配合通配符 %（任意長度字符）和 _（單個字符）可實現前綴、後綴、包含、定長模式查詢。本文聚焦數字類型字段的隱式轉換陷阱、索引利用條件與多條件組合寫法。

一、基礎語法

-- 前綴匹配（最常用，可用索引）
SELECT * FROM users WHERE phone LIKE '138%';

-- 後綴匹配（不可用索引）
SELECT * FROM users WHERE phone LIKE '%1234';

-- 包含匹配（不可用索引）
SELECT * FROM users WHERE realname LIKE '%李%';

-- 定長單字符匹配
SELECT * FROM users WHERE id_card LIKE '110101____________3X';  -- 18位身份證

二、數字類型字段的隱式轉換陷阱

當字段類型為數值型（INT、BIGINT、DECIMAL），LIKE '123%' 會觸發隱式類型轉換：MySQL 將數值轉為字符串再匹配，導致無法使用索引，全表掃描。

錯誤示例

-- card_id 為 BIGINT
SELECT * FROM users WHERE card_id LIKE '4%';  -- 全表掃描！

正確寫法

-- 1. 顯式轉字符串（MySQL 8.0+ 支持函數索引可優化）
SELECT * FROM users WHERE CAST(card_id AS CHAR) LIKE '4%';

-- 2. 數值範圍代替（最優，利用索引）
-- 4 開頭的 18 位身份證：400000000000000000 到 499999999999999999
SELECT * FROM users WHERE card_id BETWEEN 400000000000000000 AND 499999999999999999;

-- 3. 應用層轉字符串存儲（如需頻繁前綴查詢，冗余字符串列 + 索引）

三、多條件組合查詢

-- AND 組合：姓名包含"李" 且 身份證以"4"開頭
SELECT * FROM users 
WHERE realname LIKE '%李%' 
  AND card_id BETWEEN 400000000000000000 AND 499999999999999999;

-- OR 組合：姓名包含"李" 或 "張"
SELECT * FROM users 
WHERE realname LIKE '%李%' OR realname LIKE '%張%';

-- 複雜邏輯：(姓名含"李" AND 身份證4開頭) OR (姓名含"王" AND 手機138開頭)
SELECT * FROM users 
WHERE (realname LIKE '%李%' AND card_id BETWEEN 400000000000000000 AND 499999999999999999)
   OR (realname LIKE '%王%' AND phone LIKE '138%');

四、性能優化要點

1. 避免前導 %：LIKE '%abc'、LIKE '%abc%' 無法用索引，全表掃描。
2. 前綴索引：長字符串列建 INDEX idx_name_prefix (name(10))，LIKE 'abc%' 可用前綴索引。
3. 覆蓋索引：SELECT id, name FROM t WHERE name LIKE 'abc%' 若 INDEX (name, id) 可覆蓋，避免回表。
4. 全文索引：自然語言搜索用 FULLTEXT INDEX + MATCH() AGAINST()，替代 LIKE '%word%'。
5. 數值字段：堅決不用 LIKE，改用範圍查詢 BETWEEN/>= <=。

五、不同數據庫 LIKE 語法差異

六、總結

LIKE 是模糊查詢利器，但數值字段禁用 LIKE、避免前導 %、善用範圍查詢替代、前綴索引/覆蓋索引/全文索引 是性能優化三板斧。多條件組合時注意括號優先級，EXPLAIN 驗證執行計劃，確保命中預期索引。