mysql 中 character set 與 collation 的理解

標籤：代碼   with   編碼   star   理解   too   位元組   如何   database   

使用 mysql 建立資料表的時候， 總免不了要涉及到 character set 和 collation 的概念， 之前不是很瞭解。

先來看看 character set 和 collation 的是什嗎？

character set， 即字元集。

我們常看到的 utf-8, GB2312, GB18030 都是相互獨立的 character set. 即對 Unicode 的一套編碼。

 

那麼如何理解 unicode 與 utf-8, GB2312 的區別呢？
打個比方，你眼前有一個蘋果，在英文裡稱之為 apple, 而在中文裡稱之為蘋果。
蘋果這個實體的概念就是 unicode , 而 utf-8, GB2312 可以認為就是不同語言對蘋果的不同稱謂，本質上都是在描述蘋果這個物。

 

collation, 即比對方法。

用於指定資料集如何排序，以及字串的比對規則。（這樣說可能比較抽象，後面會詳細解釋。）

 

character set 與 collation 的關係

 

軟體國際化是大勢所趨， 所以 unicode 是國際化最佳的選擇。當然為了提高效能，有些情況下還是使用 latin1 比較好。

 

mysql 有兩個支援 unicode 的 character set:

1. ucs2: 使用 16 bits 來表示一個 unicode 字元。

2. utf8: 使用 1~3 bytes 來表示一個 unicode 字元。

 

選擇哪個 character set 視情況而定，例如 utf8 表示 latin 字元只需要一個位元組，所以當使用者資料大部分為英文等拉丁字元時，使用 utf8 比較節省資料庫的儲存空間。據說 SQL Server 採用的是 ucs2， 我表示懷疑。

 

每個 character set 會對應一定數量的 collation. 查看方法是在 mysql 的 console 下輸入：

 

Java代碼

1. mysql> show collation;  

 

我們會看到這樣的結果：

 

collation 名字的規則可以歸納為這兩類：

1. <character set>_<language/other>_<ci/cs>

2. <character set>_bin

 

例如:

utf8_danish_ci

 

ci 是 case insensitive 的縮寫， cs 是 case sensitive 的縮寫。即，指定大小寫是否敏感。

奇怪的是 utf8 字元集對應的 collation 居然沒有一個是 cs 的。

 

 

那麼 utf8_general_ci, utf8_unicode_ci, utf8_danish_ci 有什麼區別? 他們各自存在的意義又是什嗎？

 

同一個 character set 的不同 collation 的區別在於排序、字元春對比的準確度（相同兩個字元在不同國家的語言中的定序可能是不同的）以及效能。

例如：

utf8_general_ci 在排序的準確度上要遜於 utf8_unicode_ci， 當然，對於英語使用者應該沒有什麼區別。但效能上（排序以及比對速度）要略優於 utf8_unicode_ci. 例如前者沒有對德語中

 

? = ss

 

的支援。

 

而 utf8_danish_ci 相比 utf8_unicode_ci 增加了對丹麥語的特殊排序支援。

 

 

補充：

 

1. 當表的 character set 是 latin1 時，若欄位類型為 nvarchar, 則欄位的字元集自動變為 utf8.

可見 database character set, table character set, field character set 可逐級覆蓋，類似於物件導向中子類繼承父類，重寫父類的方法。

 

2. 在 ci 的 collation 下，如何在比對時區分大小寫：

mysql> select * from pet;+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || whistler | Gwen | bird | NULL | 1988-09-25 | NULL |+----------+-------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql> select * from pet where name = ‘whistler‘;+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || whistler | Gwen | bird | NULL | 1988-09-25 | NULL |+----------+-------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql> select * from pet where binary name = ‘whistler‘;+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| whistler | Gwen | bird | NULL | 1988-09-25 | NULL |+----------+-------+---------+------+------------+-------+1 row in set (0.00 sec)mysql> select * from pet where name = binary ‘whistler‘;+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| whistler | Gwen | bird | NULL | 1988-09-25 | NULL |+----------+-------+---------+------+------------+-------+1 row in set (0.00 sec)

 

推薦使用

 

mysql> select * from pet where name = binary ‘whistler‘;

 

這樣可以保證當前欄位的索引依然有效， 而

mysql> select * from pet where binary name = ‘whistler‘;

會使索引失效。

 

 原文連結：http://www.360doc.com/content/11/0303/01/2588264_97631236.shtml

 

參考列表：

1. What is the best collation to use for mysql with php.

http://stackoverflow.com/questions/367711/what-is-the-best-collation-to-use-for-mysql-with-php

 

2. Unicode Character Sets

http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

 

3. Show Collation Syntax

http://dev.mysql.com/doc/refman/5.1/en/show-collation.html

 

4. The Binary Operator

http://dev.mysql.com/doc/refman/5.1/en/charset-binary-op.html

mysql 中 character set 與 collation 的理解