MySQL字符集专题（字符集，校对，乱码）

1 MySQL字符集简介

    MySQL服务器可以支持多种字符集，在同一台服务器，同一个数据库，甚至同一个表的不同字段都可以指定使用不同的字符集，相比Oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，MySQL明显存在更大的灵活性。
    MySQL的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义MySQL存储字符串的方式，校对规则则是定义了比较字符串的方式，解决排序和字符分组的问题。字符集和校对规则是一对多的关系,每个字符集至少对应一个校对规则，MySQL支持39种字符集的将近200种校对规则。

    在MySQL中，字符集的概念和编码方案被看做是同义词，一个字符集是一个转换表和一个编码方案的组合。

    Unicode（Universal Code）是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。Unicode存在不同的编码方案，包括Utf-8，Utf-16和Utf-32。Utf表示Unicode Transformation Format。

2 查看字符集及校对

    2.1 查看所有字符集

[sql]  view plain  copy

1. mysql> show character set;  
2. mysql> select *  from information_schema.character_sets;  
3. mysql> select character_set_name, default_collate_name, description, maxlen  from information_schema.character_sets;  

    输出结果如下

[sql]  view plain  copy

1. +----------+-----------------------------+---------------------+--------+  
2. | Charset  | Description                 | Default collation   | Maxlen |  
3. +----------+-----------------------------+---------------------+--------+  
4. | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |  
5. | dec8     | DEC West European           | dec8_swedish_ci     |      1 |  
6. | cp850    | DOS West European           | cp850_general_ci    |      1 |  
7. | hp8      | HP West European            | hp8_english_ci      |      1 |  
8. | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |  
9. | latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |  
10. | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |  
11. | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |  
12. | ascii    | US ASCII                    | ascii_general_ci    |      1 |  
13. | ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |  
14. | sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |  
15. | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |  
16. | tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |  
17. | euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |  
18. | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |  
19. | gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |  
20. | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |  
21. | cp1250   | Windows Central European    | cp1250_general_ci   |      1 |  
22. | gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |  
23. | latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |  
24. | armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |  
25. | utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |  
26. | ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |  
27. | cp866    | DOS Russian                 | cp866_general_ci    |      1 |  
28. | keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |  
29. | macce    | Mac Central European        | macce_general_ci    |      1 |  
30. | macroman | Mac West European           | macroman_general_ci |      1 |  
31. | cp852    | DOS Central European        | cp852_general_ci    |      1 |  
32. | latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |  
33. | utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |  
34. | cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |  
35. | utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |  
36. | cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |  
37. | cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |  
38. | utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |  
39. | binary   | Binary pseudo charset       | binary              |      1 |  
40. | geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |  
41. | cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |  
42. | eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |  
43. +----------+-----------------------------+---------------------+--------+  

    其中：

    Charset：字符集名字。

    Description：对每个字符集的简短描述。

    Default collation：每个字符集的默认校对。

    Maxlen：每个字符集所保留的最大字节数。

    2.2 查看字符集可用的校对，如uft8

[sql]  view plain  copy

1. mysql> show collation like 'utf8%';  
2. mysql> select * from information_schema.collations where collation_name like 'utf8%';  

    输出结果如下

[sql]  view plain  copy

