从mysql库往pg库中插入数据，出现Illegal mix of collations for operation报错，求求大佬看下咋解决

我来答

我在人工湖旁

2025-05-07

求助 mysql postgresql

已经修改过了url，字段也做了显示转换，在不动原mysql的情况下，该如何解决啊

我来答

添加附件

问题补充

2条回答

默认

最新

大大刺猬

这个报错是排序规则不一致导致的,错误码:ER_CANT_AGGREGATE_NCOLLATIONS

要显示转换字符集和排序规则, 只转换字符集可能不行哈.例子:

drop table t20250508_a;
create table t20250508_a(id int, name varchar(200)) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
insert into t20250508_a values(1,'李四');
insert into t20250508_a values(2,'张三');

drop table t20250508_b;
create table t20250508_b(id int, name varchar(200)) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
insert into t20250508_b values(1,'李四');
insert into t20250508_b values(2,'张三');

-- 不转换时,报错
select * from  t20250508_a join t20250508_b on t20250508_a.name = t20250508_b.name;

-- 只转换字符集,可能报错(看默认排序规则)
select * from  t20250508_a join t20250508_b on convert(t20250508_a.name using utf8mb4) = t20250508_b.name;

-- 均转换为一致的排序规则
select * from  t20250508_a join t20250508_b on convert(t20250508_a.name using utf8mb4) collate utf8mb4_0900_as_ci  = t20250508_b.name;