如何编写正确的UPDATE查询？解决排序规则冲突报错

云上老周

2026-5-29

解决MySQL更新时的排序规则冲突问题

这个排序规则不兼容的坑我之前也踩过，咱们一步步来拆解解决～

问题根源

报错#1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '<>'的本质是：clients和orders表（或关联字段）的排序规则不一致——一个用了utf8_unicode_ci，另一个用了utf8_general_ci。

单独跑SELECT语句时，你指定了具体的clients.id值，没有触发跨表字段的大范围比较，所以没暴露问题；但执行UPDATE时，全表关联查询会触发字段比较，排序规则不兼容就直接抛出了错误。

解决方案

这里提供三种可行的解决方式，你可以根据实际场景选择：

1. 统一表/字段的排序规则（推荐长期方案）

先检查两个表的排序规则：

SHOW CREATE TABLE clients;
SHOW CREATE TABLE orders;

查看输出里的COLLATE字段，如果两个表规则不一致，把其中一个表的排序规则改成和另一个一致，比如把orders表改成utf8_unicode_ci：

ALTER TABLE orders CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

如果只是个别关联字段（比如orders.client_id或clients.id）规则不一致，单独修改字段即可：

-- 示例：修改orders表的client_id字段排序规则
ALTER TABLE orders MODIFY COLUMN client_id INT NOT NULL COLLATE utf8_unicode_ci;

（注意要根据字段实际类型调整语句）

2. 在查询中显式指定排序规则（临时方案）

如果不想修改表结构，可以在JOIN条件里显式指定字段的排序规则，强制统一：

UPDATE clients 
SET activity = ( 
    SELECT IFNULL( 
        GROUP_CONCAT( 
            CONCAT( ' ', o.event_abr, ' ', IFNULL(CONCAT(o.quantity,'x'),''), IFNULL(o.price,'') ) 
        ) ,''
    ) 
    FROM clients c 
    LEFT OUTER JOIN orders o 
        ON (c.id = o.client_id COLLATE utf8_unicode_ci) -- 这里指定排序规则
    WHERE c.id = clients.id 
)

你也可以反过来把c.id指定为utf8_general_ci，只要和关联字段规则一致就行。

3. 优化查询逻辑（更高效的写法）

你的UPDATE语句用了关联子查询，其实可以改成JOIN形式，不仅效率更高，也能顺便处理排序规则问题：

UPDATE clients c
LEFT JOIN (
    -- 先预聚合orders里的活动数据
    SELECT client_id,
           IFNULL(GROUP_CONCAT(CONCAT(' ', event_abr, ' ', IFNULL(CONCAT(quantity,'x'),''), IFNULL(price,''))), '') AS activity_str
    FROM orders
    GROUP BY client_id
) o ON c.id = o.client_id COLLATE utf8_unicode_ci -- 指定排序规则
SET c.activity = COALESCE(o.activity_str, '');

这种写法先聚合orders的数据，再关联到clients表更新，比子查询的执行效率高很多，尤其当数据量大的时候。

验证修改

调整后再执行UPDATE语句，应该就能正常运行了。如果还是有问题，可以再检查下clients.activity字段的排序规则是否和子查询返回结果的规则一致。

内容的提问来源于stack exchange，提问作者Dmitrijs Bambulaks