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

茉莉小涛丶

已于 2023-06-20 20:40:36 修改

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： mysql 运维 centos

于 2023-06-20 20:29:20 首次发布

本文链接：https://blog.youkuaiyun.com/moli_Y/article/details/131314648

版权

MySQL 专栏收录该内容

2 篇文章

订阅专栏

在MySQL8.0.32中，使用UNION操作结合LIKE进行模糊查询时出现错误，日志显示字符集转换警告。官方文档指出这是由于条件推下到UNION查询时字符集处理不一致导致。临时解决方案是关闭optimizer_switch中的derived_condition_pushdown选项，可以通过设置会话、全局变量或配置文件实现。重启数据库后问题可得到解决。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

版本环境

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';