Collation 主要的作用是什麼，排序。資料庫中的字元眾多，而在這裡很多的查詢中都對這些符號進行一些比對的工作，如 A = a , B > BA , c < v 等等在查詢中進行的條件輸入的工作，而字元和字元之間如何進行比對，這個就全部依靠我們的collation 了，如我們規定了 A = 0 B = 1 則， B > A 是成立的，所有collation是一套字元的編碼集合，collation會影響到order by的語句順序，會影響到where 條件比對後的結果，同時也會影響distinct, group by , having 等語句查詢的結果，不光如此，還會影響字元型的欄位建立索引後的順序等。

以下我們以 MYSQL 8.030版本作為操作的物件

show character set;

請注意charset 與 collation 之間的對應關係（預設值）

其中utf8mb4中就有眾多的collation可以被支援

基於以上的問題，我們已經瞭解到collation的重要性，他是一個規則，滿足資料庫中表的資料進行比較和排序的重要標記屬性。這些設定與字母大小寫是否敏感或者一些特殊國家的語言的重音符號等都有關係。

下面我們帶著幾個問題，來看MYSQL 的COLLATION 的問題

問題1 ，怎麼能讓我的資料庫在比對英文大小寫的時候，能產生差異

create table change_letter_i (id int primary key,letters varchar(20)) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

create table change_letter_c (id int primary key,letters varchar(20))engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_as_cs;

從下圖我們可以清晰的分析出，兩個表在使用了不同的collation 後的在對比字元的比對情況

在我們使用了 utf8mb4_0900_ai_ci 後，我們的字元比對中，大小寫英文是等同的，而在使用了utf8mb4_0900_as_cs 後，我們大小寫之間的比對是敏感的。

問題 2 在MYSQL 中兩個不同 collation的表對比字元會產生什麼結果？

從下面的截圖可以清晰的看到，兩個同樣的字符集都是 utf8mb4的情況下，但是他們的collation 是不同的情況下，是無法進行比對的。

mysql> select * from change_letter_c as c inner join change_letter_i as i on c.letters = i.letters;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_as_cs,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

兩個表雖然字符集相同，但是collation不同，在這樣的情況下，兩個表是不能關聯查詢的，如果查詢會報錯誤提示。

問題 3 collation可以更換嗎？

這是一個好的問題，因為collation的是一個在不同層級都存在的部分，在問可以不可以更換的前提條件是，你要更換哪個層次的collation

這裡我們的collation 分為以下幾個等級

1 例項級別

2 庫級別
3 表級別
4 列級別
5 SQL 級別

那麼我們帶著以上的層次的問題，來對這些collation進行更替

1 確認當前的資料庫伺服器的instance 中的collation

show variables like 'collation_connection';

set collation_connection = 'utf8mb4_0900_as_cs';

這裡可以在配置檔案或者動態的方式來對資料庫例項來進行設定，但是在其他的層級都設定了自己的collation的情況下，這個collation 是不起作用的。

2 庫級別

在MYSQL的資料庫級別中，是可以對資料庫本身進行collation的設定的
create database test default character set utf8mb4 collate utf8mb4_0900_ai_ci;

在表級別沒有設定預設的collation的情況下，則按照庫的級別來進行collation的設定。

3 表級別

上面的實驗我們做過了，略過

4 列級別

這個列級別的實用性，僅僅次於表級別

舉例

我們針對不同的列設定不同的collation

MYSQL collation 選好還能換嗎

上面的例子給我們一個很好的解釋，collation 是可以隨意設定的，級別越小越具有自己對自己管轄的部分具有collation的決定權。

上面的列子也說明在一個表中，如果有不同的對於大小寫敏感度的不同需要，可以一個表中的不同欄位具有不同的collation 。

但是需要注意的，如果有多表連線，則對應的collation必須一致。

5 SQL 級別

我們來看看SQL的級別的 COLLATION到底是要做什麼

MYSQL collation 選好還能換嗎

select distinct letters from change_letter_ci;

select distinct letters collate utf8mb4_0900_as_cs from change_letter_ci;

以上兩個SQL 可以看出，雖然我們的原有列大小寫是不敏感的，但是在我們針對查詢中，指出，這個列要大小寫敏感的情況下，查詢的結果是根據SQL 最終的collation 設定進行查詢結果的輸出的。

透過以上的介紹，我們可以聯想出，在一些中文字元的比較中，可以針對中午的特殊的列進行特殊的 collation的設定。

簡單對collation的字尾進行一個介紹，ci 結尾的是代表大小寫不敏感，而cs結尾的說明大小寫敏感。具體一些更多的不同點請參照MYSQL的官方文件部分。

MYSQL collation 選好還能換嗎

相關文章