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

茉莉小涛丶

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

阅读量873

点赞数

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

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

本文链接：https://blog.csdn.net/moli_Y/article/details/131314648

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

版本环境

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