欢迎投稿

今日深度:

MySQL8.0.26版本升级32版本查询数据为空的解决方案,

MySQL8.0.26版本升级32版本查询数据为空的解决方案,


目录
  • 版本信息
  • 建表语句
  • 表数据
  • 查询数据
  • 解决方法
    • 方法一:
    • 方法二:

某业务系统将MySQL 8.0.26升级为GreatSQL 8.0.32-24 后,某些特定的SQL语句不能查询到数据。经测试 MySQL 8.0.32也存在相同的问题

此BUG已在 GreatSQL 8.0.32-25 版本中解决

MySQL 8.0.26版本升级32版本查询数据为空的跟踪

接到客户反馈的问题后,对问题进行了复现和分析。

版本信息

greatsql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32-24 |
+-----------+
1 row in set (0.00 sec)

建表语句

greatsql> show create table t_student;
CREATE TABLE `t_student1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表数据

greatsql> select * from t_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | abc    |  10 |
|  2 | 汤姆   |  20 |
+----+--------+-----+
2 rows in set (0.08 sec)

查询数据

greatsql>  select * from (select * from t_student union select * from t_student) temp where name='汤姆';
Empty set, 2 warnings (0.00 sec)
greatsql> show warnings;
+---------+------+-------------------------------------------------------------------------+
| Level   | Code | Message                                                                 |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE6\xB1\xA4\xE5\xA7\x86' from utf8mb4 to binary |
| Warning | 1300 | Cannot convert string '\xE6\xB1\xA4\xE5\xA7\x86' from utf8mb4 to binary |
+---------+------+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | abc    |  10 |
|  2 | 汤姆   |  20 |
+----+--------+-----+
2 rows in set (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp where name='abc';
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | abc  |  10 |
+----+------+-----+
1 row in set (0.00 sec)

可以看到 直接查询temp这个view或者 在视图上添加英文过滤条件均能查询出数据,在view上添加中文过滤条件sql语句返回结果为空,有warnings提醒,warnings具体内容为Cannot convert string

在官方网站进行搜索,以下连接有相似内容:

https://bugs.mysql.com/bug.php?id=110228

https://bugs.mysql.com/bug.php?id=110955

MySQL 8.0.32版本中对于UNION/UNION ALL 后的结果中的字符串字段过滤筛选不生效,客户端中提示:

Cannot convert string '%\x...' from utf8mb4 to binary

官方已确认是8.0.32中的一个bug,已在8.0.33版本中修复,对于8.0.32版本,官方给出的解决方式如下:

set optimizer_switch="derived_condition_pushdown=off";

让所有Session都生效,需要在配置文件中将optimizer_switch设置为off

此BUG已在 GreatSQL 8.0.32-25 版本中解决

解决方法

方法一:

设置set optimizer_switch="derived_condition_pushdown=off";后SQL语句能正常执行

greatsql> set optimizer_switch='derived_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp where name='汤姆';
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  2 | 汤姆   |  20 |
+----+--------+-----+
1 row in set (0.00 sec)

方法二:

对单个SQL语句设置NO_DERIVED_CONDITION_PUSHDOWN hint

greatsql> set optimizer_switch='derived_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
greatsql> select * from (select * from t_student union select * from t_student) temp where name='汤姆';
Empty set, 2 warnings (0.00 sec)
greatsql> select  /*+ NO_DERIVED_CONDITION_PUSHDOWN(temp) */ * from (select * from t_student union select * from t_student) temp where name='汤姆';
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  2 | 汤姆   |  20 |
+----+--------+-----+
1 row in set (0.00 sec)

Enjoy GreatSQL

www.htsjk.Com true http://www.htsjk.com/Mysql/47816.html NewsArticle MySQL8.0.26版本升级32版本查询数据为空的解决方案, 目录 版本信息 建表语句 表数据 查询数据 解决方法 方法一: 方法二: 某业务系统将MySQL 8.0.26升级为GreatSQL 8.0.32-24 后,某些特定的...
评论暂时关闭