MySQL字符集、字符编码、排序规则、MySQL设置字符编码、设置表字段编码

偏执的太偏执、 2024-04-06 15:36 51阅读 0赞

#### 文章目录 ####

*  一、字符集介绍
 *  二、字符集与排序规则
 *   *  2.1、MySQL字符集:character
     *   *  2.1.1、字符集应用
         *  2.1.2、字符集操作命令
     *  2.2、校对规则(collation)
     *   *  2.2.1、什么是排序规则
         *  2.2.2、utf8mb4\_unicode\_ci 和 utf8mb4\_general\_ci 的区别
         *  2.2.3、校对规则操作命令

经常使用数据库的人会发现，有时候我们需要设置数据库编码或者表字段的编码。如：utf8mb3\_bin、utf8mb4\_0900\_ai\_ci等等，以下整理一下MySQL数据库中的字符集及如何设置编码。本文内容基于windows系统MySQL8.0.34版本进行讲解。

## 一、字符集介绍 ##

> 首先，了解以下几个老生常谈的概念：

*  **位：** 位（bit）是计算机最小的存储单位，由于计算机世界里只有逻辑0和逻辑1的存在，所以信息只能以一串二进制字码的形式存储到计算机中，如数字8保存到计算机里就是0000 1000，其中每个0和1即为一个位。
 *  **字节：** 一个字节（Byte）由8个位组成，可以用它来记录表示字母和一些符号，例如大写字母L就用0100 1100来表示（可以对照下面的ASCII码表）。
 *  **字符：** 即文字符号，包含文字，标点符号，数字，图形符号等（最直观的例子就是各国语言文字、数字、符号等）。
 *  **字符集：** 是多个字符的有序集合，常见的有ASCII（美国信息交换标准代码，如下图）、Unicode、GB2312、ISO-8859-1等字符集。
 *  **字符编码：** 计算机本身只认识数字0和1，所以字符对它来说是无法识别的，更不用说字符集了，因此要将这套字符编码成为计算机可识别的二进制编码，这个将字符集转换为字节码流的过程即字符编码。
 *  **解码：** 字符编码的逆过程，将字节码流转换成我们熟悉的字符集。  
    ![在这里插入图片描述][099644f63e9f4ad5a5d5b134f4c6bf8e.png]  
    那么问题来了，为什么要有多种字符集？

> 先举个例子：  
>   我制订了一套标准，给所有汉字从1开始标号，大概就是1-100,000（中国大约有10万个汉字），每个数字分别对应一个汉字，这样每个汉字有了它唯一的数字编号，然后我把这套标准起名BB并录入了计算机系统，且使用起来流畅无误，然后由于实用性强得到了全国普及，这时候突然有个外国佬发现他的程序出现了乱码，一查发现是因为这套程序是用的BB这个标准，他是按照自己国家的另一套编码解出来的，编码解码标准不对应产生错误字符从而出现了乱码，这时候他的计算机就不得不兼容我这套标准，而我的标准由于流行度高被国际认可，于是国际上就多了一套字符集。  
>   由此可知，乱码就是编码和解码时采用的编码方式不一致导致的！比如 ISO-8859-1 字符集里没有中文字符，用这套标准编码再解码同样会出现乱码。

**ASCII：** (American Standard Code for Information Interchange)，美国信息交换标准代码，它是最早产生的编码规范，也是最通用的信息交换标准，共有0000 0000~0111 1111的128个字符，能表示数字、大小写英文字母和一些简单的符号，后来IBM公司扩展了最高位，但就算这样最多也只能标识256个字符。

**Unicode：** 是国际化标准组织（ISO）制定的一种世界通用的编码规范，包含了全世界所有的字符，规定必须用两个字节来标识一个字符，也就是16位来统一表示所有字符，总共可以包含65535个不同字符，最多可以保存4个字节容量的字符。

由上图中可知，ASCII是一种单字节的编码标注（只需用8个位就能表示一个字符），却只有128个字符（加上最高位也才256个），这对其他国家来说就不够友好，说白了就是三个字：不够用，所以各个国家就在ASCII码的基础上进行扩展（这也是为什么编程尽量使用英文，因为英文乱码概率低），我国也有了GB2312字符集和GB18030字符集。

后来国际化标准组织（ISO）觉得一人搞一套那也太乱了，就制定了一个通用编码规范供大家一起用，这就是Unicode，即我们常说的万国码（也叫统一码）！

**总结可得：有多种字符集是因为每个字符集包含的字符数量不同，然而各国的字符内容不一，需求不一，这就不得不新增一种标准，于是就出现了 ASCII 字符集、GB2312 字符集、ISO-8859-1 字符集、 GB18030 字符集、Unicode 字符集…**

