Analysis of Different errors in MySQL table sorting rules and mysql table sorting rules

Analysis of Different errors in MySQL table sorting rules and mysql table sorting rules

MySQL multi-table join reports an error: [Err] 1267-Illegal mix of collations (utf8_general_ci, IMPLICIT) and (utf8_unicode_ci, IMPLICIT) for operation '=

That is to say, the two tables have different sorting rules (COLLATION) and cannot be compared. COLLATION is used for sorting and size comparison. A character set has one or more COLLATION types and is case-insensitive, _ cs (case-sensitive), or _ bin (Binary) end. During the comparison, make sure that the character sorting of the two tables is the same. Generally, this parameter is not specified when you create a table. You can use the default value. It is the default value.

The table structure is as follows (utf8_general_ci is the default sorting rule of utf8_general_ci ):

mysql> show create table test.cs\G*************************** 1. row ***************************    Table: csCreate Table: CREATE TABLE `cs` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)

View the default sorting rule set of 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 the 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.

Changing from utf8.utf8 _ general_ci to utf8.utf8 _ unicode_ci does not support online ddl, as shown below:

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 use the following method to modify the character set, you will find that only the table-level is changed and the column-level is not changed.

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)

When changing the character set, do not 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 only the default character set of a 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)

We can find that the column character set has not changed, and only new columns inherit the table's character set by default (utf8.utf8 _ general_ci ).

Summary

The above is all about the analysis of different errors in MySQL table sorting rules. I hope it will be helpful to you. Interested friends can refer to: several important MySQL variables, MySQL declaration variables and stored procedure analysis, MySQL master database binlog (master-log) details of the link code with the slave database relay-log, and the principle of MySQL prepare. If you have any questions, you can leave a message at any time, communicate with each other, and make common progress.