MySQL调用clean_email存储过程报非法排序规则混合错误的解决方法
问题背景
执行存储过程调用语句call clean_email('johnny@c.com');时触发报错:
Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='
已尝试修改applications表的排序规则,报错仍未消除。
报错根因
单独修改applications表的排序规则没有生效,本质是因为本次报错的冲突点并不只来自这张表,_appendix表的字段排序规则和存储过程入参的继承性排序规则,才是触发报错的核心来源。
MySQL排序规则冲突的核心逻辑是:参与等值比较的两个字符串对象,只要排序规则属性不一致(尤其是隐式字段规则和可继承的会话级规则混合时),就会直接报错。本次存储过程里的冲突点有2处:
- 第一处:
cursor_i游标查询中,_appendix表的value、key字段自带latin1_swedish_ci隐式排序规则,而存储过程入参_email_继承会话层面的utf8mb4_general_ci可继承排序规则,执行lower(value) = lower(_email_)等值判断时,两类规则不兼容触发报错。 - 第二处:
cursor_old游标查询中,对formData字段做编码转换后,没有显式指定排序规则,和入参比较时依然会出现规则属性不匹配的问题。
解决方法
无需依赖大规模修改全表排序规则,优先通过调整存储过程逻辑从根源消除冲突,步骤如下:
1. 明确存储过程入参的排序规则
把存储过程的入参定义改成显式指定统一的排序规则,避免入参继承不确定的会话级规则:
CREATE DEFINER=`root`@`%` PROCEDURE `clean_email`(_email_ varchar(128) COLLATE utf8mb4_general_ci)
2. 游标查询逻辑中显式统一比较规则
对两个游标里的等值比较逻辑,强制所有参与比较的字符串使用同一套排序规则,修改点如下:
- 改造
cursor_i的查询条件,给_appendix表的字段、硬编码的字符串值显式指定排序规则:DECLARE cursor_i CURSOR FOR (SELECT appId, null as "resumeId" FROM _appendix WHERE LOWER(`value` COLLATE utf8mb4_general_ci) = LOWER(_email_) AND LOWER(`key` COLLATE utf8mb4_general_ci) = LOWER('applicantemail' COLLATE utf8mb4_general_ci)) - 改造
cursor_old的查询条件,给formData转换后的结果显式指定排序规则:DECLARE cursor_old CURSOR FOR SELECT id FROM applications WHERE CASE WHEN formData is null OR trim(formData)="" THEN false ELSE LOWER( JSON_UNQUOTE( JSON_EXTRACT( CONVERT(formData using utf8mb4) COLLATE utf8mb4_general_ci, '$.candidateParams.ApplicantEmail' ) ) ) = LOWER(_email_) END;
3. 长期统一字段排序规则(可选)
如果排查后确认_appendix等关联表的字段是历史遗留的latin1_swedish_ci排序规则,可以在业务低峰期统一将这些字段的排序规则调整为和全局库一致的utf8mb4_general_ci,从底层减少同类冲突:
-- 示例:修改_appendix表的字段排序规则 ALTER TABLE _appendix MODIFY `value` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY `key` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
内容的提问来源于stack exchange,提问作者art
相关产品推荐
相关产品推荐

