mysql - 操作 '=' 的排序规则 (utf8mb4_general_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT) 的非法混合

Question

当我运行以下程序时

call clean_email('johnny@c.com');

我收到错误
操作 '=' 的排序规则 (utf8mb4_general_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT) 的非法混合
我尝试更改表应用程序的排序规则，但仍然无法解决错误。
我能做些什么来解决这个问题？我需要改变程序吗？

CREATE DEFINER=`root`@`%` PROCEDURE `clean_email`(_email_ varchar(128))
this_proc : BEGIN
    declare _appId                bigint unsigned;
    /*TEMP*/  declare _resumeId    bigint unsigned;
    DECLARE done INT DEFAULT FALSE;

 

    DECLARE cursor_i CURSOR FOR 
        /*TEMP*/  (SELECT appId, null as "resumeId" FROM _appendix WHERE lower(`value`) = lower(_email_) AND lower(`key`)="applicantemail")
        -- LATER -- andOr /*TEMP*/  UNION
        -- LATER -- (SELECT id 
        -- LATER -- andOr /*TEMP*/, resumeId
        -- LATER -- FROM cbax_application WHERE lower(`name`)  =  lower(_email_))
        ;
    /*TEMP*/ DECLARE cursor_old CURSOR FOR select id from applications where 
        /*TEMP*/ CASE WHEN formData is null OR trim(formData)="" THEN false ELSE 
        /*TEMP*/ lower(JSON_UNQUOTE(JSON_EXTRACT(CONVERT(formData using utf8mb4),'$.candidateParams.ApplicantEmail'))) = lower(_email_) END;
     
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        ROLLBACK;
        select "Failed" as "status", @p1 as "sql_state", @p2 as "message_text";
    END;
    
    -- ---------------------
    -- Input Validations --
    IF(_email_ IS NULL or instr(_email_,"@") < 2) THEN
        select "Failed" as "status", 0 as "sql_state", "Email id is required." as "message_text";
        LEAVE this_proc;
    END IF;
    -- END Input Validations --
    -- ------------------------
    
    START TRANSACTION;
        OPEN cursor_i;
        read_loop: LOOP
            FETCH cursor_i INTO _appId
            /*TEMP*/, _resumeId
            ;
            IF done THEN
                LEAVE read_loop;
            END IF;
    
            DELETE FROM cbax_application WHERE id = _appId;
            DELETE FROM cbax_application_blob WHERE appId = _appId;
            DELETE FROM job_info WHERE appId = _appId;
            DELETE FROM `resume` WHERE id = _resumeId;
            -- LATER -- DELETE FROM _values WHERE appId = _appId;
            -- LATER -- DELETE FROM _letter WHERE appId = _appId;
            -- LATER -- DELETE FROM _history WHERE appId = _appId;
            
            /*TEMP*/ DELETE FROM _appendix WHERE appId = _appId;
        END LOOP;
        CLOSE cursor_i;
        
        /*START TEMP*/
            SET done = FALSE;
            OPEN cursor_old;
            old_loop: LOOP
                FETCH cursor_old INTO _appId;
                IF done THEN
                    LEAVE old_loop;
                END IF;
        
                DELETE FROM applications WHERE id = _appId;
                DELETE FROM _values WHERE appId = _appId;
                DELETE FROM_letter WHERE appId = _appId;
                DELETE FROM _history WHERE appId = _appId;
            END LOOP;
            CLOSE cursor_old;
        /*END TEMP*/
    
        select "Success" as "status";
    COMMIT;
END

Answer 1

做 SHOW CREATE PROCEDURE clean_email;但请查看代码正文以外的额外列。其中一个说 CHARACTER SET创建过程时使用。大概是 latin1 .
为了改变这一点，

SET NAMES utf8mb4;   -- assuming this is desired
DROP PROCEDURE clean_emaill;
and recreate the procedure