解決簡短英文單字的 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, ...');


留言