1. +--------------------------+--------------------+-----+------------+-------------+---------+  
2. | COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |  
3. +--------------------------+--------------------+-----+------------+-------------+---------+  
4. | utf8_general_ci          | utf8               |  33 | Yes        | Yes         |       1 |  
5. | utf8_bin                 | utf8               |  83 |            | Yes         |       1 |  
6. | utf8_unicode_ci          | utf8               | 192 |            | Yes         |       8 |  
7. | utf8_icelandic_ci        | utf8               | 193 |            | Yes         |       8 |  
8. | utf8_latvian_ci          | utf8               | 194 |            | Yes         |       8 |  
9. | utf8_romanian_ci         | utf8               | 195 |            | Yes         |       8 |  
10. | utf8_slovenian_ci        | utf8               | 196 |            | Yes         |       8 |  
11. | utf8_polish_ci           | utf8               | 197 |            | Yes         |       8 |  
12. | utf8_estonian_ci         | utf8               | 198 |            | Yes         |       8 |  
13. | utf8_spanish_ci          | utf8               | 199 |            | Yes         |       8 |  
14. | utf8_swedish_ci          | utf8               | 200 |            | Yes         |       8 |  
15. | utf8_turkish_ci          | utf8               | 201 |            | Yes         |       8 |  
16. | utf8_czech_ci            | utf8               | 202 |            | Yes         |       8 |  
17. | utf8_danish_ci           | utf8               | 203 |            | Yes         |       8 |  
18. | utf8_lithuanian_ci       | utf8               | 204 |            | Yes         |       8 |  
19. | utf8_slovak_ci           | utf8               | 205 |            | Yes         |       8 |  
20. | utf8_spanish2_ci         | utf8               | 206 |            | Yes         |       8 |  
21. | utf8_roman_ci            | utf8               | 207 |            | Yes         |       8 |  
22. | utf8_persian_ci          | utf8               | 208 |            | Yes         |       8 |  
23. | utf8_esperanto_ci        | utf8               | 209 |            | Yes         |       8 |  
24. | utf8_hungarian_ci        | utf8               | 210 |            | Yes         |       8 |  
25. | utf8_sinhala_ci          | utf8               | 211 |            | Yes         |       8 |  
26. | utf8_general_mysql500_ci | utf8               | 223 |            | Yes         |       1 |  
27. +--------------------------+--------------------+-----+------------+-------------+---------+  

    COLLATION_NAME：可使用的校对名字
    CHARACTER_SET_NAME：校对所属的字符集的名字
    ID：唯一的顺序号码
    IS_DEFAULT：表示校对是否这个字符集的默认校对
    IS_COMPILED：
    SORTLEN ：

    2.3 查看当前数据库字符集

[sql]  view plain  copy

1. mysql> show variables like 'character%';  

    输出结果如下

[sql]  view plain  copy

1. +--------------------------+----------------------------------+  
2. | Variable_name            | Value                            |  
3. +--------------------------+----------------------------------+  
4. | character_set_client     | utf8                             |  
5. | character_set_connection | utf8                             |  
6. | character_set_database   | utf8                             |  
7. | character_set_filesystem | binary                           |  
8. | character_set_results    | utf8                             |  
9. | character_set_server     | latin1                           |  
10. | character_set_system     | utf8                             |  
11. | character_sets_dir       | /usr/local/mysql/share/charsets/ |  
12. +--------------------------+----------------------------------+  

    名词解释：

    character_set_client：客户端请求数据的字符集
    character_set_connection：客户机/服务器连接的字符集
    character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
    character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的
    character_set_results：结果集，返回给客户端的字符集
    character_set_server：数据库服务器的默认字符集
    character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

    2.4 查看当前数据库字符集校对

[sql]  view plain  copy

1. mysql> show variables like 'collation%';  
2. +----------------------+-------------------+  
3. | Variable_name        | Value             |  
4. +----------------------+-------------------+  
5. | collation_connection | utf8_general_ci   |  
6. | collation_database   | utf8_general_ci   |  
7. | collation_server     | latin1_swedish_ci |  
8. +----------------------+-------------------+  

    名词解释：

    collation_connection 当前连接的字符集。
    collation_database    当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation_server变量的值。
    collation_server 服务器的默认校对。     

