0基础学MySQL数据库—从小白到大牛(19)字符集

1.字符集的相关操作

1.1 查看MySQL8.0字符集

查看步骤
在MySQL 8.0版本之前，默认字符集为 latin1，utf8字符集指向的是utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码将改为utf8mb4，从而避免上述乱码的问题。
操作1：查看默认使用的字符集

show variables like 'character%'; show variables like '%char%';mysql> show variables like 'character%'; +--------------------------+--------------------------------+| Variable_name            | Value                          |+--------------------------+--------------------------------+| character_set_client     | utf8mb4                        || character_set_connection | utf8mb4                        || character_set_database   | utf8mb4                        || character_set_filesystem | binary                         || character_set_results    | utf8mb4                        || character_set_server     | utf8mb4                        || character_set_system     | utf8mb3                        || character_sets_dir       | /usr/share/mysql-8.0/charsets/ |+--------------------------+--------------------------------+8 rows in set (0.00 sec)

1.2修改5.7字符集

vim /etc/my.cnf

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8

然后重启服务器

systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

那如果已经创建好的，要如何修改呢
MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。
修改已创建数据库的字符集

alter database dbtest1 character set 'utf8';

修改已创建数据表的字符集

alter table t_emp convert to character set 'utf8';

注意：但是原有的数据如果是用非’utf8’编码的话，数据本身编码不会发生改变。已有数据需要导出或删除，然后重新插入

2.各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是：

• 服务器级别
• 数据库级别
• 表级别
• 列级别

mysql> show variables like 'character%';+--------------------------+--------------------------------+| Variable_name            | Value                          |+--------------------------+--------------------------------+| character_set_client     | utf8mb4                        || character_set_connection | utf8mb4                        || character_set_database   | utf8mb4                        || character_set_filesystem | binary                         || character_set_results    | utf8mb4                        || character_set_server     | utf8mb4                        || character_set_system     | utf8mb3                        || character_sets_dir       | /usr/share/mysql-8.0/charsets/ |+--------------------------+--------------------------------+8 rows in set (0.00 sec)

• character_set_server：服务器级别的字符集
• character_set_database：当前数据库的字符集
• character_set_client：服务器解码请求时使用的字符集
• character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results：服务器向客户端返回数据时使用的字符集

服务器级别

character_set_server =：服务器级别的字符集。
我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server] character_set_server=gbk # 默认字符集 collation_server=gbk_chinese_ci #对应的默认的比较规则 

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

数据库级别

character_set_database ：当前数据库的字符集
我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名     [[DEFAULT] CHARACTER SET 字符集名称]     [[DEFAULT] COLLATE 比较规则名称]; ALTER DATABASE 数据库名     [[DEFAULT] CHARACTER SET 字符集名称]     [[DEFAULT] COLLATE 比较规则名称];

表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)     [[DEFAULT] CHARACTER SET 字符集名称]     [COLLATE 比较规则名称]] ALTER TABLE 表名     [[DEFAULT] CHARACTER SET 字符集名称]     [COLLATE 比较规则名称] 

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(     列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],     其他列... );ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称]; 

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

小结

• 我们介绍的这4个级别字符集和比较规则的联系如下：
• 如果创建或修改列时没有显式的指定字符集和比较规则，则该列 默认用表的 字符集和比较规则
• 如果创建表时没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则
• 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库 默认用服务器的 字符集和比较规则

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据

这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表 t 中插入一条记录：

