一: Mysql字符集问题

## Mysql 字符集问题

1 修改MySQL5.7字符集

1.1 修改步骤

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库 设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

操作1：查看默认使用的字符集

show variables like 'character%';
# 或者
show variables like '%char%';

MySQL8.0中执行：

img

MySQL5.7中执行：

MySQL 5.7 默认的客户端和服务器都用了 latin1 ，不支持中文，保存中文会报错。MySQL5.7截图如 下：

image-20220116203916246

在MySQL5.7中添加中文数据时，报错：

image-20220116203940829

操作2：修改字符集

vim /etc/my.cnf

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8

image-20220116204135732

操作3：重新启动MySQL服务

systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

1.2. 已有库&表字符集的变更

MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。

image-20220116204237781

修改已创建数据库的字符集

alter database dbtest1 character set 'utf8';

修改已创建数据表的字符集

alter table t_emp convert to character set 'utf8';

image-20220116204325933

注意：但是原有的数据如果是用非'utf8'编码的话，数据本身编码不会发生改变。已有数据需要导 出或删除，然后重新插入。

2 各级别的字符集

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

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

执行如下SQL语句：

show variables like 'character%';

image-20220116204437669

  • character_set_server：服务器级别的字符集
  • character_set_database：当前数据库的字符集
  • character_set_client：服务器解码请求时使用的字符集
  • character_set_connection：服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
  • character_set_results：服务器向客户端返回数据时使用的字符集

2.1 服务器级别

character_set_server ：服务器级别的字符集。

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量 的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci #对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

2.2 数据库级别

character_set_database ：当前数据库的字符集

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];
	
ALTER DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

2.3 表级别

character_set_database ：当前数据库的字符集

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]]
	
ALTER TABLE 表名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作 为该表的字符集和比较规则。

2.4 列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列 定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
	列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
	其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集 和比较规则作为该列的字符集和比较规则。

提示

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生 错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的 话就会出错，因为ascii字符集并不能表示汉字字符。

3. 小结

我们介绍的这4个级别字符集和比较规则的联系如下：

  • 如果 创建或修改列 时没有显式的指定字符集和比较规则，则该列 默认用表的 字符集和比较规则
  • 如果 创建表时 没有显式的指定字符集和比较规则，则该表 默认用数据库的 字符集和比较规则
  • 如果 创建数据库时 没有显式的指定字符集和比较规则，则该数据库 默认用服务器的 字符集和比较规 则

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据 这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表 t 中插入一 条记录：

mysql> INSERT INTO t(col) VALUES('我们');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+--------+
| s |
+--------+
| 我们 |
+--------+
1 row in set (0.00 sec)

首先列 col 使用的字符集是 gbk ，一个字符 '我' 在 gbk 中的编码为 0xCED2 ，占用两个字节，两个字 符的实际数据就占用4个字节。如果把该列的字符集修改为 utf8 的话，这两个字符就实际占用6个字节

4. 字符集与比较规则

4.1. utf8 与 utf8mb4

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示 了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计 MySQL的设计者偷偷的定义了两个概念：

  • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1一3个字节来表示一个字符。
如果大家有使用4字节编码一个字符的情况。比如存储一些emoji表情，那请使用utf8mb4。

4.2 比较规则

上表中，MySQL版本一共支持41种字符集，其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则 比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。 后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

后缀 英文释义 描述
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitive 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较

最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节：
这里把见的字符集和对应的Maxlen显式如下：

image-20220116230254523

常用操作1：

#查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';

#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

常用操作2：

#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';

#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';

#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;

#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

说明:

utf8_unicode_ci 和 utf8_general_ci对于 中、英文来说没有实质差别
utf8-general-ci 校对速度快，但准确度稍差。
utf8_unicode_ci 准确度高，但校对速度稍慢.
一般情况，用utf8-general_ci就够了，但如果你的应用有德浯、法浯或者俄浯，请一定使用utf8_unicode_ci.

修改了数据库的默认字符集和比较规则后，原来已经创建的表格的字符集和比较规则并不会改变，如果需
要，那么需单独修改。

常用操作3：

#查看表的字符集
show create table employees;

#查看表的比较规则
show table status from atguigudb like 'employees';

#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

5 请求到响应过程中字符集的变化

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符
串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走
到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量，我们先
把它们写出来看一下·

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

image-20220116231122334

为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值：

mysql> set character_set_connection = gbk;
Query OK, 0 rows affected (0.00 sec)

现在假设我们客户端发送的请求是下边这个字符串：

SELECT * FROM t WHERE s = '我';

为了方便大家理解这个过程，我们只分析字符 '我' 在这个过程中字符集的转换。

