Cannot convert string ‘\xE9\x87...‘ from utf8mb4 to binary

版本环境

• MySQL 8.0.32

问题

使用union连接表后，通过关键字like模糊查询内容时报错。

查看日志show warnnings显示如下：

|level   | code | message                                                    |
|Warning | 1300 | Cannot convert string '\xE9\x87...' from utf8mb4 to binary |

官方解释文档

A condition pushdown into a UNION of queries having LIKE clauses did not preserve the correct character set, leading to an (erroneous) empty result.
We solve this problem in two parts:
By refactoring resolution of LIKE expressions, in which character set determination and propagation were previously performed in two separate blocks of the code that were not always consistent with one another.
By adding, in the internal parse_expression() function, a character set prefix to any literal character string that is cloned.
(Bug #107787, Bug #34359297, Bug #34589153)

Bug #107787 链接

临时解决办法

修改optimizer_switch的derived_condition_pushdown值为off

# 设置当前会话
set optimizer_switch='derived_condition_pushdown=off';
# 设置全局值
set global optimizer_switch='derived_condition_pushdown=off';
# 设置全局值，并固化到配置⽂件my.cnf
set persist optimizer_switch='derived_condition_pushdown=off';

重启数据库systemctl restart mysqld，解决~~

原文：https://greatsql.cn/thread-209-1-1.html