mysql字符集和校对规则选择

mysql字符集和校对规则选择

目录

• mysql字符集和校对规则选择
  • 基本概念
    • 字符（Character）
    • 字符集（Character set）
    • 字符编码（Character encoding）
    • 校对规则（Collation）
    • 总结
  • 设置各种字符集
    • 1. 服务器级
    • 2. 数据库级
    • 4. 列字符集和校队规则
    • 连接字符集和校对规则
  • MySQL选择合适的字符集
  • 不同的校验规则对数据的影响

常用字符集表格

基本概念

在讲解字符集和校对规则之前，我们先来简单了解一下字符、字符集和字符编码。

字符（Character）

是计算机中字母、数字、符号的统称，一个字符可以是一个中文汉字、一个英文字母、一个阿拉伯数字、一个标点符号等。

计算机是以二进制的形式来存储数据的。平时我们在显示器上看到的数字、英文、标点符号、汉字等字符都是二进制数转换之后的结果。

字符集（Character set）

定义了字符和二进制的对应关系，为字符分配了唯一的编号。常见的字符集有 ASCII、GBK、IOS-8859-1 等。

字符编码（Character encoding）

也可以称为字集码，规定了如何将字符的编号存储到计算机中。

大部分字符集都只对应一种字符编码，例如：ASCII、IOS-8859-1、GB2312、GBK，都是既表示了字符集又表示了对应的字符编码。
所以一般情况下，可以将两者视为同义词。Unicode 字符集除外，Unicode 有三种编码方案，即 UTF-8、UTF-16 和 UTF-32。最为常用的是 UTF-8 编码。

校对规则（Collation）

也可以称为排序规则，是指在同一个字符集内字符之间的比较规则。字符集和校对规则是一对多的关系，每个字符集都有一个默认的校对规则。字符集和校对规则相辅相成，相互依赖关联。

总结

简单来说，字符集用来定义 MySQL 存储字符串的方式，校对规则用来定义 MySQL 比较字符串的方式。

有些数据库并没有清晰的区分开字符集和校对规则。例如，在 SQL Server 中创建数据库时，选择字符集就相当于选定了字符集和校对规则。

而在 MySQL 中，字符集和校对规则是区分开的，必须设置字符集和校对规则。一般情况下，没有特殊需求，只设置其一即可。只设置字符集时，MySQL 会将校对规则设置为字符集中对应的默认校对规则。

可以通过SHOW VARIABLES LIKE 'character%';命令查看当前 MySQL 使用的字符集，命令和运行结果如下：

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

查看mysql支持的字符集
show character set；

上述运行结果说明如下表所示：

乱码时，不需要关心 character_set_filesystem、character_set_system 和 character_sets_dir 这 3 个系统变量，它们不会影响乱码 。

可以通过SHOW VARIABLES LIKE 'collation_%';命令查看当前 MySQL 使用的校对规则，命令和运行结果如下

mysql> SHOW VARIABLES LIKE 'collation\_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | gbk_chinese_ci  |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

查看某个字符集支持的校对规则
show collation like 'gbk%';

对上述运行结果说明如下：

• collation_connection：连接数据库时使用的校对规则
• collation_database：创建数据库时使用的校对规则
• collation_server：MySQL 服务器使用的校对规则

设置各种字符集

1. 服务器级

① 查看服务器级的字符集和校队规则

show variables like 'character_set_server';
show variables like 'collation_set_server';

②设置服务器级字符集与校队规则
1.可以在my.cnf中设置,会在mysql服务启动的时候确定

[mysqld]
character-set-server=gbk

2.或者在启动选项中指定

mysqld --character-set-server=gbk

3.或者在编译时指定(源码安装mysql)

cmake . --DDEFAULT_CHARSET=gbk

2. 数据库级

数据库的字符集和校对规则可以在创建表的时候指定，也可以创建数据库后通过alter database命令进行修改，如果数据库中已经存在数据，则修改字符集不会对原有记录造成影响，原有的记录依然按照以前的字符集存放

① 在创建数据库时指定字符集和校队规则

CREATE DATABASE IF NOT EXISTS test_dbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

② 修改已存在的数据库的字符集和校队规则

alter database test_dbname DEFAULT CHARACTER SET utf8  collate utf8_general_ci;

③ 查看数据库字符集和校队规则

show variables like 'character_set_database';
show variables like 'collation_database';

3. 数据表级
   表的字符集和校对规则可以在创建表的时候指定，也可以后期通过alter table命令进行修改，如果表中原来已有记录，则修改字符集不会对原有记录造成影响，原有的记录依然按照以前的字符集存放
   ① 创建数据表时指定字符集

create table test_tablename (
id int not null,
)default charset=utf8;

② 修改数据表的字符集

ALTER TABLE test_tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

③ 查看数据表的字符集

show create table test_tablename \G;

4. 列字符集和校队规则

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

列字符集可以在创建表时指定，也可以在后期通过修改表来调整，如果在创建表的时候没有指定字符集和校队规则，则默认使用表的字符集和校对规则
① 创建的时候指定列的字符集和校验规则

create table test_tablename (
id int CHARACTER SET utf8 COLLATE utf8_general_ci not null,
)default charset=utf8;

② 修改列的字符集

ALTER TABLE test_tablename CHANGE test_title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

连接字符集和校对规则

上面四种设置方式仅仅是针对于数据在数据库中保存时使用的字符集和校对规则，而对于实际的应用程序来说，还存在客户端和服务器之间交互的字符集和校对规则的设置，对于客户端和服务器的交互操作，mysql提供了三个不同的参数：
character_set_client 、 character_set_connection、character_set_results,分别代表客户端、连接和返回结果的字符集，通常情况下，这三个设置应该是相同的，才可以确保用户写入的数据被正确读出

可以通过一个命令同时设置这三个参数

set NAMES ***；

这个命令可以同时修改这三个参数的值，或者在配置文件my.cnf中设置

[mysql]
default-character-set=gbk

三. 注意事项
① 如果指定了字符集和校对规则，使用指定的字符集和校对规则
如果指定了字符集但没有设置校对规则，使用指定字符集的默认校对规则
如果指定了校对规则但未指定字符集，则字符集使用与该校对规则相关联的字符集
如果没有指定字符集和校对规则，使用数据库默认字符集和校对规则作为表的字符集和校对规则
② 相对于utf8而言，gbk比较小，每个汉字占两个字节，而utf汉字编码则需要三个字节

utf8属于Unicode 编码
IOS International Organization for Standization

MySQL选择合适的字符集

对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储，处理性能，以及日后系统的移植，推广都会有影响。
MySQL5.6目前支持几十种字符集，包括UCS-2，UTF-16，UTF-16LE,UTF-32，UTF-8和utf8mb4等Unicode字符集。
根据应用的需求，考虑以下几方面的因素。

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

2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是GBK文字，如果选择GB2312-80为数据库字符集，就很有可能出现某些文字无法正确导入的问题

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

4. 如果数据库需要做大量的字符运算，如比较，排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
   如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集，这样可以避免因字符集转换带来的性能开销和数据损失。

不同的校验规则对数据的影响

因为校验规则不同，所以使用两种校验规则的非唯一列相同结果的排序结果也会不同。

在向新的环境导入数据的时候一定要注意保持两端的字符集和排序规则一致，否者可能出现如下报错：

[err] 1271 - Illegal mix of collation for opertion