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的，就是”%”跟”_”這兩個字。

你也許會喜歡

DVDRip TNG 解決Windows2000 DVD Region次數的問題 DVDRip HowTo DVDRip TNG supplemental