作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
当我运行以下程序时
call clean_email('johnny@c.com');
我收到错误
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
最佳答案
做 SHOW CREATE PROCEDURE clean_email;
但请查看代码正文以外的额外列。其中一个说 CHARACTER SET
创建过程时使用。大概是 latin1
.
为了改变这一点,
SET NAMES utf8mb4; -- assuming this is desired
DROP PROCEDURE clean_emaill;
and recreate the procedure
关于mysql - 操作 '=' 的排序规则 (utf8mb4_general_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT) 的非法混合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/66005624/
我是一名优秀的程序员,十分优秀!