2019年4月24日

MySQL/MariaDB中的collation是什麼？

在MySQL/MariaDB資料庫欄位中儲存文字資料時，會需要決定儲存時使用的文字編碼，比如說常見的utf8或是該被淘汰的big5都是一種文字編碼，這些編碼又被稱為character sets，字符集。在不同的字符集當中，對於人類所認知的同樣的一個字，會使用不同編碼儲存。

而collation，又稱為字符序，則決定了某個character sets中，每個字元的排序方式，也就是當進行比較的時候，哪個字元比較大？

比如說，在某個character sets中，a被編碼成00001，A被編碼成00002，而這個character set可能有N種不同的collation，其中某些collation中，可能會認定a = A，有些卻會認定a > A，有些則是認定a < A，雖然套用不同collation會造成不同的比較結果，但是卻不會影響編碼，我們拿到的a還是00001，A還是00002，而這樣的差異在應用時顯現在：

  1. 精確比較時
  2. 排序時

當我們需要對某些字串做精確比較的時候，比方說，我們想要挑選出名字為Devin Liang的使用者，

SELECT * FROM users WHERE name = 'Devin Liang';

在某些case insensitive的collation中（a=A)，會將DEVIN LIANG，DEeIn LiAnG等都select出來，這樣的結果就不對了。

而當我們要排序的時候，則會因為不同的collation對於字元的比較方式不一樣，而造成不一樣的結果

SELECT * FROM users ORDER BY name DESC;

不同的collation下的排序會有不同結果。

1. AAA
2. AAa
3. Aaa
1. Aaa
2. AAa
3. AAA

結論

資料庫中的character set決定文字的編碼方式、collation則決定文字的排序方式。在應用時，可以根據不同的需求挑選collation。

比如使用MySql/MariaDB時，一般情況下我們可能會直接使用utf8mb4_general_ci或utf8mb4_unicode_ci作為預設的編碼和排序，但是當需要做精確比對的時，例如需要滿足case sensitive，就必須要挑選其他的的collation，像是utf8mb4_bin，避免選出錯誤的結果。