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

常用字符集

字符集 是否定长 编码方式 其他说明
ACSII 是 单字节7位编码 最早的奠基性字符集
ISO-8859-1/latin 1 是 单字节8位编码 西欧字符集，经常用来转码
GB 2312-80 是 双字节编码 早期标准，不推荐使用
GBK 是 双字节编码 不是国标，但是支持的系统不少
GB 18030 否 2字节或4字节编码 数据库支持的少见
UTF-32 是 4字节编码 US-4原始编码，目前很少采用
USC-2 是 2字节编码 Windows 2000内部使用
UTF-16 否 2字节或4字节编码 Java和Windows XP/BT内部使用
 UTF-8 否 1~4字节编码 互联网和UNIX/Linux广泛支持的Unicode字符集；Mysql Server也使用UTF-8

选择合适的字符集

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

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

  3. 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就选择双汉字定长编码的中文字符集，比如GBK。因为，相对于UTF-8而言，GBK比较“小”，每个汉字只占2个字节，而UTF-8汉字编码需要3个字节，减少磁盘I/O、数据库Cache以及网络传输的时间，提高性能。相反，如果应用主要处理英文字符，仅少量汉字数据，那么选择UTF-8更好，因为GBK、UCS-2、UTF-16的西文字符编码都是2个字符，会造成不必要的开销。

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

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

Mysql支持的字符集

Myql服务器可以指定多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以指定不同的字符集，相比Oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，Mysql灵活性更高。

  • 查看所有可用的字符集

    1
    mysql> show character set;

  • 查看所有的字符集和该字符集默认的校对规则

    1
    mysql> desc information_schema.character_set;

    校对规则(Collation)

    Mysql的字符集包括字符集(Character)和校对规则(Collation)两个概念。字符集定义Mysql存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系。每个字符集至少对应一个校对规则。

  • 查看校对规则

    1
    mysql> show Collation like '字符集名%';

  • 命名约定

    以其相关的字符集名开始，通常包括一个语言名，并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元，即比较是基于字符编码的值而与language无关)结束。

Mysql字符集设置

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

  • 服务器字符集和校对规则

    服务器字符集和校对规则可以在Mysql服务启动时确定，也可以在启动选项中指定，或者在编译时指定。

    • my.conf中设置

      1
      2
      3
      [mysqld]
      character-set-server = utf8
      collation-server = utf8_general_ci

    • 启动选项中指定

      1
      mysqld --character-set-server = utf8

    • 编译时指定

      1
      shell> cmake . -deafult_charset = utf8

      如果没有指定服务器字符集，那么默认使用latin1作为服务器字符集。

    • 查看当前服务器的字符集

      1
      2
      3
      4
      5
      6
      7
      mysql> show variables like 'character_set_server';
      +----------------------+-------+
      | Variable_name        | Value |
      +----------------------+-------+
      | character_set_server | utf8  |
      +----------------------+-------+
      1 row in set (0.01 sec)

    • 查看当前服务器的校对规则

      1
      2
      3
      4
      5
      6
      7
      mysql> show variables like 'collation_server';
      +------------------+-----------------+
      | Variable_name    | Value           |
      +------------------+-----------------+
      | collation_server | utf8_general_ci |
      +------------------+-----------------+
      1 row in set (0.00 sec)

  • 数据库字符集和校对规则

    数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过alter database命令进行修改。

    需要注意的是，如果数据库存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据内容。

    • 设置数据库字符集的规则

      1. 如果指定了字符集和校对规则，则使用指定的字符集和校对规则。
      2. 如果制定了字符集没有指定校对规则，则使用指定字符集的默认校对规则。
      3. 如果指定了校对规则没有指定字符集，则字符集使用与该校对规则关联的字符集。
      4. 如果没有指定字符集也没有指定校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。
      5. 推荐在创建数据库时明确指定字符集和校对规则，避免受到默认值的影响。

    • 查看当前数据库的字符集

      1
      2
      3
      4
      5
      6
      7
      mysql> show variables like 'character_set_database';
      +------------------------+-------+
      | Variable_name          | Value |
      +------------------------+-------+
      | character_set_database | utf8  |
      +------------------------+-------+
      1 row in set (0.03 sec)

    • 查看当前数据库的校对规则

      1
      2
      3
      4
      5
      6
      7
      mysql> show variables like 'collation_database';
      +--------------------+-----------------+
      | Variable_name      | Value           |
      +--------------------+-----------------+
      | collation_database | utf8_general_ci |
      +--------------------+-----------------+
      1 row in set (0.01 sec)

  • 表字符集和校对规则

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

    • 设置表的字符集的规则

      1. 如果指定了字符集和校对规则，使用指定的字符集和校对规则。
      2. 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则。
      3. 如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集。
      4. 如果没有指定字符集和校对规则，使用数据库字符集和校对规则最为表的字符集和校对规则。

      推荐在创建表的时候明确指明字符集和校对规则，以避免受到默认值的影响。

    • 显示表的字符集和校对规则

      1
      mysql> show create table <tablename>;

  • 列字符集和校对规则

    Mysql可以定义列级别的字符集和校对规则，主要是针对相同的表不同字符需要使用不同的字符集的情况。
    列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。

  • 连接字符集和校对规则

    上面4种设置方式，确定的是数据保存的字符集和校对规则，对于实际的应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。
    对于客户端和服务器的交互操作，Mysql提供了3个不同的参数：character_set_client、character_set_connection和character_set_results,分别代表客户端、连接和返回结果的字符集。

    通常情况下，这3个参数应该是相同的，才可以保证用户写入的数据可以正确的读出，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出。

    通常情况下，不会单独地设置这三个参数，可以通过以下命令设置连接的字符串和校对规则。

    1
    set names <character_set>;

    需要在每次数据库连接后都执行这个命令。

    另个更简便的方法，是在my.conf中设置。

    1
    2
    [mysql]
    default-character-set=<character_set>

字符集的修改

如果在应用开始阶段没有正确地设置字符集，在运行一段时间后才发现不能满足要求需要调整，又不想丢弃这段时间的数据，那么就需要进行字符集的修改。

字符集的修改操作不能直接通过“alter database character set ”或者“alter table character set ”命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。

已有记录的字符集调整，需要先将数据导出，经过适当调整重新导入后才可完成。