现在看一下在请求从发送到结果返回过程中字符集的变化：

  1. 客户端发送请求所使用的字符集

    一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一 样，如下：

    • 类 Unix 系统使用的是 utf8
    • Windows 使用的是 gbk

    当客户端使用的是 utf8 字符集，字符 '我' 在发送给服务器的请求中的字节形式就是： 0xE68891

    提示

    如果你使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编 码发送到服务器的字符串，而不采用操作系统默认的字符集（所以在学习的时候还是尽量用命令行窗口）。

  2. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client ，然后把这串字节转换为 character_set_connection 字符集编码的 字符。

    由于我的计算机上 character_set_client 的值是 utf8 ，首先会按照 utf8 字符集对字节串 0xE68891 进行解码，得到的字符串就是 '我' ，然后按照 character_set_connection 代表的字符集，也就是 gbk 进行编码，得到的结果就是字节串 0xCED2 。

  3. 因为表 t 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列 中找字节值为 0xCED2 的记录，最后找到了一条记录。

    提示 如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行 一次字符集转换。

  4. 上一步骤找到的记录中的 col 列其实是一个字节串 0xCED2 ， col 列是采用 gbk 进行编码的，所 以首先会将这个字节串使用 gbk 进行解码，得到字符串 '我' ，然后再把这个字符串使用 character_set_results 代表的字符集，也就是 utf8 进行编码，得到了新的字节串： 0xE68891 ，然后发送给客户端。

  5. 由于客户端是用的字符集是 utf8 ，所以可以顺利的将 0xE68891 解释成字符 我 ，从而显示到我 们的显示器上，所以我们人类也读懂了返回的结果。

总结图示如下：

image-20220116231727361

从这个分析中我们可以得出这么几点需要注意的地方：

  • 服务器认为客户端发送过来的请求是用character-set-client编码的·
    假设你的客户端采用的字符集和character-set-client不一样的话，这就会出现识别不准确的情况,比
    如我的客户端使用的是utf8字符集，如果把系统变量character-set-client的值设置为ascii的话，服
    务器可能无法理解我们发送的请求，更别谈处理这个请求了。
  • 服务器将把得到的结果集使用character-set-results编码后发送给客户端·
    假设你的客户端采用的字符集和character-set-results不一样的话，就可能会出现客户端无法解码结
    果集的情况，结果就是你的屏草上出现乱码。比如我的客户端使用的是utf8 字符集，如果把系统变量
    character-set-results的值设首为ascii的话，可能会产生乱码。
  • character-set-connection只服务器在将请求的字节串从character-set-client转换为
    character-set-connection时使用，一定要注意，该字符集包含的字符范围一定涵盖请求中的字符, 要不
    然会导致有的字符无法使用character-set-connection代表的字符集进行编码。

经验:

开发中常把character_set_client、character_set_connection,character—set—results这三个系统变量设置成和
客户端使用的字符集一致的情况，这样减少了很多无渭的字符集转换。为了方便我们设置MySQL提供了一条非常简便的语句。

SET NAMES 字符集名;

这一条语句产生的效果和们执行这3条的效果是一样的．

SET character_set_client = 字符名;
SET character_set_connection = 字符名;
SET character_set_results = 字符集名;

比方说我的客户端使的是utf8字符集,所以需要把这几个系统变量的值都设置为utf8

mysq1>SET NAMES utf8:

另外，如果你想在启动客户端的时候就把character_set_client、character_set_connection，
character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫
default_character_set的启动选项，比如在配置文件里可以这么写：

[client]
default-character-set = utf8

它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

6. SQL大小写规范

6.1 Windows和Linux平台区别

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关 键字，以及 ABS、MOD、ROUND、MAX 等函数名。

不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大 小写问题。 windows系统默认大小写不敏感 ，但是 linux系统是大小写敏感的 。

通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%'

Windows系统下：

image-20220118225320105

Linux系统下：

image-20220118225334626

lower_case_table_names参数值的含义：

  • 默认为0，大小写敏感 。
  • 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转 换为小写对表和数据库进行查找。
  • 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名、表名、表的别名、变量名是严格区分大小写的；

2、关键字、函数名称在 SQL 中不区分大小写；

3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL在Windows的环境下全部不区分大小写

6.2 Linux下大小写规则设置

当想设置为大小写不敏感时，要在 my.cnf 这个配置文件 [mysqld] 中加入

lower_case_table_names=1 然后重启服务器。

  • 但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。

此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动 MySQL 服务时将lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的值,如果非要将MySQL8设置为大小写不敏感，具体步骤为：

1、停止MySQL服务

2、删除数据目录，即删除 /var/lib/mysql 目录

3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1

4、启动MySQL服务

注意：在进行数据库参数设置之前，需要掌膣这个参数带来的影响，切不可盲目设置。

6.3 SQL 编写建议

如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

  1. 关键字和函数名称全部大写；
  2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
  3. SQL 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。

虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

posted @ 2022-04-11 15:41  哈哈丶丶  阅读(517)  评论(0)  编辑  收藏  举报