gogoWebsite

1. Causes

Illegal mix of collations errors are usually caused by different character sets (collations) used in the database table. In MySQL, character sets (collations) are used to specify character set encoding rules, such as: UTF-8, GBK, etc. When the character sets of two strings are found to be inconsistent in a query or comparison operation, an Illegal mix of collations error will occur.

2. Solution

Scheme 1: explicitly specify the character set (collations) in a query or comparison operation, for example:

select , from table1 a join table2 b on = where COLLATE utf8mb4_general_ci =  COLLATE utf8mb4_general_ci

Scheme 2: Change the character sets (collations) of all fields in the table to the same character sets (collations). The specific steps are as follows:

1. View the character sets of all fields in the table (collations);
2. Modify all character sets in the table to be consistent character sets;
3. Re-index all fields in the table to ensure the consistency of the index.

Examples are as follows:

-- View the character sets of all fields in the table (collations)
 SHOW FULL COLUMNS FROM table_name;

 -- Modify the character set (collations) of all fields in the table to utf8mb4_general_ci
 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 -- Modify the character set (collations) of the table to utf8mb4_general_ci
 ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 -- Rebuild the index for all fields in the table, and rebuild the primary key index as follows. Other indexes can be modified by yourself.
 ALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (id);