MySQL 8.0.32 union 语句中文查不到数据-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL 8.0.32 union 语句中文查不到数据。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

关键字

MySQL union 语句，中文查不到数据

问题描述

MySQL 8.0.32 union 语句，中文查不到数据

解决问题思路

1、Create a table `test` with two fields, such as id and name
mysql>create table test ( id int unsigned auto_increment key, name varchar(50))ENGINE=INNODB;
2、Insert data into the table. The name field is Chinese characters, such as "姜志福"
mysql>insert into  test(id,name) values(1,'姜志福');
3、Use the union statement to query, such as：
select * from (
select * from test 
union all 
select * from test 
)a  where id = 1 and name = '姜志福'

最终排查为这是MySQL BUG
https://bugs.mysql.com/bug.php?id=110005

临时解决可以关闭 derived_condition_pushdown 特性
set optimizer_switch='derived_condition_pushdown=off';

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html

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)



https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-33.html

When cloning a condition to push down to a derived table, characters in strings representing conditions were converted to utfmb4 correctly only for values less than 128 (the ASCII subset), and code points outside the ASCII subset were converted to invalid characters, causing the resulting character strings to become invalid. For derived tables without UNION, this led to problems when a column name from the derived table used characters outside the ASCII subset, and was used in the WHERE condition. For derived tables with UNION, it created problems when a character outside the ASCII subset was present in a WHERE condition.
We fix these issues by initializing the string used for representing the condition in such cases to the connection character set. (Bug #109699, Bug #34996488)