Mysql > alter table select * from ‘mysql’; alter table select * from ‘mysql’

select a.id, b.id from tt as a, t2 as b where a.xx = b.xx

-- Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
Copy the code

1. Solutions

If the character set is utF8MB4_general_CI or utF8MB4_unicode_CI, the default character set is utF8MB4_unicode_CI. The default character set is utF8MB4_unicode_CI

What if I don’t want to change the character set of the table? After all, it’s risky to do this in a production environment, and here’s a less elegant solution

  • Specify the character set after the field (all can be specified asutf8mb4_general_ciOf course, you can specify all of themutf8mb4_unicode_ci, can be processed according to actual needs)
select a.id, b.id from tt as a, t2 as b where a.xx = b.xx collate utf8mb4_general_ci
Copy the code

Mysql character set

Character set

Generally speaking, there are three common character sets for Chinese characters

  • gbkTwo bytes:
  • utf8: three bytes
  • utf8mb4: four bytes

Mysql actually supports a lot of things, you can show charset; The query

Validation rules

In a real case, you will often see the following

  • utf8_bin
  • utf8mb4_unicode_ci
  • utf8mb4_general_ci
  • latin1_general_cs

We can also do show collation; View supported verification rules

Please pay attention to the writing above

  • ci: Case Insensitive stands for insensitiveCase insensitive
  • cs: case sensitiveCase sensitivity
  • Bin: binary storage, case sensitive

II. The other

1. A gray Blog:liuyueyi.github.io/hexblog

A gray personal blog, recording all the study and work in the blog, welcome everyone to go to stroll

  1. The statement

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

  • Micro Blog address: Small Gray Blog
  • QQ: a gray /3302797840
  • Wechat official account: One Grey Blog