[Solved] MySQL query reports an error: Legal mix of settlements

1. Specific scenarios

The two tables are:

CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户编号（自增字段）',
  `userName` varchar(32) NOT NULL DEFAULT '' COMMENT '用户昵称',
  # ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `tb_sms_message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机号',
  # ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;

Error Message:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

2.Find SQL

SELECT * FROM `tb_sms_message`
  where tel not IN (select userName from tb_user);

3. Cause analysis

The collates of the two associated fields are inconsistent and cannot be directly associated

 

Here, we can convert the proofreading set of the field to consistent:

CONVERT(tel USING utf8) COLLATE utf8_general_ci

Modified SQL:

SELECT * FROM `tb_sms_message`
  where CONVERT(tel USING utf8) COLLATE utf8_general_ci not IN (select userName from tb_user);

 

Similar Posts: