文章目录
经常使用数据库的人会发现，有时候我们需要设置数据库编码或者表字段的编码。如：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 字符集…
二、字符集与排序规则
大家都知道，在新建数据库的时候，我们需要填写数据库名、字符集和排序规则，如下图：
那么这些字符集和排序规则到底有什么区别呢？我们在使用时如何选择？今天就和大家一起来探讨下这个问题！
以下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;
2.1、MySQL字符集:character
character字符集是一组符号和编码。collation是一组用于比较字符集中的字符的规则。
windows系统下查看系统字符集
SHOW VARIABLES WHERE variable_name LIKE '%character%' OR variable_name LIKE '%collation%';
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 |
+-------------------------------+--------------------------------------+
从上面client，connection，database，results层层环节扣着，任何一个环节的字符集不兼容都会出现乱码问题。
| 变量名 | 含义 |
|---|---|
| character_set_client | 设置客户端使用的字符集。 |
| character_set_connection | 设置连接数据库时的字符集，当程序里没指定连接数据库的字符集时的选项。 |
| character_set_database | 设置创建数据库时默认字符集，即创建数据库时不指定字符集时的选项。 |
| character_set_filesystem | 文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的。 |
| character_set_results | 数据库给客户端返回时使用的字符集，如果没指定则用character_set_server。 |
| character_set_server | mysql服务器使用的默认字符集 |
| character_set_system | 数据库系统存储系统元数据的字符集，该值为utf8。 |
| character_sets_dir | 字符集安装的目录。 |
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%';
(2) 查看mysql支持的字符集编码
SHOW CHARACTER SET;
或者
SHOW CHARSET; -- 是上面命令SHOW CHARACTER SET;的缩写
-- 支持匹配查询，亦可通过%实现模糊查询
SHOW {CHARACTER SET|CHARSET} [LIKE 匹配的模式];
示例：SHOW CHARACTER SET like 'utf%';
下图为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';
相当于同时执行以下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';
(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%';
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%';
（2）查看当前字符集和校对规则设置
mysql> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
（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;
714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
