报错
视图（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`
