MySQL 8字符集转换问题：UNION子查询引发排序规则冲突

场景复现 #

表结构与初始化 #

CREATE TABLE `table_latin` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`char_id` char(6) NOT NULL,
`v` varchar(128) NOT NULL,
PRIMARY KEY (`id`),
KEY `char_id` (`char_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 插入测试数据
insert into table_latin (char_id, v) values ('aaaa', 'bbbb');

-- 设置连接字符集为utf8mb3
set names utf8mb3;

正常执行的查询 #

SELECT char_id FROM `table_latin` UNION ALL SELECT 'N/A';

此查询正常运行，原因是char_id列的coercibility为2（IMPLICIT），低于字面量'N/A'的coercibility（4，COERCIBLE），MySQL会自动将utf8mb3字面量转换为latin1字符集（二者均仅包含ASCII字符，转换无冲突）。

报错的查询 #

SELECT char_id FROM `table_latin` UNION ALL SELECT * FROM (SELECT 'N/A') a;

执行后抛出错误：

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb3_general_ci,COERCIBLE) for operation 'UNION'

经检查子查询列的coercibility仍为4：

SELECT coercibility(a.test) FROM (SELECT 'N/A' as 'test') a;
-- 返回结果：4

核心原因分析 #

问题的关键在于子查询将字面量包装成了派生列，改变了其字符集属性的处理逻辑：

1. 直接使用SELECT 'N/A'时，这个字面量属于“即时值”，虽然字符集是utf8mb3，但MySQL会将其视为可被强制转换的对象，当与coercibility更低的表列进行UNION时，会自动转换为表列的字符集。
2. 但通过子查询(SELECT 'N/A') a返回的列，虽然coercibility数值还是4，但这个派生列被MySQL赋予了显式的utf8mb3字符集属性，不再是单纯的“可强制转换字面量”。此时MySQL在处理UNION时，会认为这是一个有固定字符集的列，不会自动将其转换为左侧表列的latin1字符集，最终导致字符集/排序规则冲突。

可以通过以下查询验证派生列的字符集：

SELECT charset(a.test) FROM (SELECT 'N/A' as 'test') a;
-- 返回结果：utf8mb3

解决方法 #

方法1：显式转换子查询列的字符集 #

将子查询中的字面量转换为与表列一致的latin1字符集：

SELECT char_id FROM `table_latin` UNION ALL SELECT * FROM (SELECT CONVERT('N/A' USING latin1) as test) a;

方法2：指定子查询列的排序规则 #

给子查询列指定与表列匹配的排序规则：

SELECT char_id FROM `table_latin` UNION ALL SELECT * FROM (SELECT 'N/A' COLLATE latin1_swedish_ci as test) a;

内容的提问来源于stack exchange，提问作者Nishant Neupane