Mysql的字符集编码选择

2年前 (2022) 程序员胖胖胖虎阿

155 0 0

mysql版本：5.7.37

所有内容都来自于mysql5.7文档，这里只是做个笔记，方便以后自己查看。

mysql中的基础概念：

character set：字符集编码。

collations：与字符集编码对应的一套规则，这套规则规定了字符如何比较算法，从而解决如何排序的问题，例如大小写敏感比较，按照字符的二进制编码比较等。

collation的名字组成及含义： {character set name}_{language-specific}_{collation_suffixes} 其中第一个部分表示的值字符集的名字；第二部分表示的特定语言，表示按照该特定语言的排序规则排序；第三部分表示的后缀，后缀含义如下表。

Suffix Meaning

_ai Accent-insensitive

_as Accent-sensitive

_ci Case-insensitive

_cs Case-sensitive

_bin Binary

另外，collatoin的名字后缀中，_ci暗含了_ai，_cs暗含了_as。例如，latin1_general_ci 表示case-insensitive 和隐含accent-insensitive。

对于_bin后缀的collations来说，其比较的是字符的编码值，这是比较重要的一点。

Suffix	Meaning
`_ai`	Accent-insensitive
`_as`	Accent-sensitive
`_ci`	Case-insensitive
`_cs`	Case-sensitive
`_bin`	Binary

collation与character set的关系：

mysql中，collation的值必须与character set对应，每一种character set都有一个默认的collation。

例如

如果指定character set为utf8，那么collation的值就只能是utf8相关的collation，否则报错。

如果指定了character set为utf8而没有指定collation的值，那么就会使用utf8对应的默认的collation----utf8_general_ci。

character set repertoire：字符集所支持的字符。

一个String expression可以指定repertoire属性来表明该String expressions的中包含的字符是属于哪个字符集，该属性是对 Collation Coercibility in Expressions的补充，当 Collation Coercibility in Expressions的优先级相同的时候，就需要查看repertoire属性，然后根据该属性来确定是否能够进行字符串的转换，如果不能就报错；如果能转换，则转换。

在创建字段的时候，通常系统都会默认分配一个字符集给一个字段，或者用户也可以显式的给字段指定一个字符集，然后当用户在字符串表达式中使用该字段的时候，假如用户没有指定repertoire属性，那么系统就会使用创建字段的时候的字符集的值来确定repertoire的值。repertoire是以下两个值之一：ASCII和UNICODE。

ASCII：表明该string expressions包含的字符都是ASCII字符。

UNICODE：表盖string expressions包含的字符是unicode字符。

之所以是这两个值之一的原因在于：

ASCII是其它所有character set的子集，所以它能转换为其它任意的字符集字符。

UNICODE是其它所有character set的超集，所以其它任意字符集字符可以转换为UNICODE字符。

binary character set：该字符集应该是mysql的一个概念，其有一个对应的binary collation，该collation规定了binary strings（例如，binary，varbinary类型）的比较和排序规则。binary strings类型的字段都是属于binary character set，在创建二进制字段的时候，不需要指定character set也不能指定character set（如果指定会报错）。

二进制数据

二进制数据的比较是基于字节的，比对的是字节中内容。例如，一个二进制字段具有5个字节，但是存入的数据只有1个字节，那么剩下4个字节全部自动填0，后续的比较也会把这4个0考虑在内。

1、查看mysql支持的字符集

直接在mysql命令行输入以下命令：

SHOW CHRACTER SET;

Mysql的字符集编码选择

mysql的5.7.37版本支持的字符集高达41种。

其中第三列直接给出了每种字符集对应的默认的collations。

2、查看mysql具有的collation

直接在mysql命令行输入如下命令：

SHOW COLLATION;

Mysql的字符集编码选择

第二列表明了该collation对应的字符集。

3、几个重要的系统变量character set variables

查看系统的character set相关的系统变量：

SHOW VARIABLES LIKE 'character%'; //查看当前session的系统变量，show的默认范围是session。

