MySQL 字符集

字符集是一套文字符号及其编码，比较规则的集合

字符集	是否定长	编码方式	其他说明
ACSII	是	单字节 7 位编码	最早的奠基性字符集
ISO-8895-1/Latin 1	是	单字节 8 位编码	西欧字符集，经常被一些程序员用来转码
GB 2312-80	是	双字节编码	早期标准，不推荐再使用
GBK	是	双字节编码	虽然不是国际，但支持的系统不少
GB 18030	否	2 字节或 4 字节编码	开始有一些支持，但数据库支持的还少见
UTF-32	是	4 字节编码	UCS-4 原始编码，目前很少采用
UCS-2	是	2 字节编码	Windows 2000 内部使用 UCS-2
UTF-16	否	2 字节或 4 字节编码	Java 和 Windows XP/NT 等内部使用 UTF-16
UTF-8	否	1 ~ 4 字节编码	互联网和 UNIX/Linux 广泛支持的 Unicode 字符集；MySQLServer 也使用 UTF-8

1. 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不用语言的国家或地区，就应该选择 Unicode 字符集。对 MySQL 来说，就是 UTF-8

2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性

3. 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK

4. 如果数据库需要做大量的字符运算，如比较，排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快

5. 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失

6. 查看所有可用的字符集和该字符集默认的校队规则的命令是 show character set 或者查看 information_schema.character_sets

7. MySQL 的字符集包括字符集（character）和校对规则（collation）两个概念

   其中字符集用来定义 MySQL 存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系，MySQL 支持 30 多种字符集的 70 多种校对规则

   每个字符集至少对应一个校对规则。可以用 show collation like '***'; 命令，或者通过系统表 information_schema.collations 来查看相关字符集的校对规则

   校对规则命令约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以 _ci（大小写不敏感），_cs（大小写敏感），或 _bin（二元，即比较是基于字符编码的值而与 language 无关）结束

8. 字符集和校对规则有 4 个级别的默认设置：服务器级，数据库级，表级和字段级

   服务器字符集和校对规则

   服务器字符集和校对规则，可以在 MySQL 服务启动的时候确认

   • 可以在 my.cnf 中设置

       [mysqld]
       character-set-server=gbk
     

   • 或者在启动选项中指定

       mysqld --character-set-sever=gbk
     

   • 或者在编译时指定

       shell> cmake . -DDEFAULT_CHARACTER=gbk
     

   如果没有特别的指定服务器字符集，那么默认使用 Latin1 作为服务器字符集。上面 3 种设置的方式都只指定了字符集，没有指定校对规则，这样意味着使用该字符集默认的校对规则。如果要使用该字符集的非默认校对规则，则需要在指定字符集的同时指定校对规则

   可以使用 show variables like 'character_set_server; 和 show variables like 'collation_server' 命令查询当前服务器的字符集和校对规则

   数据库字符集和校对规则

   数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过 alter database 命令进行修改。需要注意的是，如果数据库里已经存放数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容

   设置数据库字符集的规则如下

   • 如果指定了字符集和校对规则，则使用指定的字符集和校对规则
   • 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则
   • 如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集
   • 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则

   推荐在创建数据库时明确指定字符集和校对规则，避免受到默认值的影响。要显示当前数据库的字符集和校对规则，可以使用 show variables like 'character_set_database' 和 show variables like 'collation_database' 命令查看

   表字符集和校对规则

   表的字符集和校对规则在创建表的时候指定，可以通过 alter table 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集

   如果没有指定字符集和校对规则，使用数据库字符集和校对规则表位表的字符集和校对规则

   推荐在创建表的时候明确指定字符集和校对规则，以避免收到默认值的影响。要显示表的字符集和校对规则，可以使用 show create table tablename 命令查看

   列字符集和校对规则

   MySQL 可以定义列级别的字符集和校对规则，主要是针对相同的表不同字段需要使用不同字符集的情况，应该说一般遇到这种情况的几率比较小，这只是 MySQL 提供给我们一个灵活设置的手段

   列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则

9. 连接字符集和校对规则

   上面 4 中设置方式，确定的时数据保存的字符集和校对规则，对于实际的应用访问来说，还存在客户端和服务器之间的字符集和校对规则的设置

   对于客户端和服务器的交互操作，MySQL 提供了 3 个不同的参数：character_set_client，character_set_connection 和 character_set_results，分别代表客户端，连接和返回结果的字符集。通常情况下，这 3 个字符集应该时相同的，才可以确保用户写入的数据可以正确的独处，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出

   通常情况下，不会单个的设置这 3 个参数，可以通过命令 set names ***; 来设置连接的字符集和校对规则，这个命令可以同时修改这 3 个参数的值。使用这个方法修改连接的字符集和校对股则，需要应该每次连接数据库后都执行这个命令

   另一个更简便的办法，是在 my.cnf 中设置以下语句

   [mysql]
   default-character-set=gbk
   

   这样服务器启动后，所有连接默认都是使用 gbk 字符集进行连接的，而不需要在程序中执行 set names 命令。另外，字符串常量的字符集也是由 character_set_connection 参数来指定的

   可以通过 [_character_name]'string'[COLLATE collation_name] 命令强制字符串的字符集和校对规则

10. 字符集的修改步骤

    字符集的修改不能直接通过 alter database character set *** 或者 alter table character set *** 命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成

    以下模拟的是将 latin1 字符集的数据库修改成 GBK 字符集的数据库的过程

    1. 导出表结构：

        mysqldump -uroot -p --default-character-set=gbk -d databasename> createtab.sql
       

       其中 --default-character-set=gbk 表示设置以什么字符集连接
       -d 表示只导出表结构，不导出数据

    2. 手工修改 createtab.sql 中表结构定义中的字符集为新的字符集

    3. 确保记录不再更新，导出所有记录

        mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename> data.sql
       

       --quick：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中
       --extended-insert：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小，重载文件时可以加速插入
       --no-create-info：不导出每个转储表的 CREATE TABLE 语句。
       --default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

    4. 打开 data.sql，将 SET NAMES latin1 修改成 SET NAMES gbk

    5. 使用新的字符集创建新的数据库

        create database databasename default charset gbk;
       

    6. 创建表，执行 createtab.sql

        mysql -uroot -p databasename < createtab.sql
       

    7. 导入数据，执行 data.sql

        mysql -uroot -p databasename < data.sql
       

       注意：选择目标字符集的时候，要注意最好是源字符集的超级，或者确定比源字符集的字库更大，否则如果目标字符集的字库小于源字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据