Solution to abnormal connection table caused by inconsistent MySQL character sets

Doing a simple table query as follows, an error message is displayed directly. It turns out that the character set is inconsistent. This article records the character set type of MySQL and the solution to the following problem

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 '='

1. Solution

Let's first look at the solution to the above problem. The easiest way to think of it is to unify the character sets of the two tables, either both are utf8mb4_general_ci or both are utf8mb4_unicode_ci. This problem will be naturally solved by unifying. What if I don't want to change the character set of the table? After all, in a production environment, doing this kind of operation is still risky. Here is a less elegant solution.

Specify the character set after the field (you can specify all of them as utf8mb4_general_ci or all of them as utf8mb4_unicode_ci, just process it according to your actual needs)

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

2. MySQL character set

Character Set

For domestic partners, generally speaking, our common character sets are the following three

• gbk: two bytes
• utf8: three bytes
• utf8mb4: four bytes

MySQL actually supports more, which can be queried through show charset;

Verification rules

In actual cases, we often see the following

• utf8_bin
• utf8mb4_unicode_ci
• utf8mb4_general_ci
• latin1_general_cs

Of course, we can also view the supported validation rules through show collation;

Please note the above wording

• ci: case insensitive abbreviation is case insensitive
• cs: case sensitive
• bin: binary storage, case sensitive

This is the end of this article about how to solve the abnormality of connecting tables caused by inconsistent MySQL character sets. For more related content about abnormality of connecting tables caused by inconsistent MySQL character sets, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!