快捷導(dǎo)航

MySQL 8.0.26版本升級32版本查詢數(shù)據(jù)為空的解決方案

更新時間：2024年03月18日 09:24:35 作者：GreatSQL

這篇文章主要介紹了MySQL 8.0.26版本升級32版本查詢數(shù)據(jù)為空的跟蹤,本文給大家分享兩種解決方法,結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友參考下吧

版本信息

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;

表數(shù)據(jù)

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

查詢數(shù)據(jù)

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或者在視圖上添加英文過濾條件均能查詢出數(shù)據(jù)，在view上添加中文過濾條件sql語句返回結(jié)果為空，有warnings提醒，warnings具體內(nèi)容為Cannot convert string

在官方網(wǎng)站進(jìn)行搜索，以下連接有相似內(nèi)容：

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

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

MySQL 8.0.32版本中對于UNION/UNION ALL 后的結(jié)果中的字符串字段過濾篩選不生效，客戶端中提示：

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

官方已確認(rèn)是8.0.32中的一個bug，已在8.0.33版本中修復(fù)，對于8.0.32版本，官方給出的解決方式如下：

set optimizer_switch="derived_condition_pushdown=off";

讓所有Session都生效，需要在配置文件中將optimizer_switch設(shè)置為off

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

解決方法

方法一：

設(shè)置set optimizer_switch="derived_condition_pushdown=off";后SQL語句能正常執(zhí)行

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語句設(shè)置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 ??

關(guān)于 GreatSQL

GreatSQL是適用于金融級應(yīng)用的國內(nèi)自主開源數(shù)據(jù)庫，具備高性能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產(chǎn)環(huán)境，且完全免費(fèi)并兼容MySQL或Percona Server。

相關(guān)鏈接： GreatSQL社區(qū) GiteeGitHub Bilibili

GreatSQL社區(qū)：

社區(qū)博客有獎?wù)鞲逶斍椋?a rel="external nofollow" target="_blank">https://greatsql.cn/thread-100-1-1.html

到此這篇關(guān)于MySQL 8.0.26版本升級32版本查詢數(shù)據(jù)為空的跟蹤的文章就介紹到這了,更多相關(guān)mysql查詢數(shù)據(jù)為空內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: