Shulou(Shulou.com)06/01 Report--

What is the problem of reporting errors due to different sorting rules in the MySQL table? Many people don't know much about it. Today, in order to let you know more about the sorting rules of MySQL table, I summed up the following contents. Let's move on.

MySQL multiple join Times errors are as follows: [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, size comparison, a character set has one or more COLLATION and ends with _ ci (case-insensitive), _ cs (case-sensitive), or _ bin (binary). When making a comparison, you should make sure that the character order of the two tables is the same. In general, when building a table is not specified, you can take the default, all the default will be no problem. This paper mainly introduces the analysis of different errors in the sorting rules of MySQL table, hoping to help you.

Let's simulate various scenarios. The table structure is as follows (the default collation of utf8 is utf8_general_ci):

Mysql > show create table test.cs\ gateway * 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.01sec)

View table default collation 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 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 has been 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 don't forget to add CONVERT TO when you really change the character set, 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)

You can see that the column character set has not changed, and only new columns inherit the table's character set (utf8.utf8_general_ci) by default.

The above is a brief introduction of the problem caused by the different error reporting rules of the MySQL table. Of course, the differences in the detailed use of the above have to be understood by everyone. If you want to know more, welcome to follow the industry information channel!

During the Spring Festival this year, three new "cloud database" products and services were launched: cloud database MySQL, cloud database Redis and cloud database Memcached. Among them, cloud database MySQL is an "online database service" that is ready-to-use, stable, reliable and flexible, with multiple security protection measures and a perfect performance monitoring system, and provides professional database backup, recovery and optimization solutions, so that enterprises and users can focus more on application development and business development.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.