MySQL之charset和collation

使用 mysql 创建数据表的时候, 总免不了要涉及到 character set 和 collation 的概念, 之前不是很了解。今天朋友突然问到,就自己查了下整理下。

character set和collation

character set

character set, 即字符集,常见的有utf-8,Unicode,GB2312

UTF8,Unicode,GB2312区分与联系
  • Unicode:统一字符编码,让世界上所有字符都对应一个编码。
  • UTF8:Unicode最初设计想用2个字节表示所有字符,但是最多只能表示65535个,这个称为UCS-2,当字符数超过这个范围,就需要用四个字节来表示了,即UCS-4。但是早期的人类缺乏先见之明,只考虑了ASCII码。单字节的0被当做了一种非常特殊的字符:字符串终结符。如果采用UCS-2或者UCS-4,Unix中很多工具将无法使用。为了解决这个问题,就需要采用一些编码技术,把Unicode映射到一个不包含0byte的编码空间中,绕开0这个东西,目标编码,就是UTF8。
  • GB2312:它是和Unicode地位相等的。也就是说,它和UTF8、Unicode没有任何关系。它主要针对中文字符。
  • 1.UCS代表Universal Character Set通用字符集
  • 2.UTF代表UCS Transformation Format
mysql 有两个支持 unicode 的 character set:
  1. ucs2: 使用 16 bits 来表示一个 unicode 字符。
  2. utf8: 使用 1~3 bytes 来表示一个 unicode 字符。

collation

collation, 即比对方法。用于指定数据集如何排序,以及字符串的比对规则。

Collation相关概念
  1. ci: case insensitive,比较的时候不区分大小写

    在 ci 的 collation 下,如何在比对时区分大小写?

    推荐使用

    1
    2
    mysql> select * from pet where name = binary 'whistler';
    ```这样可以保证当前字段的索引依然有效, 而
    mysql> select * from pet where binary name = 'whistler';
     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    2. mb4: multi-bytes-4,4字节字符集
    3. utf8mb4\_unicode\_ci: utf8mb4表示编码规则为支持4个字节字符的utf8,unicode表示采用Unicode编码
    #### character set和collation的关系
    每个 character set 会对应一定数量的 collation。同一个 character set 的不同 collation 的区别在于排序、字符春对比的准确度(相同两个字符在不同国家的语言中的排序规则可能是不同的)以及性能。
    例如:utf8\_general\_ci 在排序的准确度上要逊于 utf8\_unicode\_ci, 当然,对于英语用户应该没有什么区别。但性能上(排序以及比对速度)要略优于 utf8\_unicode\_ci. 例如前者没有对德语中`ß = ss`的支持。而 utf8\_danish\_ci 相比 utf8\_unicode\_ci 增加了对丹麦语的特殊排序支持。
    #### character set和collation的设置
    > charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和连接级
    1. 服务器级
    查看设置:

    show global variables like ‘character_set_server’;
    show global variables like ‘collation_server’;

    1
    修改:

    vim /etc/my.cnf
    [mysqld]
    character_set_server=utf8
    collation_server=utf8_general_ci

    1
    2
    3
    2. 数据库级
    查看设置:

    select * from information_schema.schemata where schema_name = ‘cookbook’;

    1
    2
    3
    设置:
    1.若没有显式设置,则自动使用服务器级的配置
    2.显式设置:在创建库时指定

    create database playUtf8 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

    1
    2
    3
    3. 表级
    查看设置:

    show create table course;

    1
    2
    3
    设置:
    1.若没有显式设置,则自动使用数据库级的配置
    2.显式设置:在创建表时指定

    create table utf ( id int ) default charset=utf8 default collate=utf8_bin;

    1
    2
    3
    4
    4. 列级
    查看设置:

    show create table course;

    1
    2
    3
    4
    5
    6
    设置:
    1. 若没有显式设置,则自动使用表级的配置。
    2. 显式设置:

    CREATE TABLE Table1(column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);
    ```

  2. Connection字符编码

connector/j传输SQL时用什么编码?

在连接时会先查询服务器端的character_set_server值,再确定连接将使用的编码,可在用于连接到服务器的URL中使用“characterEncoding”属性。

参考链接

我知道是不会有人点的,但万一有人想不开呢!