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:

  • ucs2:使用16bits来表示一个Unicode字符。
  • utf8:使用1~3bytes来表示一个Unicode字符。

选择哪个character set视情况而定,例如utf8表示latin字符只需要一个字节,所以当用户数据大部分为英文等拉丁字符时,使用utf8比较节省数据库的存储空间。据说SQL Server采用的是ucs2。

每个character set会对应一定数量的collation。查看方法是在MySQL的Console下输入:

show collation;

我们会看到这样的结果:

collation名字的规则可以归纳为这两类:

  • <character set>_<language/other>_<ci/cs>
  • <character set>_bin

例如:

utf8_danish_ci

ci是case insensitive的缩写,cs是case sensitive的缩写。即,指定大小写是否敏感。

utf8_bin是将字符串中的每一个字符用二进制数据存储,区分大小写。

奇怪的是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)

推荐使用

select * from pet where name = binary 'whistler';

这样可以保证当前字段的索引依然有效,而

select * from pet where binary name = 'whistler';

会使索引失效。

 

参考:

http://zhongwei-leg.iteye.com/blog/899227(以上内容转自此篇文章)

http://blog.sina.com.cn/s/blog_9707fac301016wxm.html

http://blog.csdn.net/haiross/article/details/51273593

http://www.jb51.net/article/40854.htm

http://blog.csdn.net/chenghuan1990/article/details/10078931

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

http://dev.MySQL.com/doc/refman/5.0/en/charset-Unicode-sets.html

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

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

 

posted @ 2017-12-28 00:01  EasonJim  阅读(17545)  评论(0编辑  收藏