【已解决】MySQL：执行存储过程报错（MySQL字符集和排序方式冲突）

目录

问题现象：

问题分析：

解决方法：

拓展：

1、转换条件两边的字段或值为二进制数据：

 2、转换条件两边的字段或值的字符集和排序方式：

3、修改列、表、库的字符集和排序方式

参考链接：

---

问题现象：

        今天在执行Mysql的存储过程的时候，发现了一个意料之外的报错，如下：

---

问题分析：

        起因是因为最近项目要做系统演示，需要不断造数据和删数据，但由于部分业务功能还未完善，因此删数据的操作，还需要手动去数据库删除，所以为了方便运营人员和测试人员的操作，我就写了一个脚本。

        又因为需要删除的数据并非仅仅来源于单表，有多个表的数据都需要操作，因此就涉及到事务问题，最终决定用存储过程来解决，同时也能避免操作者在连续执行多个sql执行时，因为系统卡顿、网络、工具等各种可抗力或不可抗力导致脚本执行不彻底，而影响到系统演示，毕竟是给领导汇报，打脸的事，大家都不想啊！！！

        回到正题，文章开头提到的问题到底是怎么一回事呢？        根据报错信息可知，这是由于MySQL字符集和排序方式冲突导致的报错。

        下面是完整的存储过程：

-- 删除存储过程
DROP PROCEDURE IF EXISTS deleteStaffAppRelationship;

-- 创建一个存储过程：
DELIMITER $$
CREATE PROCEDURE deleteStaffAppRelationship(IN param_phone VARCHAR(255), OUT result INT(1))
BEGIN     
-- -- 如果出现异常，抛出一个sql状态码为'23000'的异常
	DECLARE EXIT HANDLER FOR SQLSTATE '23000' set result = -1;

	-- 初始化出参值
	set result = 0;

	delete from app_sys.asys_user_binding
	where USER_ID in (
		select id 
		from app_sys.asys_user
		where phone = param_phone
	)
	and IS_DELETED = 0;

    -- 其他的sql......


    -- 设置出参值
	set result = 1;
END; $$
DELIMITER;

         经过测试发现，报错原因是在如下sql中：

	delete from app_sys.asys_user_binding
	where USER_ID in (
		select id 
		from app_sys.asys_user
		where phone = param_phone
	)
	and IS_DELETED = 0;

        更准确的说就是在这个条件语句：

where phone = param_phone

        查询数据库字符集和排序方式：

-- 查看数据库字符集
show VARIABLES like '%character%';

-- 查看数据库排序方式
show VARIABLES where Variable_name like 'collation%';

        可以看到排序方式并不是完全一致的，在调用存储过程时，传入的参数param_phone使用的排序方式是：utf8mb4_0900_ai_ci，而表字段phone使用的排序方式是：utf8mb4_0900_ai_ci，因此在做条件判断时，就会因为排序方式不同而发生冲突，导致报错：

CALL app_sys.deleteStaffAppRelationship('123',@result)
> 1267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
> 时间: 0.01s

        既然知道问题原因，那就有解决思路了：

        只要保证两边的字符集和排序方式一致即可。

解决方法：

        有很多方法（在下文的拓展章节中会说明）可以保证字符集和排序方式的一致，所以建议根据实际情况来选择；这里先给出我目前使用的方法：

        转换条件两边的字段或值为二进制数据：

        修改后的脚本如下：

-- 删除存储过程
DROP PROCEDURE IF EXISTS deleteStaffAppRelationship;

-- 创建一个存储过程：
DELIMITER $$
CREATE PROCEDURE deleteStaffAppRelationship(IN param_phone VARCHAR(255), OUT result INT(1))
BEGIN     
-- -- 如果出现异常，抛出一个sql状态码为'23000'的异常
	DECLARE EXIT HANDLER FOR SQLSTATE '23000' set result = -1;

	-- 初始化出参值
	set result = 0;

	delete from app_sys.asys_user_binding
	where USER_ID in (
		select id 
		from app_sys.asys_user
		where binary phone = binary param_phone 
	)
	and IS_DELETED = 0;

    -- 其他的sql......


    -- 设置出参值
	set result = 1;
END; $$
DELIMITER;

        执行存储过程成功：

---

拓展：

        上文提到有很多方法可以，这里就简单列举一下：

1、转换条件两边的字段或值为二进制数据：

where binary 字段或值 = binary 字段或值

-- 如：
where binary phone = binary param_phone

        转为二进制后的数据，相当于字符集和排序方式相同，可以直接比较。

        如果只是临时涉及到字符集或排序方式冲突问题时，建议使用这种方式。

 2、转换条件两边的字段或值的字符集和排序方式：

where CONVERT(字段或值 USING utf8mb4) COLLATE utf8mb4_general_ci = CONVERT(字段或值 USING utf8mb4) COLLATE utf8mb4_general_ci 

--如：
where CONVERT(phone USING utf8mb4) COLLATE utf8mb4_general_ci = CONVERT(param_phone USING utf8mb4) COLLATE utf8mb4_general_ci

        通过转换，保证字符集和排序方式的一致即可比较。

        这是最简单易懂的方式，但也是写起来最麻烦的方式，同样适用于临时涉及到字符集或排序方式冲突问题时。

3、修改列、表、库的字符集和排序方式

        可以通过数据库工具进行相关操作；也可以通过sql：

-- 列：
ALTER TABLE 表名 MODIFY 列名 列的数据类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 如：
ALTER TABLE app_sys.asys_user_binding MODIFY phone VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;




-- 表：
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 如：
ALTER TABLE app_sys.asys_user_binding CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;




-- 库：
ALTER DATABASE 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 如：
ALTER DATABASE app_sys CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

        这是一劳永逸的最根本的方式，但一般不建议直接修改这些已经定好的字符集和排序方式，除非是拥有相应的权限，否则建议和团队中的技术领导讨论，另外网上还有一个说法指出：修改后只对以后插入的数据有效，对已有数据不生效（未亲测，有所以后验证）。

---

参考链接：

mysql 1267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and_Marydon的技术博客_51CTO博客