淘宝上的网站怎么做,个人网站域名快速备案,宝格丽网站建设,建设工程规范发布网站目录
问题现象#xff1a;
问题分析#xff1a;
解决方法#xff1a;
拓展#xff1a;
1、转换条件两边的字段或值为二进制数据#xff1a; 2、转换条件两边的字段或值的字符集和排序方式#xff1a;
3、修改列、表、库的字符集和排序方式
参考链接#xff1a; 问…目录
问题现象
问题分析
解决方法
拓展
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_bindingwhere USER_ID in (select id from app_sys.asys_userwhere phone param_phone)and IS_DELETED 0;-- 其他的sql......-- 设置出参值set result 1;
END; $$
DELIMITER; 经过测试发现报错原因是在如下sql中 delete from app_sys.asys_user_bindingwhere USER_ID in (select id from app_sys.asys_userwhere 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_bindingwhere USER_ID in (select id from app_sys.asys_userwhere 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博客
