mysql ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) ...._sql 错误 [1267] [hy000]: illegal mix of collations (

迁移服务器时，重建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 '='
1

存储过程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 
1
2
3
4
5
6
7
8
9
10
11
12
13

以上建表语句中 ‘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 
1
2
3
4
5
6
7
8
9
10
11
12
13

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