3 字符集，校对定义与分配

    默认字符集可以在3个级别定义，分别是 数据库服务器(server)级别， 数据库(database)级别， 数据表(table)级别。

    数据库服务器(server)级别是指在my.cnf（Linux）会my.ini（Windows）上修改。

    一旦显示的分配了一个字符集，它就不会改变，即使我们稍后在表级别，数据库级别或者数据库服务器级别修改默认字符集。

    3.1 给列分配字符集

    属于同一个表的不同列可以有不同的字符集，如果没有为一个列显示的定义字符集就使用默认字符集。创建一个表的时候，若显示的为列指定字符集，则字符集作为数据类型选项包含在其中，要放在数据类型后面及空指定和主键前面。

    例如：

[sql]  view plain  copy

1. create table colum_charset(  
2. c1 char(10) character set utf8 not null,    
3. c2 char(10) char set gbk,    
4. c3 varchar(10) charset gb2312,   
5. c4 varchar(10) );  

    其中：character set可以简写为char set和charset

    查看建表语句

[sql]  view plain  copy

1. mysql> show create table  colum_charset \G  
2. *************************** 1. row ***************************  
3.        Table: colum_charset  
4. Create Table: CREATE TABLE `colum_charset` (  
5.   `c1` char(10) CHARACTER SET utf8 NOT NULL,  
6.   `c2` char(10) CHARACTER SET gbk DEFAULT NULL,  
7.   `c3` varchar(10) CHARACTER SET gb2312 DEFAULT NULL,  
8.   `c4` varchar(10) DEFAULT NULL  
9. ) ENGINE=InnoDB DEFAULT CHARSET=latin1  

    校验列字符集是否生效（生效）

[sql]  view plain  copy

1. mysql> insert into colum_charset values("测试","测试","测试","测试");  
2. mysql> select * from colum_charset;  
3. +--------+--------+--------+------+  
4. | c1     | c2     | c3     | c4   |  
5. +--------+--------+--------+------+  
6. | 测试   | 测试   | 测试   | ??   |  
7. +--------+--------+--------+------+  

    3.2 给表指定字符集

    创建一个表，指定默认字符集为utf8

[sql]  view plain  copy

1. create table table_charset(c1 char(10), c2 char(10)) charset=utf8;  
2. 或者  
3. create table table_charset(c1 char(10), c2 char(10)) default charset=utf8;  

    查看建表语句

[sql]  view plain  copy

