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

经常使用数据库的人会发现，有时候我们需要设置数据库编码或者表字段的编码。如：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，所以字符对它来说是无法识别的，更不用说字符集了，因此要将这套字符编码成为计算机可识别的二进制编码，这个将字符集转换为字节码流的过程即字符编码。

• 解码： 字符编码的逆过程，将字节码流转换成我们熟悉的字符集。
  
  那么问题来了，为什么要有多种字符集？

先举个例子：
  我制订了一套标准，给所有汉字从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 字符集…

二、字符集与排序规则

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

那么这些字符集和排序规则到底有什么区别呢？我们在使用时如何选择？今天就和大家一起来探讨下这个问题！

点此查看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;
1
2
3
4
5
6
7
8
9
10
11

2.1、MySQL字符集:character

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

windows系统下查看系统字符集

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

linux系统下查看系统字符集

mysql> SHOW VARIABLES  WHERE variable_name LIKE '%character%'  OR  variable_name  LIKE '%collation%'  ;
+-------------------------------+--------------------------------------+
| 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          | utf8                                 |
| character_sets_dir            | /opt/idc/mysql8.0.23/share/charsets/ |
| collation_connection          | utf8mb4_bin                          |
| collation_database            | utf8mb4_bin                          |
| collation_server              | utf8mb4_bin                          |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci                   |
+-------------------------------+--------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

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%';
1

(2) 查看mysql支持的字符集编码

SHOW CHARACTER SET;
或者
SHOW CHARSET;  -- 是上面命令SHOW CHARACTER SET;的缩写

-- 支持匹配查询，亦可通过%实现模糊查询
SHOW {CHARACTER SET|CHARSET} [LIKE 匹配的模式];
示例：SHOW CHARACTER SET like 'utf%';
1
2
3
4
5
6
7

下图为MySQL中支持的全部字符集，当然不同版本下其查询结果可能有些许出入

• 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的含义和由来

(3) 设置字符集编码

mysql> set names 'utf8';
1

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

set character_set_client = utf8;
set character_set_results = utf8;
set character_set_connection = utf8;
1
2
3

(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;
1
2
3
4
5
6

(5) 查看表的字符集

show  create table  表名;
或者
show table status from <数据库名/schema> like '<表名>';

示例：show table status from test  like 'tbl_test';
1
2
3
4
5

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

mysql> alter table table_name character set xxx；
1

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

mysql> alter table table_name convert to character set xxx;
1

(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;
1
2
3
4
5
6
7
8
9
10
11
12
13

2.2、校对规则(collation)

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

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

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

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

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%';
1
2
3
4

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

mysql> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
1
2
3
4
5
6
7
8

（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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

官方参考文档：https://dev.mysql.com/doc/refman/8.0/en/charset.html