以下是 模糊查詢 的深度技術解析，涵蓋核心語法、通配符策略、性能優化及實戰陷阱：

---

🔍 一、核心運算符：LIKE

SELECT * FROM 表名 
WHERE 列名 LIKE '模式字符串';

---

🎯 二、通配符詳解

通配符	作用	示例	匹配案例
%	任意長度字符（含0字符）	'張%'	張三、張無忌、張
_	單個字符	'李_'	李四、李雷（不匹配"李"）
[ ]	字符集合內單字符 (僅SQL Server)	'[王李]%'	王五、李四
[^ ]	排除字符集合 (僅SQL Server)	'[^0-9]%'	中文、字母（排除數字開頭）

📌 通用標準（MySQL/PgSQL/Oracle）：

-- 匹配 "公司"結尾的字符串
WHERE company LIKE '%公司'  -- 匹配第二個字為"小"的姓名
WHERE name LIKE '_小%'     -- 匹配含下劃線的文件名 (需轉義)
WHERE file_name LIKE '%\_%' ESCAPE '\'  

---

?? 三、關鍵注意事項

1. 大小寫敏感性問題

數據庫	默認行為	強制不敏感方案
MySQL	取決于校對規則	WHERE LOWER(name) LIKE '%abc%'
PostgreSQL	區分大小寫	用 ILIKE 運算符
Oracle	區分大小寫	WHERE UPPER(name) LIKE '%ABC%'

2. 通配符轉義（通用方案）

-- 查找含 "20%" 的備注（如"折扣20%"）
SELECT * FROM products 
WHERE notes LIKE '%20\%%' ESCAPE '\';  -- 定義'\'為轉義符-- 查找含下劃線的文件名
WHERE file_name LIKE '%\_%' ESCAPE '\';

3. NULL 值處理

-- 模糊查詢不匹配 NULL 值！
SELECT * FROM users 
WHERE name LIKE '%張%';  -- 自動過濾 name IS NULL 的行

---

? 四、性能優化策略

1. 避免全模糊（%xxx%）

-- ? 性能殺手（無法利用索引）
WHERE content LIKE '%數據庫%'  -- ? 優化方案1：右模糊（可命中索引）
WHERE content LIKE '數據庫%'  -- ? 優化方案2：全文索引（MySQL 5.6+）
ALTER TABLE articles ADD FULLTEXT INDEX (content);
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('數據庫' IN BOOLEAN MODE);

2. 慎用函數包裹列

-- ? 索引失效
WHERE LOWER(name) LIKE '%abc%'  -- ? 預處理數據
ALTER TABLE users ADD COLUMN name_lower VARCHAR(50);
UPDATE users SET name_lower = LOWER(name);  
CREATE INDEX idx_name_lower ON users(name_lower);
WHERE name_lower LIKE '%abc%';  -- 仍全掃，但比函數快

3. 使用覆蓋索引

-- 只需返回 name 和 id
SELECT name, id FROM users 
WHERE name LIKE '張%';  -- 若索引含(name,id)則無需回表

---

🔧 五、進階技巧

1. 正則表達式增強（REGEXP）

-- MySQL/PgSQL 支持
WHERE name REGEXP '^張[小大]'  -- 匹配"張小"或"張大"開頭-- Oracle 用 REGEXP_LIKE
WHERE REGEXP_LIKE(name, '^張[小大]')

2. 通配符組合妙用

-- 匹配 130-139 開頭的手機號
WHERE phone LIKE '13_%'        -- 簡易版（可能包含無效號）
WHERE phone REGEXP '^13[0-9]{9}$'  -- 精確版（11位數字）-- 匹配郵箱格式
WHERE email LIKE '%@%.%'       -- 基礎驗證

3. 動態模式生成

-- 根據變量構造模式
SET @search_term = '數據';
SELECT * FROM books 
WHERE title LIKE CONCAT('%', @search_term, '%');

---

🧩 六、實戰陷阱案例

陷阱1：中文字符截斷錯誤

-- UTF-8 下 '李_' 可能匹配到 "李三"（3字節）或 "李𠮷"（4字節）
? 解決方案：使用 `CHAR_LENGTH()` 輔助
WHERE name LIKE '李_' AND CHAR_LENGTH(name) = 2

陷阱2：通配符沖突

-- 用戶輸入含 "%" 時（如搜索 "100%"）
? 方案：預處理輸入值
SET @input = REPLACE(@raw_input, '%', '\%');
WHERE content LIKE CONCAT('%', @input, '%') ESCAPE '\';

陷阱3：隱式空格問題

-- VARCHAR 存儲時尾部空格參與匹配
CREATE TABLE test (col VARCHAR(10));
INSERT INTO test VALUES ('abc'), ('abc   ');SELECT * FROM test WHERE col LIKE 'abc';   -- 只匹配 'abc'
SELECT * FROM test WHERE col LIKE 'abc%';  -- 匹配兩項

---

📊 七、模糊查詢類型性能對比

查詢類型	示例	索引利用	適用場景
前綴匹配	LIKE '張%'	? 可利用B樹索引	快速人名/編號檢索
后綴匹配	LIKE '%公司'	? 全表掃描	低頻分析
全模糊	LIKE '%數據%'	? 全表掃描	小表或全文索引替代方案
固定位置	LIKE '__小%'	? 全表掃描	嚴格格式數據（如身份證）

---

💡 終極建議

1. 數據清洗：入庫前規范化（統一大小寫/去除特殊字符）
2. 索引策略：對高頻前綴查詢列建索引
3. 替代方案：
4. 安全防護：過濾用戶輸入的通配符（防止惡意超長%拖垮數據庫）