MySQL與中文搜尋

今天處理一個MySQL下某些中文搜尋會凸鎚的問題,愈弄愈是一肚子火。

基本上,在搜尋字串時,最常用到的語法是 WHERE col_name LIKE ‘%str%’,這種方法可以很方便地用關鍵字的方式找出自己想要的資料,而且具有ignore case的功能,無論使用者輸入的是大小寫,MySQL都能找到資料。問題就出在這裡。

MySQL比較字串的方式是將要比較的字元及資料一律轉為大寫來比較,也就是不管你輸入pda/pDA/Pda/PDA,MySQL都會轉成PDA,同樣的,欄位中的資料也會被轉成大寫,然後再比較(manual上有寫較新的版本較有智慧,不過我試的結果對於這個問題還是沒有幫助),如果輸入的字串或是欄位中資料的字串有中文的話,就好玩了。MySQL會將資料全部大寫,中文部份的ASCII碼好像也是(我不太清楚是怎麼處理的),
結果就會出現下面的case:

輸入 author LIKE ‘%DAS’ ,結果找出欄位裡有 “房龍 F“的資料。

沒辦法,manual中也說了:

Note: Currently LIKE is not multi-byte character safe.

怎麼解決呢? 查了google,有一說法是改用strcmp,捨棄 LIKE,可以分辨大小寫的不同,而找出真正含有”DAS“字串的資料,更甚者,可以如manual中的建議,把欄位的屬性設為Binary:

If you are using Chinese data in the so-called big5 encoding, you want to make all character columns BINARY. This works because the sorting order of big5 encoding characters is based on the order of ASCII codes.

實際上試驗的結果的確是沒錯的,果然不會誤判找到像這種字了。不過卻又引出了一個新的問題:

當我們指定了文字的大小寫,我們也失去了輸入”pda“找到”PDA“的便利性! 而這是非常吃虧的……解決辦法也得另外再尋找。

那麼,把mysql server在compile時加上 WITH_CHARSET=big5 又如何呢?試驗的結果,server連開都開不起來,可能是因為資料庫本身已經是Latin1了,那這意味著如果要全server改成big5,得要所有的資料都dump出來,重新灌好再import進去,這工程耗大,而且對於已經對中文有escape的程式而言,還要再動一次大修改,實為萬不得已的下下之策!!

再來呢,試試看regular expression,使用REGEXP這個語法,如 WHERE author REGEXP ‘.*das.*’ ,總算可以做到濾掉不相關的中文,然後又能兼顧到英文的大小寫,只是速度會變慢,而且也不是(應該不是)標準的SQL語法(ANSI SQL)。

不過,問題不只一個,解決了輸入英文找到不相關中文的問題,不過還是引出了新的問題。

試著搜尋 WHERE name REGEXP ‘.*會計.*’,結果好像找到的是只有「計」這個字的資料,原因是”“這個字的ASCII code是B7 7C,其中7C是”|“這個字元,而在regular expression中”|”這個字是保留作”OR”運算用的,得用”” escape掉….看來要做的事還不少….zzz

想不到如何在server端,只好在client端的程式處理了。

把”會計”的會字的第二個byte加上””,變成”愧|計“,然後餵看看…hmm..沒用

再來呢加上個””,再把””用””escape掉,變成WHERE name
REGEXP ‘.*愧\|計.*’
hmm,總算在PHPmyAdmin中可以了,可是在程式中是要加幾個””呢,這就得try and error了。

除了”|”之外,還要一堆相關的保留字也要一起escape掉,唉….不知有沒有中文(multi-byte) friendly的REGEXP呢?

escape的字元,不過還沒確定。即使是使用REGEXP也是有這樣的一個問題….怎麼解決呢?

根據manual的說法,目前MySQL還沒有。
不過LIKE不支援Multibyte的問題「將來」會解決。

目前而言,使用LIKE會碰到大小寫的問題,
使用REGEXP的話要想辦法避開一堆regular expression的保留字,
怎麼取捨? 就看自己了。

附註:其實使用LIKE也是有保留字要escape的,就是”%”跟”_”這兩個字。

在〈MySQL與中文搜尋〉中有 3 則留言

  1. 我比你还要麻烦

    我是ASP+MYSQL

    比如上面这个”粹”字,是绝对输入不了的

    一输就出错

    你可以去我的网站看看

    好象是ASP对UTF-8的支持不好

    好象MSN.COM就是UTF-8的也不行的

    也不知道是怎么回事

    好象”綷”字被搞成了\

    而\在MYSQL要解释的

    回覆

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料