2013年1月16日星期三

Solved!! Illegal mix of collations for operation 'concat'

今天解決這個了MySQL Error,錯誤出現在Stored Routines (Stored Procedure)當中的Concat function

因為我公司那個MySQL DB Schema設計十分差,Database / Table /Column的collation都有不同。
所以原因已經說得很明,估都不用估肯定是collation的原因。

但我公司的Production環境是MySQL 5.0版本,並沒有這個錯誤出現。
而我今天下載XAMPP最新版本,是MySQL 5.5版本,把5.0的DB migrate至5.5就得知這個問題。

上網找了很久很久,十個解決方法,有九個都不外乎說在my.ini加入以下設定:
[client]
default-character-set = utf8
[mysqld]
skip-character-set-client-handshake
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
collation-server=utf8_general_ci 
#collation-server=utf8_unicode_ci
character-set-server=utf8
[mysql]
default-character-set=utf8

不知道其他人怎樣,但對我來說,都是解決不到,同樣的錯誤出現。
最後找到的方法是直接在Concat語法中設定String的Collate。
用法如下 : (省略了很多沒關係的)
DECLARE Usage DOUBLE; #Variable
DECLARE ins_query VARCHAR(500); #Dynamic SQL
#MemberID  & MemberID are [IN] Parameters.

SET ins_query = CONCAT(
     CONVERT('Select @Usage:=Count(*) from Sales Where ' USING UTF8) , 
     CONVERT(CASE MemberID WHEN '' THEN '' ELSE CONCAT('MemberId="',MemberID,'" AND ') END USING UTF8), 
     ' CompanyId = ' , CompanyID , ';');

SELECT ins_query;
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;

沒錯,就是那句USING UTF8了,把各個String都做一次設定就可以了。
如果還是不行,試試執行下列的Query,看看collation_server是不是跟其他的一致。
show variables like '%coll%'

2 則留言:

  1. 谢谢您的方法,只有这个才解决了我的问题!您是如何想到要用USING UTF8的?

    回覆刪除
    回覆
    1. 其實都是取自官方的文件。
      http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

      不過的確在Google找查了很久 ~_~

      刪除