| 函數 | 語法 | 作用 |
REPLACE | REPLACE(str, from_str, to_str) | 將字串中所有 from_str 替換為 to_str |
TRIM | TRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str) | 移除首尾指定字元(預設空格) |
TRIM 變體示範
SELECT TRIM(' phpernote '); -- 'phpernote' (首尾空格)
SELECT TRIM(LEADING 'x' FROM 'xxxphpernotexxx'); -- 'phpernotexxx' (僅前導 x)
SELECT TRIM(BOTH 'x' FROM 'xxxphpernotexxx'); -- 'phpernote' (首尾 x)
SELECT TRIM(TRAILING 'xyz' FROM 'phpernotexxyz'); -- 'phpernotex' (尾部 xyz)
實戰場景:標題欄位含不確定空格
表 news 欄位 title 存有 '李楊 技術 博客'、' 李楊技術博客 ' 等髒資料。查詢 '李楊技術博客' 需忽略所有空格。
錯誤寫法
SELECT * FROM news WHERE title = '李楊技術博客'; -- 失敗
SELECT * FROM news WHERE title LIKE '%李楊技術博客%'; -- 失敗
正確寫法:雙邊淨化
-- 精確比對
SELECT * FROM news
WHERE TRIM(REPLACE(title, ' ', '')) = TRIM(REPLACE('李楊技術博客', ' ', ''));
-- 模糊比對
SELECT * FROM news
WHERE TRIM(REPLACE(title, ' ', '')) LIKE TRIM(REPLACE('%李楊 技術 博客%', ' ', ''));
REPLACE(col, ' ', ''):移除欄位值所有內部空格。
TRIM(...):再移除可能殘留的首尾空格(雙重保險)。
- 查詢參數同樣處理,確保比對基準一致。
效能與替代方案
- 索引失效: 函數作用於欄位導致無法使用索引,大表全掃描。
- 應用層預處理: 寫入前
trim() + str_replace(' ', ''),查詢前同理處理參數,資料庫保持乾淨、索引有效。
- 產生欄/虛擬欄: MySQL 5.7+ 支援
GENERATED ALWAYS AS (TRIM(REPLACE(title,' ',''))) VIRTUAL,可對虛擬欄建索引。
FULLTEXT 索引:若需自然語言搜尋,考慮 ngram 分詞器(中文支援)。
總結
TRIM(REPLACE(col,' ','')) 是臨時查詢清洗髒資料的瑞士刀;長期治本靠「入庫前清洗」+「虛擬欄索引」,兼顧查詢效能與資料品質。
copyright ©
萬盛學電腦網 all rights reserved