Analysis of different MySQL table sorting rules error
|
The following error is reported when MySQL joins multiple tables: [Err]1267 – Illegal mix of collations (utf8_general_ci, IMPLICIT) and (utf8_unicode_ci, IMPLICIT) for operation '= This means that the sorting rules (COLLATION) of the two tables are 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 a comparison, you should make sure that the character ordering of both tables is the same. Generally, you don't specify it when creating a table. You can use the default one. There will be no problem if all the defaults are used. Let's simulate various scenarios. The table structure is as follows (the default sorting rule for 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 default collation set for a table 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 Collation 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. Changing from utf8.utf8_general_ci to utf8.utf8_unicode_ci does not support online DDL, as follows: 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. If you modify the character set in the following way, you will find that only the table level is changed, not the column level. 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) So when you really change the character set, don't forget to add CONVERT TO, as follows: 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) To change the default character set for just one table, use this statement: 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) You can find that the column character set has not changed, and only new columns will inherit the table character set (utf8.utf8_general_ci) by default. Summarize The above is all the content of this article about the analysis of different error problems in MySQL table sorting rules. I hope it will be helpful to everyone. Interested friends can refer to: Several important MySQL variables, Analysis of MySQL declared variables and stored procedures, Detailed code explanation of the relationship between MySQL master library binlog (master-log) and slave library relay-log, Detailed explanation of MySQL prepare principle, etc. If you have any questions, you can leave a message at any time to communicate with each other and make progress together. You may also be interested in:
|
<<: JavaScript+HTML to implement student information management system
>>: How to build nfs service in ubuntu16.04
Recommend
How to change the domestic source of Ubuntu 20.04 apt
UPD 2020.2.26 Currently Ubuntu 20.04 LTS has not ...
Detailed explanation of four solutions for MySQL active-active synchronous replication
Table of contents Master-Master Synchronization S...
How to use the EXPLAIN command in SQL
In daily work, we sometimes run slow queries to r...
Summary of Problems in Installing MySQL 5.7.19 under Linux
The first time I installed MySQL on my virtual ma...
W3C Tutorial (5): W3C XML Activities
XML is designed to describe, store, transmit and ...
JavaScript knowledge: Constructors are also functions
Table of contents 1. Definition and call of const...
jQuery realizes the effect of theater seat selection and reservation
jQuery realizes the effect of theater seat select...
Using js to realize dynamic background
This article example shares the specific code of ...
The combination and difference between ENTRYPOINT and CMD in dockerfile
In the previous article [Detailed explanation of ...
Details on using JS array methods some, every and find
Table of contents 1. some 2. every 3. find 1. som...
Detailed explanation of monitoring NVIDIA GPU usage under Linux
When using TensorFlow for deep learning, insuffic...
JavaScript custom calendar effect
This article shares the specific code of JavaScri...
Example of using swiper plugin to implement carousel in Vue
Table of contents vue - Use swiper plugin to impl...
JavaScript - Using slots in Vue: slot
Table of contents Using slots in Vue: slot Scoped...
Vue project code splitting solution
Table of contents background Purpose Before split...