The error when joining MySQL multiple tables is as follows: [Err]1267 – Illegal mix of collations(utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=
means two tables The collation (COLLATION) is different and the comparison cannot be completed. COLLATION is used for sorting and size comparison. A character set has one or more COLLATIONs and ends with _ci (case insensitive), _cs (case sensitive) or _bin (binary). When doing comparisons, you should ensure that the character ordering of both tables is the same. Generally, you don't specify when creating a table. You can use the default. If it is all the default, there will be no problem. This article mainly introduces the analysis of different errors in MySQL table sorting rules. I hope it can help everyone.
Let’s simulate various scenarios. The table structure is as follows (the default sorting rule of utf8 is utf8_general_ci):
mysql> show create table test.cs\G *************************** 1. row *************************** Table: cs Create Table: CREATE TABLE `cs` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec)
View the table default sorting rule Set
mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.tables where table_name='cs'; +--------------+------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------+------------+-----------------+ | test | cs | utf8_general_ci | +--------------+------------+-----------------+ 1 row in set (0.00 sec)
View column sorting rule set
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs'; +--------------+------------+-------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | +--------------+------------+-------------+-----------------+ | test | cs | id | NULL | | test | cs | name | utf8_general_ci | +--------------+------------+-------------+-----------------+ 2 rows in set (0.00 sec)
Upgrading from utf8 to utf8mb4 does not support online ddl. As follows:
##
mysql> ALTER TABLE cs CONVERT TO CHARACTER SET utf8mb4,ALGORITHM=INPLACE,LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> ALTER TABLE cs CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci,ALGORITHM=INPLACE,LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> ALTER TABLE cs CHARACTER SET utf8 collate utf8_unicode_ci; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.tables where table_name='cs'; +--------------+------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------+------------+-----------------+ | test | cs | utf8_unicode_ci | +--------------+------------+-----------------+ 1 row in set (0.00 sec) mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs'; +--------------+------------+-------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | +--------------+------------+-------------+-----------------+ | test | cs | id | NULL | | test | cs | name | utf8_general_ci | +--------------+------------+-------------+-----------------+ 2 rows in set (0.00 sec)
mysql> ALTER TABLE cs CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci; Query OK, 5 rows affected (0.06 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs'; +--------------+------------+-------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | +--------------+------------+-------------+-----------------+ | test | cs | id | NULL | | test | cs | name | utf8_unicode_ci | +--------------+------------+-------------+-----------------+ 2 rows in set (0.00 sec)
mysql> ALTER TABLE cs default CHARACTER SET utf8 collate utf8_general_ci,ALGORITHM=INPLACE,LOCK=NONE; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.tables where table_name='cs'; +--------------+------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------+------------+-----------------+ | test | cs | utf8_general_ci | +--------------+------------+-----------------+ 1 row in set (0.00 sec) mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs'; +--------------+------------+-------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | +--------------+------------+-------------+-----------------+ | test | cs | id | NULL | | test | cs | name | utf8_unicode_ci | +--------------+------------+-------------+-----------------+ 2 rows in set (0.00 sec)
Detailed code explanation of the four partition types of MySQL table
How to use mysql table connection
How to solve the Chinese garbled problem in php mysql table
The above is the detailed content of Analysis of error reports caused by different MySQL table sorting rules. For more information, please follow other related articles on the PHP Chinese website!