Detailed explanation of the role of COLLATE in MYSQL and the differences between various COLLATEs

What is COLLATE in MYSQL?

Execute the show create table command in mysql, and you can see the table creation statement of a table. The example is as follows:

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

We can see most fields I understand, but what I want to discuss today is the COLLATE keyword. What does the corresponding utf8_unicode_ci behind this value mean? If you use this question to take the DBA exam during the interview, it should be able to stump most people.

What is COLLATE used for?

Development using phpmyadmin may look very familiar, because the Chinese header has already given the answer:

phpmyadmin screenshot

The so-called utf8_unicode_ci is actually a rule used for sorting. For those character type columns in mysql, such as VARCHAR, CHAR, and TEXT type columns, a COLLATE type is required to tell mysql how to sort and compare the column. In short, COLLATE will affect the order of the ORDER BY statement, the results filtered out by the greater than or less sign in the WHERE condition, and the **DISTINCT**, **GROUP BY**, and **HAVING** statements. query results. In addition, when MySQL builds an index, if the index column is of character type, it will also affect index creation, but we cannot perceive this impact. In short, wherever character type comparison or sorting is involved, it will be related to COLLATE.

The difference between various COLLATEs

COLLATE is usually related to data encoding (CHARSET). Generally speaking, each CHARSET has multiple COLLATEs it supports. , and each CHARSET specifies a COLLATE as the default value. For example, the default COLLATE for Latin1 encoding is latin1_swedish_ci, the default COLLATE for GBK encoding is gbk_chinese_ci, and the default value for utf8mb4 encoding is utf8mb4_general_ci.

Here is a digression. There are two encodings in mysql: utf8 and utf8mb4. In mysql, please forget **utf8** and always use **utf8mb4**. This is a legacy issue of MySQL. UTF8 in MySQL can only support character encodings with a maximum length of 3 bytes. For some text that needs to occupy 4 bytes, MySQL's UTF8 does not support it. You must use utf8mb4.

Many COLLATEs have the word _ci, which is the abbreviation of Case Insensitive, which means that "A" and "a" are treated equally when sorting and comparing. selection * from table1 where field1="a" can also select the value of field1 as "A". At the same time, for those COLLATEs with the _cs suffix, it is Case Sensitive, that is, case-sensitive.

Use the show collation command in mysql to view all COLLATEs supported by mysql. Taking utf8mb4 as an example, all COLLATEs supported by this encoding are as shown in the figure below.

All COLLATEs related to utf8mb4 in mysql

In the picture, we can see the collation rules of many countries' languages. The three commonly used ones in China are utf8mb4_general_ci (default), utf8mb4_unicode_ci, and utf8mb4_bin. Let’s explore the differences between these three:

First of all, the comparison method of utf8mb4_bin is to directly treat all characters as binary strings, and then compare them from the highest bit to the lowest bit. So obviously it's case sensitive.

There is actually no difference between utf8mb4_unicode_ci and utf8mb4_general_ci for Chinese and English. For the system we developed for domestic use, you can choose any one. It's just that for the letters in some Western countries, utf8mb4_unicode_ci is more in line with their language habits than utf8mb4_general_ci. General is an older standard of MySQL. For example, the German letter "ß" is equivalent to the two letters "ss" in utf8mb4_unicode_ci (this is in line with German habits), but in utf8mb4_general_ci, it is equivalent to the letter "s". However, the subtle differences between the two encodings are difficult to perceive for normal development. We rarely use text fields to sort directly. To take a step back, even if one or two letters are misaligned, can it really bring catastrophic consequences to the system? Judging from various posts and discussions found on the Internet, more people recommend using utf8mb4_unicode_ci, but they are not very resistant to systems that use the default value, and do not think there is any big problem. Conclusion: It is recommended to use utf8mb4_unicode_ci. For systems that already use utf8mb4_general_ci, there is no need to spend time modifying it.

Another thing to note is that starting from mysql 8.0, the default CHARSET of mysql is no longer Latin1, but has been changed to utf8mb4 (reference link), and the default COLLATE has also been changed to utf8mb4_0900_ai_ci. utf8mb4_0900_ai_ci is generally a further subdivision of unicode. 0900 refers to the number of the unicode comparison algorithm (Unicode Collation Algorithm version), and ai means accent insensitive (pronunciation is irrelevant). For example, e, è, é, ê and ë are treated equally. Related reference link 1, related reference link 2

COLLATE setting level and its priority

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

库级别设置COLLATE的语句如下：

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如

CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

推荐学习：《mysql视频教程》

The above is the detailed content of Detailed explanation of the role of COLLATE in MYSQL and the differences between various COLLATEs. For more information, please follow other related articles on the PHP Chinese website!