Mysql case 视图操作报错 1267 Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE)……

报错

视图（view）查询时报错：General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'case' 

报错语句

SELECT
	`test`.`score_item_id` AS `score_item_id`,
	`test`.`title` AS `title`,
	`test`.`check_year` AS `check_year`,
	`test`.`check_month` AS `check_month`,
	sum((
		CASE
				`test`.`check_month` 
				WHEN '01' THEN
				1 ELSE 0 
			END 
			)) AS `january` 
	FROM
		`test` 
	GROUP BY
		`test`.`type_id`,
		`test`.`check_year` 
	ORDER BY
	`test`.`check_year` DESC,
	`test`.`score_item_id`

处理

从报错中可以看出是case操作时的字段出现了字符集混乱问题，由于我数据库默认设置的字符集是utf8mb4_general_ci，所以我直接把字符集转为utf8mb4，处理后的语句如下，在case字段出加了convert()函数对字段字符集进行转换

SELECT
	`test`.`score_item_id` AS `score_item_id`,
	`test`.`title` AS `title`,
	`test`.`check_year` AS `check_year`,
	`test`.`check_month` AS `check_month`,
	sum((
		CASE
				CONVERT ( `test`.`check_month` USING utf8mb4 ) 
				WHEN '01' THEN
				1 ELSE 0 
			END 
			)) AS `january` 
	FROM
		`test` 
	GROUP BY
		`test`.`type_id`,
		`test`.`check_year` 
	ORDER BY
	`test`.`check_year` DESC,
	`test`.`score_item_id`