1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for opera

数据库报错之 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

数据库报错之 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

原 SQL :

SELECT
	d.shopname,
	c.goodsno,
	s.id
FROM
	t_oversea_demand d
	LEFT JOIN t_overseapurchasing c ON d.id = c.demandID
	LEFT JOIN d_shop s ON  s.`name` = d.shopname
WHERE
	d.STATUS = '5' 
	AND d.updatetime > '2020-02-09' 
GROUP BY
	CONCAT( d.shopname, c.goodsno )

报错：

SELECT
	d.shopname,
	c.goodsno,
	s.id
FROM
	t_oversea_demand d
	LEFT JOIN t_overseapurchasing c ON d.id = c.demandID
	LEFT JOIN d_shop s ON  s.`name` = d.shopname
WHERE
	d.STATUS = '5' 
	AND d.updatetime > '2020-02-09' 
GROUP BY
	CONCAT( d.shopname, c.goodsno )
> 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
> 时间: 0.001s

分析：

关联表 d_shop 与 主表 t_oversea_demand  表结构的编码不同 所以不能用来做等式

解决：

方案1 修改表结构

ALTER TABLE `表名` CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci

方案2 使用 CONVERT 。之前这个表使用的编码不同是有原因的 不能随便更改 于是乎…

SELECT
   d.shopname,
   c.goodsno,
   s.id
FROM
   t_oversea_demand d
   LEFT JOIN t_overseapurchasing c ON d.id = c.demandID
   LEFT JOIN d_shop s ON CONVERT ( s.`name` USING utf8 ) = CONVERT ( d.shopname USING utf8 )
WHERE
   d.STATUS = '5' 
   AND d.updatetime > '2020-02-09' 
GROUP BY
   CONCAT( d.shopname, c.goodsno )

就是把编码不同到两个表字段临时转码处理以下就好了

完美解决