萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查詢含空格欄位值:REPLACE 與 TRIM 組合技實戰

資料髒污導致字串比對失效是常見痛點:資料庫欄位值含前後/內部空格,或查詢參數帶空格,直接 = 或 LIKE 皆無法命中。本文示範 REPLACE() + TRIM() 組合淨化策略。

核心函數速查

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
函數語法作用
REPLACEREPLACE(str, from_str, to_str)將字串中所有 from_str 替換為 to_str
TRIMTRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str)移除首尾指定字元(預設空格)