【数据库】1267 - Illegal mix of collations (A,IMPLICIT) and (B,COERCIBLE) for operation ‘like‘

最新推荐文章于 2025-02-26 02:08:29 发布

原创最新推荐文章于 2025-02-26 02:08:29 发布 · 151 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#1267 #Illegal #mysql #utf8mb4 #gbk #CONVERT #USING

数据库专栏收录该内容

65 篇文章

订阅专栏

文章讲述了在MySQL中遇到的关于字符集不兼容错误1267，涉及到`medicine`表和`v_medicine`视图的`LIKE`操作。给出了两种解决方案：一是将`LIKE`操作中的字段编码改为`utf8mb4_general_ci`，二是将`LIKE`字符串转换为`gbk_chinese_ci`字符集。

背景描述

问题背景

medicine 表

CREATE TABLE `medicine` (
`med_hint`  varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '医嘱信息' ,
PRIMARY KEY (`med_id`),
INDEX `med_id` (`med_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci
AUTO_INCREMENT=5609
ROW_FORMAT=COMPACT
;

v_medicine 视图

SELECT medicine.*,CONVERT('自定义A' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%A%'UNION ALL
SELECT medicine.*,CONVERT('自定义B' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%B%'UNION ALL
SELECT medicine.*,CONVERT('毒' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%毒%' UNION ALL
SELECT medicine.*,CONVERT('麻/精一' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%麻/精一%' UNION ALL
SELECT medicine.*,CONVERT('精二' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%精二%' UNION ALL
SELECT medicine.*,CONVERT('冰箱' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%冰箱%'UNION ALL
SELECT medicine.*,CONVERT('虚拟' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%虚拟%'UNION ALL
SELECT medicine.*,CONVERT('非' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%非%' UNION ALL
SELECT medicine.*,CONVERT('处' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%处%' UNION ALL
SELECT medicine.*,CONVERT('贵重' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%贵重%' UNION ALL
SELECT medicine.*,CONVERT('未定义' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE '%未定义%'

问题

在保存，或者还原备份库的时候报如下错误
在这里插入图片描述

1267 - Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (uft8mb4_general_ci,COERCIBLE) for operation 'like'

上述错误表达的意思是 like操作符中混合了gbk_chinese_ci与uft8mb4_general_ci两种字符集，无法进行like操作，like操作完整表达式是 {LikeColumnName} LIKE {LikeString}，因此有两个解方案：

将{LikeColumnName} 改为 uft8mb4_general_ci字符集
将{LikeString}改为 gbk_chinese_ci字符集

解决方案

一、将`{LikeColumnName}`改为 `uft8mb4_general_ci`字符集

①使用数据库管理工具直接修改指定字段的编码
在这里插入图片描述

②使用 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci 语法实现在表中对指定的字段进行转码。
medicine表

CREATE TABLE `medicine` (
`med_hint`  varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '医嘱信息' ,
PRIMARY KEY (`med_id`),
INDEX `med_id` (`med_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci
AUTO_INCREMENT=5609
ROW_FORMAT=COMPACT
;

二、将`{LikeString}`改为 `gbk_chinese_ci`字符集

v_medicine视图
使用CONVERT('{LikeString}' USING gbk ) COLLATE gbk_chinese_ci 语法实现在视图中对指定的字符串进行转码。

SELECT medicine.*,CONVERT('自定义A' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%A%' USING gbk ) COLLATE gbk_chinese_ci) UNION ALL
SELECT medicine.*,CONVERT('自定义B' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%B%' USING gbk ) COLLATE gbk_chinese_ci) UNION ALL
SELECT medicine.*,CONVERT('毒' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%毒%' USING gbk ) COLLATE gbk_chinese_ci)  UNION ALL
SELECT medicine.*,CONVERT('麻/精一' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%麻/精一%' USING gbk ) COLLATE gbk_chinese_ci)  UNION ALL
SELECT medicine.*,CONVERT('精二' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%精二%' USING gbk ) COLLATE gbk_chinese_ci)  UNION ALL
SELECT medicine.*,CONVERT('冰箱' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%冰箱%' USING gbk ) COLLATE gbk_chinese_ci) UNION ALL
SELECT medicine.*,CONVERT('虚拟' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%虚拟%' USING gbk ) COLLATE gbk_chinese_ci) UNION ALL
SELECT medicine.*,CONVERT('非' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%非%' USING gbk ) COLLATE gbk_chinese_ci)  UNION ALL
SELECT medicine.*,CONVERT('处' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%处%' USING gbk ) COLLATE gbk_chinese_ci)  UNION ALL
SELECT medicine.*,CONVERT('贵重' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%贵重%' USING gbk ) COLLATE gbk_chinese_ci)  UNION ALL
SELECT medicine.*,CONVERT('未定义' USING gbk ) COLLATE gbk_chinese_ci as `cust_type` FROM  medicine WHERE med_hint LIKE (CONVERT('%未定义%' USING gbk ) COLLATE gbk_chinese_ci)