## 二、字符集与排序规则 ##

大家都知道，在新建数据库的时候，我们需要填写**数据库名、字符集和排序规则**，如下图：

![在这里插入图片描述][13504fe7720a4730841c9b78312b3789.png]  
**那么这些字符集和排序规则到底有什么区别呢？我们在使用时如何选择？今天就和大家一起来探讨下这个问题！**

[点此查看mysql官网8.0版本关于字符集的详细内容说明][mysql_8.0]

以下sql先看一下字符集与排序规则：

drop table if exists tbl_test;
    create table tbl_test(
    	id int primary key auto_increment,
    	name varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin unique not null,
    	age int comment '年龄',
    	address varchar(50) comment '住址',
    	update_time datetime default null
    ) comment '测试表';
    
    
    show create table tbl_test;

![在这里插入图片描述][04cced3127c04e93b85cb7a719aa0cdf.png]

### 2.1、MySQL字符集:character ###

> character字符集是一组符号和编码。collation是一组用于比较字符集中的字符的规则。

**windows系统下查看系统字符集**

SHOW VARIABLES  WHERE variable_name LIKE '%character%'  OR  variable_name  LIKE '%collation%';

![在这里插入图片描述][284728f57b1d49cc8935eb26487666e9.png]

**linux系统下查看系统字符集**

从上面client，connection，database，results层层环节扣着，任何一个环节的字符集不兼容都会出现乱码问题。

<table> 
 <thead> 
  <tr> 
   <th>变量名</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>character_set_client</td> 
   <td>设置客户端使用的字符集。</td> 
  </tr> 
  <tr> 
   <td>character_set_connection</td> 
   <td>设置连接数据库时的字符集，当程序里没指定连接数据库的字符集时的选项。</td> 
  </tr> 
  <tr> 
   <td>character_set_database</td> 
   <td>设置创建数据库时默认字符集，即创建数据库时不指定字符集时的选项。</td> 
  </tr> 
  <tr> 
   <td>character_set_filesystem</td> 
   <td>文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的。</td> 
  </tr> 
  <tr> 
   <td>character_set_results</td> 
   <td>数据库给客户端返回时使用的字符集，如果没指定则用character_set_server。</td> 
  </tr> 
  <tr> 
   <td>character_set_server</td> 
   <td>mysql服务器使用的默认字符集</td> 
  </tr> 
  <tr> 
   <td>character_set_system</td> 
   <td>数据库系统存储系统元数据的字符集，该值为utf8。</td> 
  </tr> 
  <tr> 
   <td>character_sets_dir</td> 
   <td>字符集安装的目录。</td> 
  </tr> 
 </tbody> 
</table>

#### 2.1.1、字符集应用 ####

**(1) 基础应用**

建库时, 若未明确指定字符集, 则采用character\_set\_server指定的字符集;  
建表时, 若未明确指定字符集, 则采用当前库所采用的字符集;  
新增或修改表字段时, 若未明确指定字符集, 则采用当前表所采用的字符集;

**(2) 更新,查询涉及到得字符集**

插入或更新流程字符集转换过程  
character\_set\_client–>character\_set\_connection–>表(列)字符集.  
MySQL服务端接到插入或更新SQL后, 发现有字符, 会查看客户端字符集(character\_set\_client), 当MySQL发现客户端字符集与自己的connection不一样时, 会将client的字符集转换为connection的字符集, MySQL将编码转换后的数据存储到MySQL表的列上, 在存储时, 会再判断编码是否与列字符集上的编码是否一致, 如果不一致需要再次转换.

查询流程字符集转换过程  
表(列)字符集–>character\_set\_result  
结果转换为与客户端相同的字符集再传递给客户端. (character\_set\_results默认等于character\_set\_client)

注意: 所有的字符集转换都发生在数据库端, 为避免出现乱码问题, 要保证各字符集一致.

#### 2.1.2、字符集操作命令 ####

(1) 查看字符集编码设置

mysql> show variables like '%character%';

![在这里插入图片描述][b3ae9bb3e8754c319892f66f5b4b52a6.png]  
(2) 查看mysql支持的字符集编码

SHOW CHARACTER SET;
    或者
    SHOW CHARSET;  -- 是上面命令SHOW CHARACTER SET;的缩写
    
    -- 支持匹配查询，亦可通过%实现模糊查询
    SHOW {CHARACTER SET|CHARSET} [LIKE 匹配的模式];
    示例：SHOW CHARACTER SET like 'utf%';

下图为MySQL中支持的全部字符集，当然不同版本下其查询结果可能有些许出入  
![在这里插入图片描述][8b8df70a0e0e4d239870290b67d30efa.png]

*  MaxLen列，其含义表示的是该字符集中表示一个字符最多需要的字节数。通过上图红框，我们可以看出MySQL中的utf8字符集、utf8mb4字符集的MaxLen值分别为3、4。而我们知道实际在utf8字符集中，其需要1～4个字节来表示一个字符。那为啥MySQL下的utf8字符集却最多只需3个字节呢？原因在于MySQL中一个字符所用的最大字节长度有时可能会对存储、性能等方面造成影响，故早期MySQL设计者对于utf8字符集的支持只到3个字节长度的字符，即其对于utf8中需要4个字节表示的字符是不支持的。故MySQL中的utf8字符集又被称之为utf8mb3字符集(most bytes 3) 。通常3字节的utf8字符已经能够满足绝大多数场景的需要了。但对于一些特殊场景，如emoji表情在实际的utf8字符集中就需要4个字节来表示。为此MySQL从5.5.3版本开始，提供了utf8mb4字符集，其是utf8(utf8mb3)的超集，将utf8字符的支持拓展到4字节。故utf8mb4才是我们真正意义上的utf8字符集

关于utf8、utf8mb3、utf8mb4的区别可以参考 [MYSQL（8.0版本及以上）- utf8，utf8mb3和utf8mb4的含义和由来][MYSQL_8.0_- utf8_utf8mb3_utf8mb4]

(3) 设置字符集编码

mysql> set names 'utf8';

相当于同时执行以下3个命令:

set character_set_client = utf8;
    set character_set_results = utf8;
    set character_set_connection = utf8;

(4) 修改数据库字符集  
只修改库的字符集, 影响以后创建的表的默认定义；对于已创建的表的字符集不受影响.一般在数据库修改字符集即可, 表和列都默认采用数据库的字符集.

mysql> alter database database_name character set xxx;
    mysql> ALTER DATABASE database_name CHARACTER SET = charset_name COLLATE = collation_name;
    
    
    示例：alter database test character set utf8mb4;
    示例：ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

(5) 查看表的字符集

show  create table  表名;
    或者
    show table status from <数据库名/schema> like '<表名>';
    
    示例：show table status from test  like 'tbl_test';

![在这里插入图片描述][c578b80d4aad4f40a4be6aef2720e940.png]

(6) 修改表的字符集  
只修改表的字符集, 影响后续该表新增列的默认定义, 已有列的字符集不受影响.

mysql> alter table table_name character set xxx；

(7) 同时修改表字符集和已有列字符集, 并将已有数据进行字符集编码转换.

mysql> alter table table_name convert to character set xxx;

(8) 修改列字符集

-- 定义列(字段)并设置其字符集或比较规则
    CREATE TABLE 表名(
        字段名 字符串类型 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称],
        其他列信息...
    );
    
    -- 修改已有列(字段)以设置其字符集或比较规则
    ALTER TABLE 表名 MODIFY 字段名 字符串类型 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称];
    
    -- 添加列(字段)并设置其字符集或比较规则
    ALTER TABLE 表名 ADD 字段名 字符串类型 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称];
    
    mysql> alter table table_name modify col_name varchar(col_length) character set xxx;

### 2.2、校对规则(collation) ###

校对规则是在字符集内用于字符比较和排序的一套规则, 可以设置区分/无视大小写.

校对规则命名规则是字符集名+语言名+区分后缀, 区分后缀一般是\_ci(不区分大小写), \_cs(区分大小写)和\_bin(二进制)三种.

通过以下sql查看排序规则：

-- 查看支持的比较规则
    SHOW COLLATION; 
    -- 支持匹配查询，亦可通过%实现模糊查询
    SHOW COLLATION [LIKE 匹配的模式];
    示例：SHOW COLLATION like '%utf%';

![在这里插入图片描述][923a949e10ca4f37ba43183a9fc6a3f0.png]

#### 2.2.1、什么是排序规则 ####

排序规则（collation），字符串在比较和排序时所遵循的规则。不同的字符集有不同的排序规则，同一个字符集也可以有多种排序规则。在 MySQL 8.0.1 及更高版本中将 utf8mb4\_0900\_ai\_ci（属于 utf8mb4\_unicode\_ci 中的一种） 作为默认排序规则，在这之前 utf8mb4\_general\_ci 是默认排序规则。

MySQL常用排序规则有：utf8mb4\_general\_ci、utf8mb4\_unicode\_ci、utf8mb4\_bin、utf8mb4\_0900\_ai\_ci

> **\_bin** ： 按二进制方式比较字符串，区分大小写和重音符号。  
> **\_ai\_ci**：按照特定语言或地区方式比较字符串，不区分大小写和重音符号。  
> **\_unicode\_ci**： 按 Unicode 标准方式比较字符串，不区分大小写和重音符号。  
> **\_general\_ci**：按一般方式比较字符串，不区分大小写和重音符号。

