MySQL中LIKE与CONCAT、UNION ALL结合使用时匹配失效问题排查

看起来你遇到的问题核心是字符集或排序规则不匹配导致的模糊查询失效，尤其是在CONCAT生成的字段上。咱们一步步拆解原因和解决办法：

可能的原因分析 #

你提到直接查询master_subject.name可以匹配%Пром%，但子查询中CONCAT(uid, ' ', name)生成的searchBy字段却不行，连单表子查询也失效——这说明问题出在CONCAT操作后的字段属性上，而非UNION ALL或者子查询本身：

• MySQL中，当你拼接不同字符集/排序规则的字段时，会按照字符集优先级自动确定结果的字符集和排序规则。如果uid字段的字符集是latin1（或其他不支持西里尔文的字符集），而name是utf8mb4，拼接后的searchBy可能会被强制转换为latin1，导致西里尔字符被乱码或丢失，自然无法匹配。
• 另一种可能是searchBy的排序规则是binary（二进制排序），这种排序规则会严格区分字符的二进制值，而原表name字段用的是支持西里尔文的排序规则（比如utf8mb4_unicode_ci），直接查询时不区分二进制，所以能匹配，拼接后的字段却不行。

排查步骤 #

1. 检查原表字段的字符集和排序规则
   执行以下命令，查看master_subject表中uid和name字段的属性：

   SHOW FULL COLUMNS FROM master_subject LIKE 'uid';
   SHOW FULL COLUMNS FROM master_subject LIKE 'name';
   

   重点看Collation列，确认name的排序规则是否支持西里尔文（比如utf8mb4_croatian_ci、utf8mb4_unicode_ci等），而uid是否和它一致。

2. 检查子查询中searchBy的字符集/排序规则
   执行这条语句，查看拼接后的字段属性：

   set @tenantId = 1;
   SELECT COLLATION(searchBy), CHARSET(searchBy) FROM (
    select tenantId, 'Компанија' type, id, concat(uid, ' - ', name) name, concat(uid, ' ', name) searchBy, concat('/master/subjects/', id) url from master_subject where tenantId = @tenantId
   ) t1 LIMIT 1;
   

   如果结果的CHARSET不是utf8mb4，或者COLLATION和name字段不一致，那就是问题所在。

解决办法 #

方法1：显式指定CONCAT结果的排序规则 #

在CONCAT后加上COLLATE子句，强制使用和name字段一致的排序规则：

set @tenantId = 1;
select id, type, name, url, searchBy from (
 select tenantId, 'Производ' type, id, concat(uid, ' - ', name) name, concat(uid, ' ', name) COLLATE utf8mb4_unicode_ci searchBy, concat('/master/items/', id) url from master_item where tenantId = @tenantId
 union all
 select tenantId, 'Компанија' type, id, concat(uid, ' - ', name) name, concat(uid, ' ', name) COLLATE utf8mb4_unicode_ci searchBy, concat('/master/subjects/', id) url from master_subject where tenantId = @tenantId
) t1 where searchBy like '%Пром%';

注意：把utf8mb4_unicode_ci替换成你实际查到的name字段的排序规则。

方法2：转换拼接字段的字符集 #

用CONVERT()函数将拼接结果转换为支持西里尔文的字符集：

set @tenantId = 1;
select id, type, name, url, searchBy from (
 select tenantId, 'Производ' type, id, concat(uid, ' - ', name) name, CONVERT(concat(uid, ' ', name) USING utf8mb4) searchBy, concat('/master/items/', id) url from master_item where tenantId = @tenantId
 union all
 select tenantId, 'Компанија' type, id, concat(uid, ' - ', name) name, CONVERT(concat(uid, ' ', name) USING utf8mb4) searchBy, concat('/master/subjects/', id) url from master_subject where tenantId = @tenantId
) t1 where searchBy like '%Пром%';

方法3：修改字段默认字符集（长期方案） #

如果你的数据库中有很多这类拼接查询问题，建议统一修改uid字段的字符集，让它和name字段一致，这样后续拼接时就不会出现自动转换的问题：

ALTER TABLE master_subject MODIFY COLUMN uid VARCHAR(xxx) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE master_item MODIFY COLUMN uid VARCHAR(xxx) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意：替换xxx为uid字段实际的长度，操作前记得备份数据。

验证 #

修改后，先执行子查询查看searchBy的实际值，确认Пром确实存在其中，再执行带LIKE的查询，应该就能正常匹配了。

内容的提问来源于stack exchange，提问作者Sir Rubberduck