报错
视图（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`
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/format,webp/resize,h_160,m_lfit)