Mysql的字符集编码选择

SHOW GLOBAL VARIABLES LIKE 'character%' ; //查看全局的系统变量。

Mysql的字符集编码选择

1）、character_set_server

这是server的字符集编码变量，用来确定当前mysqld使用的字符集编码，默认值：latin1。

对应的collation的默认值为：-latin1_swedish_ci

该参数可以通过在mysqld命令行启动的时候指定对应参数来设置：

--character-set-server=latin1

--collation-server=latin1_swedish_ci

或者通过配置文件的方式来设置：

[mysqld]
character_set_server=latin1

collation_server=latin1_swedish_ci

该参数的作用：

当CREATE DATABASE创建数据库的时候，如果没有指定CHRACTER SET和COLLATION的话，就以该值作为数据库的character set的值以及collation的值。

2）、character_set_database

这是数据库的字符集变量，用来确定当前数据库的字符集编码，该变量的来源：

首先根据CREATE DATABASE语句创建数据库时指定。

当指定了character_set_database和collation时，则使用指定的值。

当只指定了character_set_database时，则使用指定的character_set_database以及对应默认的collation。

当只指定了collation时，则使用该collation对应的character_set_database和以及该collation。

如果没有指定任意一个character_set_database和collation时，则使用character_set_server以及collation-server的值。

当数据库创建完成，该值确定，后续可以通过ALTER语句修改。

注意：

全局的character_set_database系统变量在5.7版本中被废弃了，所以这里说得都是具体的数据库的character_set_database。

当切换数据库的时候，character_set_database的值会自动改变。

该参数的作用：

当创建表的时候，如果表没有指定character set和collation，那么就使用数据库的character_set_database的值以及对应的collation的值作为表的选项值。

当通过LOAD DATA从一个文件中读入数据的时候，如果LOAD DATA没有指定字符集编码，则使用数据库的character_set_database的值来作为文件的字符集编码，使用该编码来解释文件中的内容；如果指定了字符集编码，则使用对应的字符集编码。

在stored routines（procedures 和 functions）中，如果没有指定字符集编码和collation，则使用数据库character_set_database的值以及对应的collation的值作为该stored foutines的值。如果指定了字符集和collation，则使用指定的值。

mysql可以在5个层面指定字符集编码：

server层面，也就是说上面对应的server字符集系统变量。

database层面，也就是上面所说的database字符集系统变量。

table层面，在通过CREATE TABLE语句创建表的时候，给表指定字符集编码和collation，如果没有指定，则使用数据库的字符集编码和Collation作为表的字符集编码和collation。

column层面，在创建表的时候，可以在字段后面指明该字段的字符集编码以及collation，如果没有指定，则使用表的字符集编码和collation作为字段的字符集编码和collation（二进制类型字段除外，因为二进制字段类型只能是二进制字符集以及二进制collation）。

字符串常量（string literal）层面，使用字符串常量的时候，可以通过[_charset_name]'string' [COLLATE collation_name]中的[_charset_name]和 [COLLATE collation_name]指定字符集编码和collation。如果没有指定，则使用character_set_connection及其对应的collation。

3）、character_set_connection、character_set_results和character_set_client

这三个系统变量都是mysql clien跟mysql server交互的重要系统变量：

character_set_client是mysql client发送过来的语句所使用的字符集编码。

character_set_connection是mysql client发送过来的语句会转换为该值对应的字符集编码。

character_set_results是mysql server查询得到的结果所使用的字符集编码。

这三个系统变量里，只有character_set_connection有对应的collation_connection系统变量。

交互过程：

mysql client发送所使用的字符集编码给mysql server，然后mysql server根据发送过来的字符集编码来设置当前session的这三个系统变量值。如果mysql server没有这样一个字符集编码，mysql server就会将当前session中的这三个系统变量的值设置为全局的对应的这三个系统变量的值。

mysql client一定要通过connector API发送自己所使用的字符集编码给mysql server，否则很容易出现编码不能识别的错误或者出现乱码。

亦或者，在执行sql语句之前，先执行SET NAMES语句，将这三个系统变量设置为客户端所使用的字符集编码。

mysql系统connector API，其他程序语言通过该API与mysql server交互，其它程序就相当于mysql client。

为了保证不出现乱码，或者不出现编码错误，这三个值最好一致，而且在执行sql语句之前，最好同步一下session中的这三个系统变量值。

注意点：

mysql server在转换sql语句编码的时候，如果mysql client发送来的sql语句中的字符串常量指定了字符集编码的话，那么它就不会转换这些这些字符串常量的编码，该字符串常量的字符集编码就是它本身的指定值。

附：

4）、character_set_system和character_set_filesystem以及character_set_dir

character_set_system是数据库系统使用字符集编码，保存metadata信息的时候，就使用该字符集编码，值永远是utf8。

character_set_filesystem是数据库系统用来解释文件名时所使用的字符集编码，即把操作系统的文件名转换为此字符集，默认是binary，即不做任何转换。

character_set_dir是字符集安装目录。

注意点：

mysql采用的是C/S架构，每一个mysql client需要通过与mysql server建立连接来访问数据库。mysql server为每一个session初始化一份字符集编码系统变量，我们通过Connector API或者或者SET NAMES或者SET CHARACTER SET命令来修改这些字符集编码的系统变量，实际上修改的是当前session中的那一份字符集编码的系统变量，并不会影响全局的系统变量。

5）、修改这些系统变量的值

注意点：系统变量分为全局变量和会话变量

全局变量：server启动时在全局初始化的一份系统变量。

会话变量：server与client建立session之后，server会为每个session都初始化一份会话变量。

临时修改全局变量

set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION

例如：

set character_set_server=utf8;

注意：因为这是一种临时修改方式，所以当server重启之后，所有的修改都将失效。

永久修改全局变量

想要永久修改全局变量就必须修改server的配置文件，linux下，mysql的配置文件默认为/etc/mysql/my.cnf。

该配置文件中的内容是分组的，[group]开头表示为一个组，如果是修改mysql server的配置，则以[mysqld]开头。

[mysqld]

character_set_server=utf8

当mysqld重启之后，全局的character_set_server就变为utf8了。

全局变量的初始化

当mysqld启动的时候，首先会去读取配置文件中的配置，然后利用配置文件的设置来初始化对应的server的全局系统变量。如果配置文件中没有设置，那么就会使用编译时指定的默认值。

对于character_set_server来说，其可以在配置文件中配置，当mysqld启动的时候，就从配置文件中读取该配置项，然后设置全局系统变量character_set_server。

当全局的character_set_server设置好了之后，其它几个全局的系统变量（character_set_database、character_set_client、character_set_connection、character_set_results）都会初始化为与全局的character_set_server同样的值。

注意点：

如果没有配置collation_server（必须与character_set_server配合，否则报错。），那么就会将该全局系统变量初始化为character_set_server的字符集编码对应的默认的collation值。其它的两个全局系统变量collation_database、collation_connection会初始化为collation_server的值。

临时修改session变量

set var_name=value; //等同于set session var_name=value，set的默认值为session范围，设置当前session的系统变量值。

例如：

set character_set_server=utf8;

另外一个命令

set names value;//同时设置当前session的character_set_client、character_set_connection、character_set_results的值。

例如：

set names utf8;

相当于

set character_set_client=utf8;

set character_set_connection=utf8;

set character_set_results=utf8;

session变量无法永久修改，当session结束，相关修改失效。

session系统变量的初始化

当mysql client与mysql server建立连接之后，mysql server会为当前session复制一份全局的系统变量作为当前session的初始值。

在session的过程中，mysql client可以修改对应的系统变量值，session结束，修改消失。

当重新建立session，mysql server又会重新从全局的系统变量复制一份作为新的session的初始化。

