MySQL：字符集与比较规则

OceanStar的学习笔记

已于 2022-09-15 08:57:16 修改

阅读量330

点赞数

分类专栏：数据库文章标签： mysql 数据库 linux

于 2022-07-21 15:19:44 首次发布

原文链接：http://product.dangdang.com/29146258.html?_ddclickunion=P-341155|ad_type=10|sys_id=1#dd_refer=

版权

数据库专栏收录该内容

289 篇文章 17 订阅

订阅专栏

查看字符集和比较规则

什么是字符集和比较规则

查看当前MySQL支持的字符集

(root@localhost) [(none)]> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
......     
41 rows in set (0.01 sec)

Default collation ：默认比较规则
Maxlen ：最多需要几个字节来表示一个字符。

比较常用的字符集的MaxLen：
在这里插入图片描述

查看某个字符集支持的比较规则

(root@localhost) [(none)]> show collation like 'utf8%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |

需要注意的列：

Collation ：注意命名，其格式为：比较规则 + 所应用的语言 + 是否区分语言中的重音、大小写等。举个例子
- utf8mb4_general_ci、utf8mb4_spanish_ci：
  - 应用的字符集是utf8mb4
  - general表示通用语言，spanish表示是西班牙语的比较规则
  - ci：表示不区分大小写
- 后缀规则表：
Default ：填YES的表示这是当前语言的默认比较规则

各级别的字符集和比较规则

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

服务器级别
数据库级别
表级别
列基本

应该怎么应用呢？

服务器级别

（1）怎么查看

MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则，命令如下：

[(none)]> show variables like 'character_set_server';  #服务器级别的字符集
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)

[(none)]> show variables like 'collation_server'; #服务器级别的比较规则
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.00 sec)

（2）怎么设置

可以在启动服务器，通过启动选项，或者配置文件设置
可以在服务器运行过程中，通过SET语句来修改

（2.1）配置项

[server]
character_set_server=utf8mb4 
collation_server=utf8mb4_0900_ai_ci

数据库级别

（1）怎么设置：

可以为数据库指定该数据库的字符集和比较规则，命令如下：

[(none)]> create database charset_table_db character set gb2312 collate gb2312_chinese_ci;

如果建表是不指定字符集和比较规则，将默认使用服务器级别的字符集和比较规则

[(none)]> create database charset_table_db

（2）怎么查看

- MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则，命令如下：

[(none)]> use charset_table_db;
[(none)]> show variables like 'character_set_database';  #数据库级别的字符集
[(none)]> show variables like 'collation_database'; #数据库级别的比较规则

表级别

（1）怎么设置

可以在创建和修改表的时候指定

[(none)]> create table t(col varchar(10)) character set utf8mb4 collate utf8mb4_0900_ai_ci ;

如果建表时不指定的话，那么就会继承库级别的字符集和比较规则

列级别

（1）怎么设置

同一表中的不同列也可以有不同字符和比较规则
建表时指定如下

[(none)]> create table t(
	col varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci 
	);

如果不指定的话那么就会继承表级别的

（2）怎么修改

[(none)]> alter table t modify col varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci ;

如果修改时不指定字符集和编码规则的话就会继承列的

[(none)]> alter table t modify col varchar(10)

注意：修改时如果发现列中存储的数据不能用修改后的字符表示，则会发生错误。

可以仅仅修改字符集或者只修改比较规则吗？

不可以。字符集和比较规则是相互影响的：

只修改字符集，则比较规则会变成字符集默认的比较规则
只修改比较规则，字符集将会变为修改后的比较规则对应的字符集

字符集跟存储数据有什么关系？

超出字符集表示范围内的数据不能被存储
超出字符集所占用内存大小的数据不能被存储
如果字符集和修改后的字符集不兼容，那么会乱码
…
字符集是很重要的，别乱修改

比较规则是干什么用的？一般两个作用

比较字符串的大小
对某些字符串进行排列

客户端和服务器通信过程中使用的字符集

如果我们抓包看，就会看到客户端发送的和服务器返回的响应都是一个字符串。

从机器的角度看，客户端发送的请求和服务器返回响应的过程中，经过了很多字符集的转换

客户端发送请求

MySQL客户端和服务器通信的过程中，客户端会根据事先定好的数据格式发送请求给服务端。
这些请求必须经过一定的编码才能发送。
默认情况下，客户端编码请求字符串时使用的字符集和操作系统当前使用的字符集一致。

服务器接收请求

服务器接收到请求后，它会分析这个请求是采用哪种字符集进行编码的，这一步骤取决于系统变量character_set_client
在真正处理请求时，服务器把请求的字节序列使用character_set_connection对应的字符串解码
当服务器返回响应时，又会采用character_set_results对应的字符串编码

在这里插入图片描述

怎么查看当前操作系统是使用什么字符集呢？

在linux上，LC_ALL、LC_CTYPE、LANG，这三个环境变量决定了当前操作系统的字符集。
那么是以哪一个为准呢？前面的会覆盖后面的，我们可以直接打印查看这三个变量的值：

$ echo $LC_ALL;
$ echo $LC_CTYPE;
$ echo $LANG;
zh_CN.UTF-8

关于character_set_client

每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的character_set_client变量，这个变量是会话级别的
当然，我们可以在客户端通过set语句强制改变character_set_client的值。但是如果character_set_client对应的字符集不能解释请求的字节序列，服务器就会发出警告

[charset_table_db]> set character_set_client=ascii;
Query OK, 0 rows affected (0.01 sec)

[charset_table_db]> select '致';
+-----+
| ??? |
+-----+
| ??? |
+-----+
1 row in set, 1 warning (0.00 sec)

 [charset_table_db]> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE8\x87\xB4' from ascii to utf8mb4 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

我们应该保证客户端使用的编码字符集和服务端使用的解码字符集一致，所以别乱改！！！

MySQL中的utf-8和utf8mb4

UTF-8字符集在表示一个字符时需要使用1~4字节，但是我们常用的一些字符使用1~3字节就可以表示了。

所以，MySQL 字符编码集中有两套 UTF-8 编码实现：

utf8 ：
- utf8编码只支持1-3个字节，是阉割版。它也叫做utf8mb3
- 在 utf8 编码中，中文是占 3 个字节，其他数字、英文、符号占一个字节。但 emoji 符号占 4 个字节，一些较复杂的文字、繁体字也是 4 个字节。
utf8mb4：
- UTF-8 的完整实现，正版！最多支持使用 4 个字节表示字符
- 如果需要存储一些emoji表情，就必须用这个
- 在MySQL8.0中，MySQL已经很大程度的优化了utf8mb4的性能了，而且它是默认字符集
- 设计原则：永远不要在MySQL中使用utf8,而是要用utf8mb4。