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.

Dolphin
  • 775
  • 4
  • 18
  • 34

2 Answers2

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.

Dolphin
  • 775
  • 4
  • 18
  • 34
  • 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 05 '22 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
)
Chris M
  • 111
  • 2