1. mysql> show create table table_charset \G  
2. *************************** 1. row ***************************  
3.        Table: table_charset  
4. Create Table: CREATE TABLE `table_charset` (  
5.   `c1` char(10) DEFAULT NULL,  
6.   `c2` char(10) DEFAULT NULL  
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

    3.3 给数据库指定字符集

    创建的每个数据库都有一个默认字符集，如果没有指定，就用latin1。

[sql]  view plain  copy

1. mysql> create database defaults;  
2. mysql> show create database defaults;  
3. +----------+---------------------------------------------------------------------+  
4. | Database | Create Database                                                     |  
5. +----------+---------------------------------------------------------------------+  
6. | defaults | CREATE DATABASE `defaults` /*!40100 DEFAULT CHARACTER SET latin1 */ |  
7. +----------+---------------------------------------------------------------------+  
8. mysql> create database specify default charset  utf8;  
9. mysql> show create database specify;  
10. +----------+------------------------------------------------------------------+  
11. | Database | Create Database                                                  |  
12. +----------+------------------------------------------------------------------+  
13. | specify  | CREATE DATABASE `specify` /*!40100 DEFAULT CHARACTER SET utf8 */ |  
14. +----------+------------------------------------------------------------------+  

    3.4 给列分配校对

    每个列都应该有一个校对，如果没有显示指定，MySQL就使用属于该字符集的默认校对。如果指定了一个字符集和一个校对，字符集应该放在前面。
    例如：显示指定列校对

[plain]  view plain  copy

1. create table column_collate(  
2. c1 char(10) charset utf8 collate utf8_romanian_ci not null,   
3. c2 varchar(10) charset utf8 collate utf8_spanish_ci);  

    获取校对信息

[plain]  view plain  copy

1. mysql> select table_name, column_name, collation_name from information_schema.columns where table_name = 'column_collate';  
2. mysql> select table_name, column_name, collation_name from information_schema.columns where table_name in ('column_collate');  
3. +----------------+-------------+------------------+  
4. | table_name     | column_name | collation_name   |  
5. +----------------+-------------+------------------+  
6. | column_collate | c1          | utf8_romanian_ci |  
7. | column_collate | c2          | utf8_spanish_ci  |  
8. +----------------+-------------+------------------+  

    注意：字符集和校对在处理字符表达式的过程中扮演着重要角色。 我们不能比较两个属于不同校对的不同字符值。

    例如：

[sql]  view plain  copy

1. mysql> insert into column_collate values ('A', 'A');     
2. mysql> select * from column_collate;  
3. +----+------+  
4. | c1 | c2   |  
5. +----+------+  
6. | A  | A    |  
7. +----+------+  
8. mysql> select * from column_collate where c1 = c2;  
9. ERROR 1267 (HY000): Illegal mix of collations (utf8_romanian_ci,IMPLICIT) and (utf8_spanish_ci,IMPLICIT) for operation '='  

    3.5 字符直接量的字符集

    如果没有显示指定，那么字符直接量的字符集就是数据库的默认字符集。如果要显示分配另一个字符集，需要把字符集的名字放在直接量前面，并且要在字符集前面加上下划线。

    例如：

[sql]  view plain  copy

1. mysql> select _utf8'语言 Language 言語 язык';  
2. +---------------------------------+  
3. | 语言 Language 言語 язык     |  
4. +---------------------------------+  
5. | 语言 Language 言語 язык     |  
6. +---------------------------------+     
7.   
8. mysql> select _latin1'语言 Language 言語 язык';  
9. +--------------------------------------------------------+  
10. | è¯­è¨€ Language è¨€èªž ÑÐ·Ñ‹Ðº                     |  
11. +--------------------------------------------------------+  
12. | è¯­è¨€ Language è¨€èªž ÑÐ·Ñ‹Ðº                     |  
13. +--------------------------------------------------------+  

4 修改和设置数据库服务器默认字符集

    MySQL服务器支持众多不同的字符集，这类字符集可在编译时和运行时指定。

    4.1 编译时指定

    编译时可指定默认字符集和默认校对规则，要想同时更改默认字符集和校对规则，要同时使用--with-charset和--with-collation选项。校对规则必须是字符集的合法校对规则。

[sql]  view plain  copy

1. ./configure -- with-charset=CHARSET --with-collation=COLLATION  

    通过configure选项--with-extra-charsets=LIST，可以定义在服务器中再定义 增加字符集 。LIST 指下面任何一项：
    1.空格间隔的一系列字符集名
    2.complex -，以包括不能动态装载的所有字符集
    3.all –，以将所有字符集包括进二进制

[sql]  view plain  copy

1. ./configure -- with-charset=CHARSET --with-collation=COLLATION --with-extra-charsets=all  

     4.2 在my.cnf或my.ini中指定

[sql]  view plain  copy

1. [mysqld]  
2. character_set_server=utf8  

    影响参数：character_set_server 和 character_set_database

    注意：修改后要重启数据库才能生效。

[sql]  view plain  copy

1. [client]  
2. default-character-set=utf8  

    影响参数：character_set_client，character_set_connection 和character_set_results。

    注意：修改后无需重启数据库。
    4.3 在启动参数前指定

[sql]  view plain  copy

1. ./mysqld --character-set-server=utf8 &  

    影响参数：character_set_server 和 character_set_database

    4.4 临时指定 

[sql]  view plain  copy

1. mysql> SET character_set_client = utf8;    
2. mysql> SET character_set_connection = utf8;     
3. mysql> SET character_set_database = utf8;     
4. mysql> SET character_set_results = utf8;      
5. mysql> SET character_set_server = utf8;   

    其中character_set_client，character_set_connection 和character_set_results，可通过一句话指定

[sql]  view plain  copy

1. mysql> SET NAMES 'utf8';  

5 字符集转换流程

    5.1 MySQL中的字符集转换过程
    a.MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
    b.进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
      使用每个数据字段的CHARACTER SET设定值；
      若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
      若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
      若上述值不存在，则使用character_set_server设定值。
    c.将操作结果从内部操作字符集转换为character_set_results。

        

6 数据库乱码案例及解决方案
    6.1 产生乱码的根本原因在于：
    a.客户机没有正确地设置client字符集，导致原先的SQL语句被转换成connection所指字符集，而这种转换，是会丢失信息的，如果client是utf8格式，那么如果转换成gb2312格式，这其中必定会丢失信息，反之则不会丢失。一定要保证connection的字符集大于client字符集才能保证转换不丢失信息。
    b. 数据库字体没有设置正确，如果数据库字体设置不正确，那么connection字符集转换成database字符集照样丢失编码，原因跟上面一样。

    6.2 乱码或数据丢失

    character_set_client:我们要告诉服务器，我给你发送的数据是什么编码？
    character_set_connection:告诉字符集转换器，转换成什么编码？
    character_set_results:查询的结果用什么编码？
    如果以上三者都为字符集N,可简写为set names 'N';

        6.2.1 乱码问题

        模拟情景1

        向默认字符集为utf8的数据表插入utf8编码的数据前连接字符集设置为latin1，查询时设置连接字符集为utf8。

        插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和character_set_results均为latin1；
        插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；
        查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码……

        例如：

[sql]  view plain  copy

1. mysql> set names latin1;  
2. mysql> create table temp(name varchar(10)) charset utf8;  
3. mysql> insert into temp values('中国');  
4. mysql> select * from temp;  
5. +--------+  
6. | name   |  
7. +--------+  
8. | 中国   |  
9. +--------+  
10. mysql> set names utf8;  
11. mysql> select * from temp;  
12. +---------------+  
13. | name          |  
14. +---------------+  
15. | ä¸­å›½        |  
16. +---------------+  

        注意：存储字符集编码比插入时字符集大时，如果原封不动返回数据会出现乱码，不过可通过修改查询字符集，避免乱码，即不会丢失数据。

        6.2.2 数据丢失问题

        模拟情景1

        向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8
        插入时根据连接字符集设置，character_set_client、character_set_connection和character_set_results均为utf8；
        插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的Unicode字 符，会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。

       例如：

[sql]  view plain  copy

1. mysql> set names utf8;  
2. mysql> create table temp(name varchar(10)) charset latin1;  
3. mysql> insert into temp values('中国');  
4. mysql> select * from temp;  
5. +------+  
6. | name |  
7. +------+  
8. | ??   |  
9. +------+  
10. mysql> set names latin1;  
11. mysql> select * from temp;  
12. +------+  
13. | name |  
14. +------+  
15. | ??   |  
16. +------+  

        数据不完整了，且无法恢复。

    6.3 乱码终极解决方案

        1.首先要明确你的客户端时候何种编码格式，这是最重要的（IE6一般用utf8，命令行一般是gbk，一般程序是gb2312)
        2.确保你的数据库使用utf8格式，很简单，所有编码通吃。
        3.一定要保证connection字符集大于等于client字符集，不然就会信息丢失，比如： latin1 < gb2312 < gbk < utf8，若设置set character_set_client = gb2312，那么至少connection的字符集要大于等于gb2312，否则就会丢失信息
        4.以上三步做正确的话，那么所有中文都被正确地转换成utf8格式存储进了数据库，为了适应不同的浏览器，不同的客户端，你可以修改character_set_results来以不同的编码显示中文字体，由于utf8是大方向，因此web应用是我还是倾向于使用utf8格式显示中文的。