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
The principle and implementation of two-way binding in Vue2.x
Table of contents 1. Implementation process 2. Di...
Detailed tutorial on deploying Hadoop cluster using Docker
Recently, I want to build a hadoop test cluster i...
MySQL ID starts to increase from 1 to quickly solve the problem of discontinuous ID
mysql id starts from 1 and increases automaticall...
HTML implements Double 11 coupon grabbing (set time to open the coupon grabbing page)
Without further ado, I will post the code for you...
jQuery implements simple pop-up window effect
This article shares the specific code of jQuery t...
How to implement n-grid layout in CSS
Common application scenarios The interfaces of cu...
Solve the problem of HTML automatic formatting after saving in vscode
The version of vsCode has been updated in recent ...
Example code of vue custom component to implement v-model two-way binding data
In the project, you will encounter custom public ...
Ubuntu installs scrcpy to complete mobile phone screen projection and control (another way to use QQ WeChat in Ubuntu)
Scrcpy Installation snap install scrcpy adb servi...
Details of using Vue slot
Table of contents 1. Why use slots? 1.1 slot 1.2 ...
Use dockercompose to build springboot-mysql-nginx application
In the previous article, we used Docker to build ...
mysql gets yesterday's date, today's date, tomorrow's date, and the time of the previous hour and the next hour
1. Current date select DATE_SUB(curdate(),INTERVA...
How to set up remote access to a server by specifying an IP address in Windows
We have many servers that are often interfered wi...
SQL implementation LeetCode (176. Second highest salary)
[LeetCode] 176. Second Highest Salary Write a SQL...
An article to understand the execution process of MySQL query statements
Preface We need to retrieve certain data that mee...