开发实践｜MySQL的字符集（二）
开发实践｜MySQL的字符集（二）
📫 作者简介：「六月暴雪飞梨花」，专注于研究Java，就职于科技型公司后端工程师 🏆 近期荣誉：华为云云享专家、阿里云专家博主、 🔥 三连支持：欢迎 ❤️关注、👍点赞、👉收藏三连，支持一下博主~
文章目录
- 引言
- MySQL中的字符集
- 演示环境
- 命令行查看MySQL字符集
- 比较规则的作用域（常用）
- （1）服务器级别
- （2）数据库级别
- （3）表级别
- （4）列级别
- 结束语
引言
上一篇讲到字符、字符集、字符编码，粗略的一笔带过MySQL的编码，本篇想要讲讲字符编码在MySQL数据库中的应用。不仅仅是本篇文章，其他博主的文章也是，多多阅读他人的好文，才可以提升自己的水平。再次借用六一居士写的《卖油翁》来激励大家，多多阅读。
上一篇链接：运维分享｜MySQL的字符集（一）
MySQL中的字符集
MySQL数据库工具支持多样字符集设置，在我们安装完成mysql服务端以及客户端后，mysql是选用了latin作为他的默认字符集，所以我们在启动时都会将配置文件中的编码改为兼容性更好的UTF-8字符集。
演示环境
● 操作系统：MacOS Ventura 13.2 x86_64 ● MySQL版本：8.0.31 ● MySQL安装方式：Home brew
命令行查看MySQL字符集
在控制台输入登录到mysql服务器，如果是远程连接也是可以，为了方便大家查阅，除非特别说明，本次演示都是在MacOS上进行操作。 输入命令 show charset; 可以查看当前机器已经安装支持的所有字符集编码。执行命令后，我们可以看到结果有四列，分别为字符集名称、字符集描述、默认字符序列以及最大长度。其中字符集名称和最大长度是我们在以后使用过程中需要留意且需要记住的。
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 |
...
| 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 |
...
| 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 |
...
| 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 | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)
mysql>（1）我当前这个版本的MySQL数据库支持41种字符集（我做了删减），其中utf8mb3是UTF8的另外的名字，也就是说我们如果设定了utf8的字符集编码规则，那么也就是utf8mb3。这个是说明在底层的数据存储时，使用1～3个字节来表示一个字符。有些时候我们存储表情符😊，就需要4字节编码来描述一个字符，此时需要使用utf8mb4字符集编码。为了方便（一刀切），建议都创建为utf8mb4字符集编码。 （2）为了验证第一篇文章的基础知识，也可以在此来验证我们字符集编码的长度。这些数据存储在 information_schema库 中的 CHARACTER_SETS表 中。 命令行查看MySQL字符集比较规则 字符集的比较规则也就是字符序列的比较规则，这里的比较规则就是我们常常说的，1 和 2 到底哪一个大的问题，一或者说是排名前后问题，所以需要一种规则来约束。其中比较规则utf8我这里有一百多，我做了删减。
mysql> show collation like 'utf8%';
+-----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb3_bin | utf8mb3 | 83 | | Yes | 1 | PAD SPACE |
...
| utf8mb3_general_ci | utf8mb3 | 33 | Yes | Yes | 1 | PAD SPACE |
| utf8mb3_general_mysql500_ci | utf8mb3 | 223 | | Yes | 1 | PAD SPACE |
| utf8mb3_german2_ci | utf8mb3 | 212 | | Yes | 8 | PAD SPACE |
...
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
| utf8mb4_bg_0900_ai_ci | utf8mb4 | 318 | | Yes | 0 | NO PAD |
| utf8mb4_bg_0900_as_cs | utf8mb4 | 319 | | Yes | 0 | NO PAD |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
...
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE |
...
| utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD |
| utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD |
| utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD |
| utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD |
...
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE |
...
| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD |
+-----------------------------+---------+-----+---------+----------+---------+---------------+
117 rows in set (0.01 sec)
mysql>在utf8字符集中，我们可以看到有编码、ID、是否默认、是否完成、排序、填充属性等。这里说明这些数据是存储在表中。这个数据是存储在 information_schema库 中的COLLATIONS表 中，也可以使用SQL语句来查询数据。 这里的命令都是有规则的，其他的国家的可以忽略，如果你是做跨国项目，而且又使用MySQL数据库，那么就要考虑了，目前只是针对国内的，所以说几个我们常用的比较规则。例如 utf8mb3_general_ci 是通用的比较规则，utf8mb3_bin是二进制比较规则。 ● 以_general_ci结尾的是通用比较规则，当然，这个也是默认的。 ● 以_bin结尾的是二进制比较规则 ● 以_ci结尾的不区分大小写，同_cs结尾相反 ● 以_cs结尾的区分大小写，同_ci结尾相反
比较规则的作用域（常用）
MySQL中的字符集、比较规则有四个级别——即服务器级别、数据库级别、表级别、列（字段）级别。其作用范围正如其名，依次降低分别为MySQL服务器、数据库、表、列（字段）。 比较级别我们可以通过查询获取已经设置的级别。 ● 字符集比较级别
mysql> show variables like 'character_%';
+--------------------------+------------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
+--------------------------+------------------------------------------------------+
8 rows in set (0.01 sec)
mysql>● 比较规则比较级别
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb3_general_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)
mysql>（1）服务器级别
● character_set_server：服务器级别的字符集 ● collation_server：服务器级别的比较规则 如果望永久修改，则可以在MySQL配置文件的[server]选项组进行配置，示例如下
[server]
character_set_server=utf8
collation_server=utf8_general_ci（2）数据库级别
● character_set_database：当前数据库的字符集 ● collation_database：当前数据库的比较规则 数据库级别和表级别的都是可以修改的，我们在使用时也是根据自己的规则来选择。例如
CREATE DATABASE 数据库名 [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称];
下面可以举一个例子，首先创建一个数据库：
CREATE DATABASE db_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
USE db_test;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
（3）表级别
类似地数据库级别，当我们创建、修改表时同样支持显式地设置表的字符集或比较规则。这个设置由于是在一个固定的Schema中进行处理，所以没有放在参数中来设置。有默认的比较规则，格式同数据库设置格式差不多。
CREATE TABLE 表名 <字段信息> [ {CHARACTER SET|CHARSET} 字符集名称] [COLLATE 比较规则名称]]
CREATE TABLE it_test1 (
id INT(10) NOT NULL AUTO_INCREMENT COMMENT '序号',
`name` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
sex VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
age INT(5) COMMENT '项目编号',
PRIMARY KEY (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT '演示-字符集';
此时，我们修改表级别的字符集为 utf8mb4
ALTER TABLE it_test1 charset utf8mb3;
再次查询字符集级别
（4）列级别
对于字符串类型的列（字段）而言，一个表中的不同列（字段）也可以设置不同的字符集、比较规则。具体地，可在创建、修改列（字段）时显式地设置其字符集或比较规则。 如果你比较细心，那么你会发现我们在表级别创建的案例时，我们已经创建列级别的字符集和比较规则。修改时可以使用 modify 或者 change 来改变字符集和比较规则。
服务器端和客户端字符集编码 在MySQL客户端与服务端交互的过程中，会出现字符集的转换。如果不一致，则会出现乱码。 为了方便，我们通常会提前设置字符集编码以及比较规则。
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;结束语
本文讲解了MySQL中的编码字符集和比较规则，以及比较规则的作用域和如何查看和设置这些规则，最后又介绍了如何使用，以及在我们开发运维过程中的使用出现的问题。客户端、服务器端这个没有讲解，一般情况下，这个属于我们运维工程师的分内事情，所以我们经常会在安装好之后，提前设置好编码规则。