1

After I changed both table collition into utf8mb4_unicode_ci, the database still show this error:

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

when I execute this query:

select *
from zl_report_user
where union_id not in(
    select distinct unionid
    from wechat_user
)

should I restart the MySQL 5.7 to make it take effect? I restart the MySQL still did not work. what should I do to avoid this problem? I also tried this way:

ALTER DATABASE `zhuolian-report-fat` CHARACTER SET utf8 COLLATE utf8_unicode_ci;

still did not work.

2 Answers 2

1

I use this command fix the problem:

ALTER TABLE zl_report_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

it should always use utf8mb4 not utf8.

1
  • Also consider that if charset-collation is different for two columns of two tables then no indexes will be used for comparisons/joins of that columns.
    – Kondybas
    Jul 5, 2022 at 12:02
1

I've been struggling with a very similar problem, and was able to fix it by casting the result of the select distinct in the sub query. So if you changed your query to look like this it should work.

select *
from zl_report_user
where union_id not in(
    select distinct cast(unionid as char)
    from wechat_user
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.