MetaBase增加筛选条件查询报错

参考文档出处：https://blog.csdn.net/lbzhao_28/article/details/119211567

数据库是mysql8.0.28版本，数据库字符集用的是utf8

问题描述：

实用Metabase制作报表，我是用视图输出报表内容，然后Metabase里面直接设置查询这张识图，添加过滤条件。
我的sql大致如下（简化过）:

SELECT
CASE
  WHEN ( `a`.`finish_time` IS NOT NULL ) THEN
     '已完成' 
  WHEN ((`a`.`reserve_start_time` >= now())  AND ( `a`.`sign_time` IS NULL )  AND ( `a`.`finish_time` IS NULL )) THEN
     '未到仓' 
  WHEN ((a`.`sign_time` IS NOT NULL )  AND ( `a`.`finish_time` IS NULL )) THEN
     '到仓等待' 
  WHEN ((`a`.`reserve_start_time` < now()) AND ( `a`.`sign_time` IS NULL )  AND ( `a`.`finish_time` IS NULL )) THEN
     '已超时' 
     ELSE '' 
     END ) AS `status_name` 
FROM a
	

这个status_name字段，在Metabase中新增 “文本或类别”过滤器后，只要输入查询条件，查询就会报错，没有查询内容则正常。

解决方案

在数据库执行下面的语句：

show full columns from 【表名】

会显示如下结果
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| ----------- | ----------- | ------------------ | ---- | --- | ------- | ----- | ------------------------------- | ------- |
| column1 | varchar(20) | utf8_general_ci | YES | | (NULL) | | select,insert,update,references | xxx |
| status_name | varchar(20) | utf8mb4_0900_ai_ci | YES | | (NULL) | | select,insert,update,references | xxx |

重点来了
注意看status_name的字段字符集格式，是utf8mb4_0900_ai_ci，这个字符集格式导致的Metabase查询报错，将sql改为如下形式

SELECT 
  t1.`status_name` COLLATE utf8mb4_general_ci AS `status_name_str`
FROM (
  SELECT
CASE
  WHEN ( `a`.`finish_time` IS NOT NULL ) THEN
     '已完成' 
  WHEN ((`a`.`reserve_start_time` >= now())  AND ( `a`.`sign_time` IS NULL )  AND ( `a`.`finish_time` IS NULL )) THEN
     '未到仓' 
  WHEN ((a`.`sign_time` IS NOT NULL )  AND ( `a`.`finish_time` IS NULL )) THEN
     '到仓等待' 
  WHEN ((`a`.`reserve_start_time` < now()) AND ( `a`.`sign_time` IS NULL )  AND ( `a`.`finish_time` IS NULL )) THEN
     '已超时' 
     ELSE '' 
     END ) AS `status_name` 
FROM a
)

加上 COLLATE utf8mb4_general_ci 给字段转换一下字符集格式即可