問題場景

數據庫字段值含不可見空格（如 '張三 '、' 李四'、'王 五'），導致 WHERE name = '張三' 查無結果，LIKE '%張三%' 也匹配不上。

核心函數對比

函數	語法	作用	適用場景
TRIM()	TRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str)	去除首尾指定字符（默認空格）	清理頭尾空格/換行/制表符
REPLACE()	REPLACE(str, from_str, to_str)	替換所有出現的子串	清理中間空格、特定字符
LTRIM()	LTRIM(str)	去除左側空格	左側清理
RTRIM()	RTRIM(str)	去除右側空格	右側清理

TRIM 示例

SELECT TRIM('  phpernote  ');           -- 'phpernote'
SELECT TRIM(LEADING 'x' FROM 'xxxabc'); -- 'abc'
SELECT TRIM(BOTH 'x' FROM 'xxxabcxxx'); -- 'abc'
SELECT TRIM(TRAILING 'xyz' FROM 'abcxyz'); -- 'abc'

查詢匹配策略：雙邊清理

-- 表數據：title = '李楊 技術博客' (中間有空格)
-- 查詢輸入：'李楊技術博客' (無空格)

-- 錯誤：直接比較
SELECT * FROM `table` WHERE title = '李楊技術博客';  -- 0 行

-- 正確：雙邊 REPLACE 移除所有空格後比較
SELECT * FROM `table` 
WHERE REPLACE(TRIM(title), ' ', '') = REPLACE(TRIM('李楊技術博客'), ' ', '');

-- LIKE 模糊匹配同理
SELECT * FROM `table` 
WHERE REPLACE(TRIM(title), ' ', '') LIKE CONCAT('%', REPLACE(TRIM('李楊 技術博客'), ' ', ''), '%');

⚠️ 致命陷阱：函數導致索引失效

-- 即使 title 有索引，以下查詢**全表掃描**
SELECT * FROM `table` WHERE REPLACE(TRIM(title), ' ', '') = '李楊技術博客';
-- 原因：索引存儲原始值，函數變換後無法利用索引查找

解決方案分層

方案一：寫入端規範（根治）

• 應用層 trim() + 正則替換多空格為單空格/去空格。
• 數據庫層 BEFORE INSERT/UPDATE TRIGGER 強制清理：

CREATE TRIGGER `trg_clean_title` BEFORE INSERT ON `table`
FOR EACH ROW
SET NEW.title = REPLACE(TRIM(NEW.title), ' ', '');

方案二：冗余乾淨列 + 索引（查詢快、寫入微增）

ALTER TABLE `table` ADD COLUMN `title_clean` VARCHAR(200) NOT NULL DEFAULT '';
CREATE INDEX `idx_title_clean` ON `table` (`title_clean`);

-- 觸發器維護
CREATE TRIGGER `trg_title_clean` BEFORE INSERT ON `table`
FOR EACH ROW
SET NEW.title_clean = REPLACE(TRIM(NEW.title), ' ', '');

CREATE TRIGGER `trg_title_clean_upd` BEFORE UPDATE ON `table`
FOR EACH ROW
SET NEW.title_clean = REPLACE(TRIM(NEW.title), ' ', '');

-- 查詢極快，命中索引
SELECT * FROM `table` WHERE title_clean = REPLACE(TRIM('李楊技術博客'), ' ', '');

方案三：全文索引（模糊匹配、自然語言）

ALTER TABLE `table` ADD FULLTEXT INDEX `ft_title` (`title`);
SELECT * FROM `table` WHERE MATCH(title) AGAINST('+李楊 +技術 +博客' IN BOOLEAN MODE);

不敏感於空格，但需 MyISAM/InnoDB 5.6+，中文需配置 ngram parser。

方案四：應用層預處理（無改表權限時）

// PHP 示例
$clean_input = preg_replace('/\s+/', '', trim($input));
$sql = "SELECT * FROM `table` WHERE REPLACE(TRIM(title), ' ', '') = ?";
// 仍走全表掃描，但邏輯正確；配合 LIMIT、緩存緩解

性能對比實測（100 萬行，title 有索引）

方案	查詢時間	索引使用	維護成本
直接函數查詢	~2.5 秒	❌ 全表掃	零
冗余列 + 索引	~0.001 秒	✅ ref	低(觸發器)
全文索引	~0.05 秒	✅ fulltext	中(分詞配置)
應用層清理 + 精確匹配	~0.001 秒	✅ ref (原 title 索引)	零(寫入時處理)

總結建議

1. 源頭治理：入庫前徹底清理，建立唯一約束防髒數據回流。
2. 冗余列：存量數據已髒、無法全量清洗時，加乾淨列 + 索引 + 觸發器維護，查詢極速。
3. 拒絕 SELECT 時做 REPLACE/TRIM：除非極小表或極低頻查詢，否則生產環境嚴禁。