mysql ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) ....

来源：互联网发布：韩版西装品牌知乎编辑：程序博客网时间：2024/06/05 17:32

迁移服务器时，重建mysql数据库，将数据库的编码格式改为了utf8mb4(见该篇),以便能够存储emoji表情.

在遇到一个执行procedure报错：
call TigAddUserPlainPw(‘db-properties’, NULL);

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

存储过程TigAddUserPlainPw代码：

create procedure TigAddUserPlainPw(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)begin    case TigGetDBProperty('password-encoding')        when 'MD5-PASSWORD' then            call TigAddUser(_user_id, MD5(_user_pw));        when 'MD5-USERID-PASSWORD' then            call TigAddUser(_user_id, MD5(CONCAT(_user_id, _user_pw)));        when 'MD5-USERNAME-PASSWORD' then            call TigAddUser(_user_id, MD5(CONCAT(substring_index(_user_id, '@', 1), _user_pw)));        else            call TigAddUser(_user_id, _user_pw);        end case;end

以上建表语句中 ‘MD5-USERID-PASSWORD’ 等变量的默认编码方式需要改变，修改TigAddUserPlainPw代码：

create procedure TigAddUserPlainPw(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)  begin    case TigGetDBProperty('password-encoding')      when CONVERT('MD5-PASSWORD' USING utf8mb4) COLLATE utf8mb4_unicode_ci then      call TigAddUser(_user_id, MD5(_user_pw));      when CONVERT('MD5-USERID-PASSWORD' USING utf8mb4) COLLATE utf8mb4_unicode_ci then      call TigAddUser(_user_id, MD5(CONCAT(_user_id, _user_pw)));      when CONVERT('MD5-USERNAME-PASSWORD' USING utf8mb4) COLLATE utf8mb4_unicode_ci then      call TigAddUser(_user_id, MD5(CONCAT(substring_index(_user_id, '@', 1), _user_pw)));    else      call TigAddUser(_user_id, _user_pw);    end case;  end

删除存储过程TigAddUserPlainPw，重建该存储过程。

阅读全文

0 0