MariaDB中关联查询使用COALESCE函数遇VARBINARY/BLOB不支持错误的解决方法

问题背景 #

我需要关联两张表，从其中一张表的多列中选取第一个非空值，同时查询另一张表的列，写了如下SQL语句：

SELECT t1.id, t1.date, 
       coalesce(t2.source, t2.src, t2.data_source) as source, 
       coalesce(t2.medium, t2.mdm, t2.data_medium) as medium 
FROM table1 t1 
JOIN table2 t2 ON t1.id = t2.id 
WHERE t1.date >= '2020/01/01';

执行时遇到错误：

SQL Error[1815][HY000]: (conn=3186) Internal error: VARBINARY/BLOB in filter or function is not supported.

我使用的是MariaDB，请问该怎么解决这个问题？

解决方案 #

这个问题我之前处理过，核心原因是你在COALESCE函数里用到了VARBINARY或BLOB类型的列——MariaDB的机制限制了这类二进制类型直接在聚合/条件函数中使用，所以才抛出了这个内部错误。

最简单的解决办法就是把这些二进制列转换为字符串类型，用CAST()或者CONVERT()函数都可以，我个人更习惯用CAST，语法更直观。

修改后的SQL语句如下：

SELECT t1.id, t1.date, 
       coalesce(CAST(t2.source AS CHAR), CAST(t2.src AS CHAR), CAST(t2.data_source AS CHAR)) as source, 
       coalesce(CAST(t2.medium AS CHAR), CAST(t2.mdm AS CHAR), CAST(t2.data_medium AS CHAR)) as medium 
FROM table1 t1 
JOIN table2 t2 ON t1.id = t2.id 
WHERE t1.date >= '2020/01/01';

额外建议 #

• 如果这些列本来就应该存储文本内容，我建议直接修改表结构，把它们的类型改成VARCHAR或者TEXT，这样后续查询不用每次都做类型转换，效率会更高，也能避免类似的问题。
• 要是必须保留二进制类型，转换的时候可以指定字符集，比如CAST(t2.source AS CHAR CHARACTER SET utf8mb4)，防止出现乱码。
• 另外，如果你用的是比较旧的MariaDB版本，升级到最新的稳定版也可能解决这类兼容性问题，毕竟官方后续对函数的类型支持做了不少优化。

内容的提问来源于stack exchange，提问作者John peter