解決簡短英文單字的 MySQL 查詢：搜尋 app 而不是 apple

MySQL 資訊庫查詢文章內文，常會使用 LIKE 函數的「LIKE '%關鍵字%' 」語法。但是在搜尋簡短英文單字或專有名詞的縮寫，卻會遇到問題，例如明明要找的是 app 卻會連 apple 也會在查詢結果。解決方法可以使用「單字邊界符號」 (word boundary) 加上文字編碼轉換，處理中英文夾雜的原始文章資料。

Photo by Sticker Mule from StockSnap

問題狀況

市面教科書全文搜尋的查詢語法「LIKE '%關鍵字%' 」(線上試驗 SQL Fiddle)

SELECT * FROM `articles` WHERE `content` LIKE '%ESL%'

關鍵字 ESL 查詢結果除了預期包含 ESL 單字的文章，還會包含 eslite 等其他不相關的單字，只因為 eslite 包含了 esl 。

解決方法第 1 部分：使用單字邊界符號 (word boundary)

在 Google 試算表遇過類似問題：解決英文字的搜尋：搜尋 app 而不是 apple 【Google 試算表】，所以一開始想使用「單字邊界符號」 (word boundary) 來處理。

SELECT 'a word esl' REGEXP '[[:<:]]ESL[[:>:]]'; -> 結果: 1 符合預期
SELECT 'a wood eslite' REGEXP '[[:<:]]ESL[[:>:]]'; -> 結果: 0 符合預期

SELECT '以英語為第二語言（ESL）' REGEXP '[[:<:]]ESL[[:>:]]'; -> 結果: 0 不符合預期

SELECT '以英語為第二語言（ ESL ）' REGEXP '[[:<:]]ESL[[:>:]]'; -> 結果: 1 符合預期

如果英文單字前後是空白或換行符號，MySQL 查詢結果才會符合預期。但是真實資料並不會這麼美好。常常會英文單字前後緊接著標點符號或中文字。查看 Oracle 文件：

[[:<:]], [[:>:]]
These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

「單字邊界符號」可以找出的完整英文單字可以在 (1) 句子開始處、(2) 句子結尾處、(3) 前或後是空白、(4) 前或後不是文字符號 (word character)：指不是數字、英文字 (Alphanumeric characters)、底線符號 _ 。

解決方法第 2 部分：將文章編碼移除非 ASCII 文字

將文章欄位轉成 ASCII 達到移除中文字的效果，可使用以下 SQL 語法：

CONVERT(`文章欄位` USING ascii)
或 REPLACE(CONVERT(`文章欄位` USING ascii), '?', ' ')

中文字轉換成 ASCII 編碼會變成問號，所以需要將問號取代為空白。

最終解決方法使用單字邊界符號 (word boundary) + 移除中文字

將兩個嘗試方法整合，語法：(線上試驗 SQL Fiddle)

SELECT * FROM `articles`
WHERE CONVERT(`content` USING ascii) REGEXP '[[:<:]]ESL[[:>:]]';

查詢結果變得乾淨也符合預期

參考資料

準備測試資料

取自以英語為第二語言或外語 - 維基百科，自由的百科全書

CREATE TABLE `articles`
(
id int auto_increment primary key,
content varchar(500)
);

INSERT INTO `articles`
(`content`)
VALUES
('This is a test'),
('ESL is English as a Second Language'),
('誠品書店eslite bookstore.'),
('https://www.eslite.com/'),
('以英語為第二語言（English as a Second Language；ESL）、以英語外語（English as a Foreign Language；EFL）、以英語為附加語言（English as an Additional Language；EAL）、或操其他語言者英語皆指（English for Speakers of Other Languages；ESOL）皆指英語為其他非母語人士使用的情形或研究。'),
('以英語為外語（EFL）指的是在非英語系地區使用英語的情況，在學習者母國的正規學校課程的體制下，或是因遊學等原因短暫造訪英語系國家的情況下發生。'),
('以英語為外語（EFL
）指的是在非英語系地區使用英語的情況，在學習者母國的正規學校課程的體制下，或是因遊學等原因短暫造訪英語系國家的情況下發生。'),
('Since 1995 Dave\'s ESL Cafe has been a meeting place for ESL/EFL students & teachers from around the world - jobs, a resume database, forums, resources, ...');

Err

搜尋此網誌

解決簡短英文單字的 MySQL 查詢：搜尋 app 而不是 apple