SQL 模糊查詢：兼容下劃線通配符與普通模糊查詢的動態 SQL 寫法

需求：同一查詢需同時支持「包含下劃線的精確匹配」和「普通模糊匹配」。下劃線 `_` 在 `LIKE` 中是單字符通配符，需轉義；但若用戶輸入不含下劃線，應退化為普通 `%keyword%`。

問題分析

-- 僅匹配含下劃線的
SELECT * FROM table_name t WHERE t.name LIKE '%\_%' ESCAPE '\'
-- 無法匹配不含下劃線的關鍵詞

解決方案：動態 SQL 判斷構建

方案 1：應用層判斷（推薦）

-- 偽代碼
IF (keyword LIKE '%_%') THEN  -- 含下劃線
  sql = "SELECT * FROM t WHERE name LIKE ? ESCAPE '\'";
  param = keyword.replace("_", "\_").replace("%", "\%");  -- 轉義
ELSE
  sql = "SELECT * FROM t WHERE name LIKE ?";
  param = "%" + keyword + "%";
END IF;
EXECUTE sql USING param;

方案 2：存儲過程/動態 SQL（Oracle/PLSQL 風格）

IF flag THEN
  V_PRO := 'SELECT * FROM table_name t WHERE t.name LIKE ''%\_%'' ESCAPE ''\''';
ELSE
  V_PRO := 'SELECT * FROM table_name t WHERE t.name LIKE ''%' || keyword || '%''';
END IF;
EXECUTE IMMEDIATE V_PRO;

方案 3：單條 SQL 用 CASE/REGEXP（MySQL 8.0+ / PostgreSQL）

SELECT * FROM table_name
WHERE 
  (keyword LIKE '%\_%' ESCAPE '\' AND name LIKE keyword ESCAPE '\')
  OR (keyword NOT LIKE '%\_%' ESCAPE '\' AND name LIKE CONCAT('%', keyword, '%'));
-- 注意：keyword 為參數，需應用層注入或預處理

最佳實踐建議

1. 應用層組裝 SQL：語言層面判斷關鍵詞特徵，構建不同參數化查詢，安全且性能好（利用緩存計劃）。
2. 統一轉義函數：封裝 `escapeLikeWildcards(str)` 處理 `%`、`_`、`\`。
3. 避免拼接 SQL 字符串：防注入，必須用參數化查詢（PreparedStatement）。

總結

單條靜態 SQL 難以同時優雅處理「轉義下劃線」與「普通模糊」。推薦應用層根據輸入特徵動態選擇查詢模式並參數化執行。