*  bin：binary case sensitive ，binary 以二进制方式，区分大小写
 *  ci：即case insensitive，不区分大小写，即排序时 p 和 P相同 。
 *  cs：case sensitive 区分大小写
 *  ai： accent insensitive，指口音不敏感，不区分重音，即排序时 e，è，é，ê 和 ë 相同。
 *  as：accent sensitive，即口音敏感，区分重音，也就是说，排序时 e，è，é，ê 和 ë 互不相同。
 *  0900： 是 Unicode 校对算法版本。

#### 2.2.2、utf8mb4\_unicode\_ci 和 utf8mb4\_general\_ci 的区别 ####

> utf8mb4\_unicode\_ci： 能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。  
> utf8mb4\_general\_ci： 不支持扩展，它仅能够在字符之间进行逐个比较。utf8\_general\_ci 比较速度很快，但比 utf8mb4\_unicode\_ci 的准确性稍差一些。

**1. 准确性**  
utf8mb4\_unicode\_ci 是精确排序，utf8mb4\_general\_ci 没有实现 Unicode 排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。  
因此，准确性是**utf8mb4\_unicode\_ci > utf8mb4\_general\_ci**

**2. 性能**  
utf8mb4\_general\_ci 在**比较和排序的时候更快**。

utf8mb4\_unicode\_ci 在特殊情况下，Unicode 排序规则为了能够处理特殊字符的情况，**实现了略微复杂的排序算法**。

因此，性能方面是**utf8mb4\_general\_ci > utf8mb4\_unicode\_ci**

**3. 如何选择？**  
如果在创建数据库时对特殊字符的顺序并不需要那么精确，排序规则可使用utf8mb4\_general\_ci 。推荐用 utf8mb4\_unicode\_ci，但是用 utf8mb4\_general\_ci 也没问题

#### 2.2.3、校对规则操作命令 ####

（1）查看数据库支持的所有校对规则

mysql> show collation;
    
    查看 utf8开头的排序规则
    SHOW COLLATION LIKE 'utf8%';

![在这里插入图片描述][923a949e10ca4f37ba43183a9fc6a3f0.png]

（2）查看当前字符集和校对规则设置

（3）设置表中字段字符集及校对规则

方式一：建表时指定整体表的字符集及校对规则（ps:collate=utf8mb4_bin指的是字符串类型的字段大小写敏感）
    mysql> create table t2(id int,name varchar(20)) character set=utf8mb4 collate=utf8mb4_bin;
    
    方式二：对name字段手动设置字符集及校对规则
    create table tbl_test(
    	id int primary key auto_increment,
    	name varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin unique not null,
    	age int comment '年龄',
    	address varchar(50) comment '住址',
    	update_time datetime default null
    ) comment '测试表';
    
    方式三：建表后修改字段字符编码
    alter table 表名   modify  字段名  字段数据类型  character set 字符编码 not null;
    例：alter table  tb  modify  name varchar(10)  character  set  utf8  not null;

官方参考文档：[https://dev.mysql.com/doc/refman/8.0/en/charset.html][https_dev.mysql.com_doc_refman_8.0_en_charset.html]  
![在这里插入图片描述][d59fe5628d0c417b89f3ec8d8be41292.png]

[099644f63e9f4ad5a5d5b134f4c6bf8e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/13ace269f51e463c8bcdc34910a088de.png
[13504fe7720a4730841c9b78312b3789.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/2595df69a764403981d2fe747032347e.png
[mysql_8.0]: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html
[04cced3127c04e93b85cb7a719aa0cdf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/09b20a0a462a483095a6bd5b76f5f221.png
[284728f57b1d49cc8935eb26487666e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8dfc838f35bd410f9e336be157002419.png
[b3ae9bb3e8754c319892f66f5b4b52a6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/334af55208f449e8bfe05380d1c2f856.png
[8b8df70a0e0e4d239870290b67d30efa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d9757d8111564eedb35b137491300020.png
[MYSQL_8.0_- utf8_utf8mb3_utf8mb4]: https://blog.csdn.net/weixin_49114503/article/details/136624441
[c578b80d4aad4f40a4be6aef2720e940.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3afb7c1a10dc4b57a4d89b01e1d6819b.png
[923a949e10ca4f37ba43183a9fc6a3f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/599c898c55064a17aa954bd15de45532.png
[https_dev.mysql.com_doc_refman_8.0_en_charset.html]: https://dev.mysql.com/doc/refman/8.0/en/charset.html
[d59fe5628d0c417b89f3ec8d8be41292.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/2dc5d17bcd134d7da4740c2a620bbfbf.png