mysql的collat​​e什麼意思

mysql的collat​​e是校對集的意思，可以理解為排序規則。在mysql中，儲存的字元資料可以有不同的排序規則，這取決於所使用的字元集和collat​​e規則。字符集決定了可以儲存的字符的種類，而collat​​e規則決定了這些字符的排序方式。

本教學作業系統：Windows10系統、mysql 8.0版本、Dell G3電腦。

mysql COLLATE是校對集的意思，可以理解為排序規則等。

在MySQL中，儲存的字元資料可以有不同的排序規則，這取決於所使用的字元集和collat​​e規則。字符集決定了可以儲存的字符的種類，而collat​​e規則則決定了這些字符的排序方式。

通常情況下，在建立資料庫或表格時，可以透過指定不同的字元集和collat​​e規則來設定字元資料的排序方式。如果沒有指定特定的collat​​e規則，MySQL將使用預設的collat​​e規則。常見的字元集和collat​​e規則包括utf8mb4_general_ci (不區分大小寫，不區分重音符號)、utf8mb4_unicode_ci (不區分大小寫，區分重音符號)等。

collat​​e規則的選擇是根據具體的需求來決定的。不同的collat​​e規則適用於不同的場景。例如，在搜尋和排序資料時，某些collat​​e規則會進行區分大小寫的比較，而其他規則則不會。因此，在設計資料庫時，應根據實際需求和業務規則來選擇合適的collat​​e規則。

透過使用不同的collat​​e規則，可以實現各種排序方式。例如，可以對字元資料進行不區分大小寫的排序，這意味著"A"和"a"將被視為相等。相反，如果使用了區分大小寫的collat​​e規則，那麼"A"和"a"將被視為不同的字元。

此外，collat​​e規則還可以影響字串的比較操作。在不同的collat​​e規則下，字元之間的比較結果可能會有所不同。例如，在某些collat​​e規則下，字母"a"可能被視為大於字母"Z"，而在其他規則下則相反。

要注意的是，collat​​e規則不僅適用於字元資料的排序和比較，也適用於查詢中使用字串函數和運算子時的結果。因此，在編寫SQL查詢時，應考慮所使用的collat​​e規則，以確保得到預期的結果。

在mysql中執行show create table 指令，可以看到一張表的建表語句，example如下：

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;

大部分欄位我們都能看懂，但是今天要討論的是COLLATE關鍵字。這個值後面對應的utf8_unicode_ci是什麼意思呢？面試的時候用這題目考一考DBA，應該可以難倒一大部分人。

COLLATE是用來做什麼的？

使用phpmyadmin的開發可能會非常眼熟，因為其中的中文表頭已經給出了答案：

所謂utf8_unicode_ci ，其實是用來排序的規則。對於mysql中那些字元類型的列，如VARCHAR，CHAR，TEXT類型的列，都需要有一個COLLATE類型來告知mysql如何對該列進行排序和比較。簡言之，COLLATE會影響到ORDER BY語句的順序，會影響到WHERE條件中大於小於號篩選出來的結果，會影響**DISTINCT**、**GROUP BY**、**HAVING**語句的查詢結果。另外，mysql建索引的時候，如果索引列是字元類型，也會影響索引創建，只不過這種影響我們感知不到。總之，凡是涉及字元類型比較或排序的地方，都會和COLLATE有關。

各種COLLATE的區別

COLLATE通常是和資料編碼（CHARSET）相關的，一般來說每種CHARSET都有多種它所支援的COLLATE，並且每種CHARSET都指定一種COLLATE為預設值。例如Latin1編碼的預設COLLATE為latin1_swedish_ci，GBK編碼的預設COLLATE為gbk_chinese_ci，utf8mb4編碼的預設值為utf8mb4_general_ci。

這裡順便講個題外話，mysql有utf8和utf8mb4兩種編碼，在mysql請大家忘記**utf8**，永遠使用**utf8mb4**。這是mysql的遺留問題，mysql中的utf8最多只能支援3bytes長度的字元編碼，對於一些需要佔據4bytes的文字，mysql的utf8就不支援了，要使用utf8mb4才行。

很多COLLATE都帶有_ci字樣，這是Case Insensitive的縮寫，即大小寫無關，也就是說"A"和"a"在排序和比較的時候是一視同仁的。 selection * from table1 where field1="a"同樣可以把field1為"A"的值選出來。同時，對於那些_cs後綴的COLLATE，則是Case Sensitive，即大小寫敏感的。

在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以utf8mb4为例，该编码所支持的所有COLLATE如下图所示。

imgmysql中和utf8mb4相关的所有COLLATE

图中我们能看到很多国家的语言自己的排序规则。在国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。我们来探究一下这三个的区别：

首先utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。

而utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。对于我们开发的国内使用的系统来说，随便选哪个都行。只是对于某些西方国家的字母来说，utf8mb4_unicode_ci会比utf8mb4_general_ci更符合他们的语言习惯一些，general是mysql一个比较老的标准了。例如，德语字母“ß”，在utf8mb4_unicode_ci中是等价于"ss"两个字母的（这是符合德国人习惯的做法），而在utf8mb4_general_ci中，它却和字母“s”等价。不过，这两种编码的那些微小的区别，对于正常的开发来说，很难感知到。本身我们也很少直接用文字字段去排序，退一步说，即使这个字母排错了一两个，真的能给系统带来灾难性后果么？从网上找的各种帖子讨论来说，更多人推荐使用utf8mb4_unicode_ci，但是对于使用了默认值的系统，也并没有非常排斥，并不认为有什么大问题。结论：推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。

另外需要注意的一点是，从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（ Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê 和 ë是一视同仁的。相关参考链接1，相关参考链接2

COLLATE设置级别及其优先级

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

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

CREATE DATABASE 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的collat​​e什麼意思的詳細內容。更多資訊請關注PHP中文網其他相關文章！