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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.