需要注意的是：character_set_database这个session系统变量，总是随着数据库的切换而改变。

注意：

系统变量collation_xxxx总是与相应的的character_set_xxxx对应的，当character_set_xxxx的值改变的时候，collation_xxxx总是随之而变。

4、指定mysql的字符集编码

前面提到过，mysql可以在5个层面设置字符集编码，下面根据说明如何设置。

1）、server层面

server层面的字符集编码设置有3种方式：

server启动之后，修改全局的对应的系统变量。这只是临时性修改不推荐。
在server启动的时候，通过mysqld的命令行参数来设置。每次启动需要重新输入命令行。
在配置文件中设置，每次server启动都会自动去读配置文件中的配置项。

这里直接给出配置文件的方式，其他两种方式，上面介绍过，可以查看。

[mysqld]

character_set_server=utf8

这样配置会将全局的character_set_server变量设置为utf8。

2）、数据库层面

创建数据库的时候是可以直接指定数据库的字符集编码的。

数据库创建好之后，也可以修改数据库的字符集编码。

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

例如：

CREATE DATABASE test_mysql CHARACTER SET utf8mb4;

数据库编码的计算过程：

当指定了character set和collation时，则使用指定的值。

当只指定了character set时，则使用指定的character set以及该字符集编码默认的collation。

当只指定了collation时，则使用该collation对应的character set和该collation。

如果没有指定任意一个character set和collation时，则使用character_set_server以及collation-server的值。

3)、表层面

创建表的时候可以直接指定表的字符集编码。

表创建好之后，也可以修改表的字符集编码。

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

例如：

CREATE TABLE t1 ( c1 char(1)) CHARACTER SET utf8mb4;

表编码计算过程：

当指定了character set和collation时，则使用指定的值。

当只指定了character set时，则使用指定的character set以及该字符集编码默认的collation。

当只指定了collation时，则使用该collation对应的character set和该collation。

如果没有指定任意一个character set和collation，则使用数据库的character set和collation。

4）、字段层面

创建表的时候可以直接指定字段的字符集编码。

表创建好之后，也可以修改表的字段的字符集编码。

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

例如：

CREATE TABLE t1 ( col1 VARCHAR(5) CHARACTER SET utf8mb4）;

字段编码计算过程：

当指定了character set和collation，则使用指定的值。

当只指定了character set时，则使用指定的character set以及该字符集编码默认的colltion。

当只指定了collation时，则使用该collation对应的character set和该collation。

如果没有指定任意一个character set和collation，则使用表的character set和collation。

5）、字符串常量层面

可以通过_introducer来指定字符串常量的前缀。

[_charset_name]'string' [COLLATE collation_name]

例如：

SELECT _utf8mb4'abc';

小结：

1）、一般来说server层面就使用系统的默认即可，贸然修改这个层面的字符集，可能某些依赖server默认字符集的东西出现问题。

2）、数据库层面最好在创建数据库的时候就指定字符集编码，将数据库的编码与server层面隔离开。数据库的字符集编码指定之后，就不要再修改数据库的字符集编码了。项目中的某些SQL脚本（例如创建表的SQL脚本）如果依赖于数据库字符集的话，那么修改数据库字符集会导致这些SQL脚本出错。

3）、表层面字符集编码与数据库保持一致即可。如果某个表中的大部分字段的字符集编码与数据库的字符集编码不一样，那么就重新指定该表的字符集编码与大部分字段字符集编码一致即可。表的字符集编码指定之后，就不要修改了，因为项目中的某些SQL脚本（例如创建表的SQL脚本，某些字段没有指明字符集，默认继承表的字符集编码）如果依赖于表的字符集编码，那么修改表的字符集会导致SQL脚本出错。

4）、字段层面，除非字段需要使用与表字符集编码不一样的字符集编码，否则与表的字符集编码保持一致。

5）、collation一般不指定，使用字符集编码默认的collation即可。如果默认不能满足需求，再指定对应的值。