[MySQL] 字符集的选择

MySQL服务器可以支持多种字符集，不同的字段都可以使用不同的字符集。

查看所有可用字符集：

show character set;

select * from information_schema.character_sets;

　　MySQL的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。其中字符集定义MySQL存储字符串的方式，校对规则用来定义比较字符串的方式。字符集和校对规则是一对多的关系。

注：目前线上服务器上用utf8mb4 和utf8mb4_bin。

　　校对规则可用 show collation like ‘xxx’ 查看。

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

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

1）服务器字符集和校对规则

• 在my.cnf中设置

[mysqld]

character-set-server=utf8mb4

• 在启动选项中指定：

mysqld --character-set-server=utf8mb4

• 或者在编译时指定：

shell> cmake . –DDEFAULT_CHARSET=utf8mb4

查看当前服务器的字符集和校对规则：

show variables like ‘character_set_server’;

show variables like ‘collation_server’;

如果没有设置则默认是latin 1字符集。（MySQl5.6版本）

2）数据库字符集和校对规则

在数据库创建的时候指定，也可以在创建完数据库后通过alter database 命令进行修改。

查看当前数据库字符集和校对规则：

show variables like ‘character_set_database’;

show variable like ‘collation_database’;

3）表字符集和校对规则

在建表的时候可以指定字符集和校对规则，也可以通过alter table 命令进行修改（一般不用）。如果没有指定，则使用数据库的规则。

查看表的字符集和校对规则：show create table table_name;

4）列字符集和校对规则

同上

5）连接字符集和校对规则

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

　　客户端和服务器的交互操作，MySQL提供了三个不同的character_set_client、character_set_connection和character_set_result,分别表客户端、连接和返回结果的字符集。通常情况下需要确保三者一样。可以用set names utf8mb4；命令来同时设置三个参数。

　　另一个简便方法：在my.cnf里设置客户端

[mysql]

default-character-set=utf8mb4

　　如果在应用开始阶段没有正确的选择字符集，在运行一段时间后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就要进行字符集的修改。字符集的修改不能直接通过 “alter database character set ‘xxx” 或者”alter table table_name character set xxx”命令进行调整，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。

　　字符集修改，需要先导出数据，经过适当的调整重新导入后才可完成。

下面模拟将latin1字符集的数据库修改成utf8mb4字符集的数据库。

1）导出表结构：

mysqldump –uroot –p –default-character-set=utf8mb4 –d database_name > createtab.sql；

-d 表示指导处表结构，不导出数据。

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

3）确保记录不在更新，导出所有记录（锁表导出）

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

--quick 用于转储大的表。他强制mysqldump从服务器一次一行地检索表中的行而不是一次检索所有行，并在输出前将他缓存在内存中。

--extended-insert 使用包括几个values列表的多行insert 语法。这样使转储文件更小，重载文件时可以加速插入。

--no-create-info 不导出建表语句

--default-character-set=latin1 按照原有的字符集导出所有数据

4）打开data.sql，将set names latin1修改成set names utf8mb4

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

create database databasename default charset utf8mb4;

6）创建表，执行createtab.sql

mysql –uroot –p database_name < createtab.sql

7）导入数据

mysql –uroot –p database_name < data.sql