mysql> INSERT INTO t(col) VALUES('我们'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t; +--------+| s      | +--------+| 我们    | +--------+1 row in set (0.00 sec)

首先列 col 使用的字符集是 gbk ，一个字符 '我' 在 gbk 中的编码为 0xCED2 ，占用两个字节，两个字符的实际数据就占用4个字节。如果把该列的字符集修改为 utf8 的话，这两个字符就实际占用6个字节

3.字符集与比较规则

utf8 与 utf8mb4

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：

utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。

utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

在MySQL中utf8就是utf8mb3，如果要存储emoji表情，就需要使用utf8mb4

查看MySQL支持的字符集

SHOW CHARSET;或SHOW CHARACTER SET;mysql> SHOW CHARSET;+----------+---------------------------------+---------------------+--------+| Charset  | Description                     | Default collation   | Maxlen |+----------+---------------------------------+---------------------+--------+| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 || ascii    | US ASCII                        | ascii_general_ci    |      1 || big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 || binary   | Binary pseudo charset           | binary              |      1 || cp1250   | Windows Central European        | cp1250_general_ci   |      1 || cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 || cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 || cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 || cp850    | DOS West European               | cp850_general_ci    |      1 || cp852    | DOS Central European            | cp852_general_ci    |      1 || cp866    | DOS Russian                     | cp866_general_ci    |      1 || cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 || dec8     | DEC West European               | dec8_swedish_ci     |      1 || eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 || euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 || gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 || gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 || gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 || geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 || greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 || hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 || hp8      | HP West European                | hp8_english_ci      |      1 || keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 || koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 || koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 || latin1   | cp1252 West European            | latin1_swedish_ci   |      1 || latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 || latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 || latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 || macce    | Mac Central European            | macce_general_ci    |      1 || macroman | Mac West European               | macroman_general_ci |      1 || sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 || swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 || tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 || ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 || ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 || utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 || utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 || utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 || utf8mb3  | UTF-8 Unicode                   | utf8_general_ci     |      3 || utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |+----------+---------------------------------+---------------------+--------+41 rows in set (0.52 sec)

最后一列 Maxlen ，它代表该种字符集表示一个字符最多需要几个字节。

比较规则

上表中，MySQL版本一共支持41种字符集，其中的Default collation列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较， utf8_spanish_ci 是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

#查看GBK字符集的比较规则 SHOW COLLATION LIKE 'gbk%'; #查看UTF-8字符集的比较规则 SHOW COLLATION LIKE 'utf8%'; 

#查看服务器的字符集和比较规则 SHOW VARIABLES LIKE '%_server'; #查看数据库的字符集和比较规则 SHOW VARIABLES LIKE '%_database'; #查看具体数据库的字符集 SHOW CREATE DATABASE dbtest1; #修改具体数据库的字符集 ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别
utf8_general_ci校对速度快，但准确度稍差。
utf8_unicode_ci准确度高，但校对速度稍慢。

一般情况，用utf8_general_ci就够了，但如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。

修改了数据库的默认字符集和比较规则后，原来已经创建的表格的字符集和比较规则并不会改变，如果需要，那么需单独修改。

#查看表的字符集 show create table employees; #查看表的比较规则 show table status from atguigudb like 'employees'; #修改表的字符集和比较规则 ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; 

4.请求到响应过程中字符集的变化

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量，我们先把它们写出来看一下：

系统变量	描述
character_set_client	服务器解码请求时使用的字符集
character_set_connection	服务器处理请求时会把请求字符串从character_set_client 转为 character_set_connection
character_set_results	服务器向客户端返回数据时使用的字符集

为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值：

mysql> set character_set_connection = gbk; Query OK, 0 rows affected (0.00 sec)

现在假设我们客户端发送的请求是下边这个字符串：

SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程，我只分析字符 '我' 在这个过程中字符集的转换。

现在看一下在请求从发送到结果返回过程中字符集的变化：
1.客户端发送请求所使用的字符集
一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：

• 类 Unix 系统使用的是 utf8
• Windows 使用的是 gbk 当客户端使用的是utf8字符集，字符 我在发送给服务器的请求中的字节形式就是：0xE68891

如果你使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集。

2服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是character_set_client ，然后把这串字节转换为character_set_connection字符集编码的字符。由于我的计算机上character_set_client的值是 utf8 ，首先会按照 utf8 字符集对字节串0xE68891进行解码，得到的字符串就是 '我' ，然后按照character_set_connection代表的字符集，也就是gbk进行编码，得到的结果就是字节串0xCED2

3因为表 t 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为0xCED2 的记录，最后找到了一条记录。

如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行一次字符集转换。

4上一步骤找到的记录中的 col 列其实是一个字节串 0xCED2，col列是采用gbk进行编码的，所以首先会将这个字节串使用gbk进行解码，得到字符串 ‘我’ ，然后再把这个字符串使用character_set_results 代表的字符集，也就是 utf8 进行编码，得到了新的字节串：0xE68891 ，然后发送给客户端。

5由于客户端是用的字符集是 utf8 ，所以可以顺利的将0xE68891解释成字符 我 ，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

在这里插入图片描述

• 服务器认为客户端发送过来的请求是用character_set_client编码的 假设你的客户端采用的字符集和character_set_client不一样的话，这就会出现识别不准确的情况。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_client的值设置为ascii的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。
• 服务器将把得到的结果集使用character_set_results编码后发送给客户端。 假设你的客户端采用的字符集和character_set_results不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_results的值设置为ascii的话，可能会产生乱码。
• character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。

开发中通常把character_set_client、character_set_connection、character_set_results这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：

SET NAMES 字符集名;一条语句顶三条语句SET character_set_client=字符集名;SET character_set_connection=字符集名;SET character_set_results=字符集名;

另外，如果你想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set的启动选项，比如在配置文件里可以这么写：

[client]default-character-set=utf8

它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

5.怎样选择合适的字符集

（1）满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同国家语言或者地区，就应该选择Unicode
（2）如果已经涉及已有数据的导入，就要充分的考虑数据库字符集对已有数据的兼容性
（3）如果数据库需要支持一般中文，且数据量大，性能要求也高，那就要选择双字节定长编码的中文字符集比如GBK
（4）字符集如果需要做大量的字符运算，比如排序，比较等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集处理的速度快
（5）如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免字符集转换带来的性能开销和数据损失