mysql error 1267

怼烎@ 2022-05-20 06:05 72阅读 0赞

###### Error Code: 1267. Illegal mix of collations (utf8mb4\_unicode\_ci,IMPLICIT) and (utf8mb4\_general\_ci,IMPLICIT) for operation ‘=’ 0.015 sec ######

#### 错误原因：关联表的相同字段的编码不一致导致的错误 ####

#### 示例： ####

# 创建表 t1
    create table t1( id int(11) PRIMARY KEY auto_increment, name varchar(25), age varchar(20), common varchar(25) collate utf8mb4_unicode_ci default null comment '桥梁' );
    
    insert into t1 (name,age,common) values("刘德华","57","明星");
    insert into t1 (name,age,common) values("黄日华","59","演员");
    
    select * from t1;

![这里写图片描述][70]

# 创建表 t2
    create table t2( id int(11) PRIMARY KEY auto_increment, nickname varchar(25), password varchar(20), common varchar(25) collate utf8mb4_general_ci default null comment '桥梁' );
    
    insert into t2 (nickname,password,common) values("ldh","57123","明星");
    insert into t2 (nickname,password,common) values("hrh","59123","演员");
    
    select * from t2;

![这里写图片描述][70 1]

**查询**

select t1.name,t1.age,t2.nickname,t2.password,t2.common from t1 t1 left join t2 t2 on t1.common = t2.common;

报错信息：

15:52:21    select t1.name,t1.age,t2.nickname,t2.password,t2.common  from t1 t1 join t2 t2 on t1.common = t2.common LIMIT 0, 1000   Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='   0.015 sec

#### 解决方案： ####

修改t2表中common字段的编码格式与t1表中common的编码格式保持一致，即修改T2表的common字段的编码为utf8mb4\_unicode\_ci即可。

# 创建表 t2
     create table t2( id int(11) PRIMARY KEY auto_increment, nickname varchar(25), password varchar(20), common varchar(25) collate utf8mb4_unicode_ci default null comment '桥梁' );

此时，执行查询语句成功，如下：

select t1.name,t1.age,t2.nickname,t2.password,t2.common from t1 t1 left join t2 t2 on t1.common = t2.common;

![这里写图片描述][70 2]

[70]: /images/20220520/6ad9a53ce0b64ee0a8e7e6f2c10b4513.png
[70 1]: /images/20220520/2951ff2dc161464fa4856bc0f484bca6.png
[70 2]: /images/20220520/dcdacb37b090479a9b8d4b0731147ab8.png