MySQL 高级 - 第一章 | 字符集、大小写规范与sql_mode的合理设置

淡淡的烟草味﹌ 2024-05-06 23:21 3阅读 0赞

#### 目录 ####

*  前言
 *  一、字符集
 *   *  1.1 数据库中的默认字符集
     *  1.2 各级别的字符集
     *  1.3 字符集与比较规则
     *  1.4 字符集设置
     *  1.5 已有库表字符集变更
     *  1.6 请求到响应过程中字符集的变化
 *  二、大小写规范
 *   *  2.1 Windows 和 Linux 平台区别
     *  2.2 Linux 下大小写规则设置
     *  2.3 SQL 编写建议
 *  三、sql\_mode 的合理设置
 *   *  3.1 sql\_mode 的介绍
     *  3.2 sql\_mode 常用值
     *  3.3 模式查看与设置

--------------------

下篇：[第二章、数据库目录结构与文件系统][Link 1]

## 前言 ##

目前市面上使用的 `Mysql` 大多数都是 `mysql-8.0` 或者是 `mysql-5.7`，以下内容是对 `mysql` 的字符集与大小规范的一个简单介绍

可以用以下命令查看 `mysql` 版本：

select version();

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

如果您想要查看更详细的版本信息，包括版本号、发行版本、系统名称和 `description`，可以使用以下命令：

SHOW VARIABLES LIKE "%version%";

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

以下内容主要源于：[bilibili-尚硅谷-MySQL高级篇][bilibili-_-MySQL]

`MySQL` 的安装教程：[Linux-安装MySQL（详细教程）][Linux-_MySQL]

--------------------

## 一、字符集 ##

如果使用过 `mysql-5.7` 的人，大多数都会遇到这样一种情况，就是当我们向数据库中存放一个 `emoji` 表情 “😃” 时，数据库中的数据是乱码的，如果将该表中的字符集改成 `utf8mb4`，就能正常存放 `emoji` 表情 “😃” 了，这个问题就与 `mysql` 的字符集密切相关

--------------------

### 1.1 数据库中的默认字符集 ###

在 `Mysql-8.0` 版本之前，默认的字符集为 `Iantin1` ，开发人员在进行数据库设置的时候往往会需要将编码修改为 `UTF-8` 字符集，否则就可能出现乱码的问题

从 `Mysql-8.0` 开始，数据库的默认编码改为 `utf8mb4`，从而避免了上述的问题

通常情况下会使用以下命令查看 `mysql` 的默认字符集：

show variables like '%character%';

在 `Mysql-8.0` 中，默认的字符集如下：

而在 `Mysql-5.7` 中，默认的字符集如下：

*  character\_set\_server：服务器级别的字符集
 *  character\_set\_database：当前数据库的字符集
 *  character\_set\_client：服务器解码请求时使用的字符集
 *  character\_set\_connection：服务器处理请求时会把请求字符串从 character\_set\_client 转换为 character\_set\_connection
 *  character\_set\_results 服务器向客户端返回数据时使用的字符集

这里主要关注的是 `character_set_database` 和 `character_set_server`

--------------------

### 1.2 各级别的字符集 ###

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

*  服务器级别
    
     *  定义了 `MySQL` 服务器实例的默认字符集
     *  可以在 `my.cnf` 或 `my.ini` 配置文件中设置，或使用 `SET GLOBAL character_set_server = 'charset_name';` 来动态更改
     *  常见的服务器级别字符集有 `utf8、utf8mb4、latin1` 等
 *  数据库级别
    
     *  定义了特定数据库的默认字符集
     *  当创建数据库时，可以指定字符集，例如 `CREATE DATABASE mydb CHARACTER SET utf8mb4;`
     *  如果不指定，则使用服务器级别的默认字符集
 *  表级别
    
     *  定义了表中所有列的默认字符集
     *  创建表时，可以指定字符集，例如 `CREATE TABLE mytable (column1 VARCHAR(50)) CHARACTER SET utf8mb4;`
     *  如果不指定，则使用数据库级别的默认字符集
 *  列级别
    
     *  定义了特定列的字符集
     *  创建列时，可以指定字符集，例如 `CREATE TABLE mytable (column1 VARCHAR(50) CHARACTER SET utf8mb4);`
     *  如果不指定，则使用表级别的默认字符集

--------------------

### 1.3 字符集与比较规则 ###

可以通过以下命令查看 `mysql` 支持的字符集和比较规则：

show charset;

或者

show character set;

示例：

`Charset` 就是所支持的字符集，`Description` 是描述，`Default collation` 为对应的比较规则，`Maxlen` 字段表示所最大占用字节数量

**比较规则**

在上表中，`MySQL` 版本一共支持 `41` 种字符集，其中 `Default collation` 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如：`uft8_polish_ci` 表示以波兰语的规则比较，`uft8_spanish_ci` 是以西班牙语的规则比较，`utf8_general_ci` 是一种通用的比较规则。

<table> 
 <thead> 
  <tr> 
   <th>后缀</th> 
   <th>英文释义</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>_ai</td> 
   <td>accent insensitive</td> 
   <td>不区分重音</td> 
  </tr> 
  <tr> 
   <td>_as</td> 
   <td>accent sensitive</td> 
   <td>区分重音</td> 
  </tr> 
  <tr> 
   <td>_ci</td> 
   <td>case insensitive</td> 
   <td>不区分大小写</td> 
  </tr> 
  <tr> 
   <td>_cs</td> 
   <td>case sensitive</td> 
   <td>区分大小写</td> 
  </tr> 
  <tr> 
   <td>_bin</td> 
   <td>binary</td> 
   <td>以二进制方式比较</td> 
  </tr> 
 </tbody> 
</table>

> `utf8_unicode_ci` 和 `utf_general_ci` 对中、英文来说没有实质性的差别，`utf_general_ci` 速度较快，但是准确度稍差，`utf8_unicode_ci` 准确度高，但是速度稍慢，一般情况下用 `utf_general_ci` 就够了，如果应用中有德语、法语或者是俄语，就需要使用 `utf8_unicode_ci`

查看 `utf8mb4` 相关的字符集：

SHOW COLLATION LIKE 'utf8mb4%';

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

查看服务器的字符集和比较规则：

SHOW VARIABLES LIKE '%_server';

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

查看数据库的字符集和比较规则：

SHOW VARIABLES LIKE '%_database';

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

查看具体数据库的字符集和比较规则：

SHOW VARIABLES LIKE '%_database';

如果想要知道某一个数据库创建时是采用哪种字符集或比较规则，可以通过查看建库语句的 `sql` 查看：

show create database database_name;

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

如果想要知道某一个表创建时是采用哪种字符集或比较规则，可以通过查看建表语句的 `sql` 查看：

show create table table_name;

示例：

mysql> show create table t_access_log;
    +--------------+----------------------------------------------------------------------------------------------------------+
    | Table        | Create Table                                                                                             |
    +--------------+----------------------------------------------------------------------------------------------------------+
    | t_access_log | CREATE TABLE `t_access_log` (
      `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      `login_name` varchar(64) DEFAULT NULL COMMENT '登录名',
      `access_path` varchar(255) DEFAULT NULL COMMENT '访问路径',
      `access_ip` varchar(16) DEFAULT NULL COMMENT '访问IP',
      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `state` tinyint DEFAULT NULL COMMENT '访问状态',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='访问记录表'                   |
    +--------------+----------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

**utf8 与 utf8mb4 的区别**

`UTF-8` 字符集表示一个字符需要使用 `1~4` 个字节，通常情况下一些字符只需要 `1~3` 个字节就可以表示了，而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，`MySQL` 的设计者定了两个概念：

*  `utf8mb3`：阉割的 `UTF-8` 字符集，只使用 `1~3` 个字节表示字符
 *  `uft8mb4`：正宗的 `UTF-8` 字符集，只使用 `1~4` 个字节表示字符

在 `MySQL` 中 `utf8` 是 `utf8mb3` 的别名，所以在 `MySQL` 中使用字符集尽量采用 `uft8mb4`，比如说前面举例的存入 `emoji` 表情 “😃” ，就需要使用 `uft8mb4`。

--------------------

### 1.4 字符集设置 ###

如果安装的是 `mysql-5.7`，通常情况下，会在安装好数据库之后修改其配置文件来设置字符集

修改 `my.cnf` （或者 `my.ini`）文件，以 `linux` 上为例：

编辑 `my.cnf` 文件

vi /etc/my.cnf

![972ca792044f44e08174cc9b6cc98871.png][]

在 `[mysqld]` 标签下修改配置，例如：

character_set_server=utf8mb4

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

修改完配置文件后需要重启下 `mysql` 服务才会生效

# 启动 mysql
    systemctl restart mysqld

在这之后创建的库或者是表就会默认使用 `uft8mb4` 字符集，但之前创建的库或者表并不会自动修改其字符集

--------------------

### 1.5 已有库表字符集变更 ###

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

alter database database_name character set 'utf8mb4';

修改已创建数据库的字符集和比较规则：

alter database database_name character set 'utf8mb4' collate 'utf8_general_ci';

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

alter table table_name convert to character set 'utf8mb4';

修改已创建数据表的字符集和比较规则：

alter table table_name convert to character set 'utf8mb4' collate 'utf8_general_ci';

--------------------

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

从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据，从发送请求到返回结果的过程中可能会伴随着多次字符串的转换，其中主要涉及的三个变量如下：

<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>服务器处理请求时会把请求字符串从character_set_client 转为character_set_connection</td> 
  </tr> 
  <tr> 
   <td>character_set_results</td> 
   <td>服务器向客户端返回数据时使用的字符集</td> 
  </tr> 
 </tbody> 
</table>

具体流程如下图所示：

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

通常情况下，为了避免多次的字符串转换，通常情况下会将 `character_set_client`、`character_set_connection`、`character_set_results` 这三个系统变量设置成和客户端使用的字符集相一致

例如：

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

为了方便我们设置，`MySQL` 提供了一条非常简便的语句：

SET NAMES 字符集名;

例如：

SET NAMES utf8;

--------------------

## 二、大小写规范 ##

--------------------

### 2.1 Windows 和 Linux 平台区别 ###

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

而表名、变量因平台的关系会有所不同，在 `Windows` 下 `SQL` 是不区分大小写的，而在 `Linux` 下 `SQL` 却区分大小写

所以就可能会出现这样一种情况，比如说你的项目中使用了类似于 `quartz` 这种定时器，在 `Windows` 上创建的表都是小写，但是一旦迁移到 `Linux` 上就会出现以下错误：

![548b209c52464631b9b7c36b15292b2c.png][]

就是因为两个环境上对大小写敏感度不同所导致的

通常情况下我们可以通过以下命令查看大小写是否敏感：

SHOW VARIABLES LIKE '%lower_case_table_names';

*  Windows 下

*  Linux 下

**lower\_case\_table\_names 参数值的设置**

*  `0`：大小写敏感
 *  `1`：大小写不敏感，创建的表、数据库都是以小写形式存放在磁盘上，对于 `SQL` 语句都是转换为小写对表和数据库进行查找
 *  `2`：创建的表和数据依据语句上的格式存放，凡是查找都是转换为小写进行

所以在 `Linux` 下会严格区分数据库、表名、表的别名、变量名的大小写，而在 `Windows` 下都不区分大小写

--------------------

### 2.2 Linux 下大小写规则设置 ###

在 `Linux` 下如果想要设置为大小写不敏感，`mysql-5.7` 只要在 `my.cnf` 文件中的添加或者修改 `lower_case_table_names` 的值为 `1` ，然后再重启数据库即可

这里需要注意的是：在重启数据库之前需要将原来的数据和表名转换为小写，否则将找不到数据库和表

修改 `my.cnf` （或者 `my.ini`）文件

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

添加以下配置：

[mysqld]  
    lower_case_table_names = 1

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

接着再重启 `mysql`

# 重启 mysql
    systemctl restart mysqld

但是如果数据库使用的是 `mysql-8.0`，且数据库已经初始化过了，`mysql` 是不允许修改大小写敏感的

如果执意要修改可参见博客：[MySQL8 设置大小写敏感][MySQL8]

--------------------

### 2.3 SQL 编写建议 ###

在实际开发中，命名规范建议：

*  ① 关键字和函数名名称全部用大写
 *  ② 数据库名、表名、表别名、字段名、字段别名等全部小写
 *  ③ `SQL` 语句必须以分号 `;` 结尾

--------------------

## 三、sql\_mode 的合理设置 ##

--------------------

### 3.1 sql\_mode 的介绍 ###

`sql_mode` 会影响 `MySQL` 支持的 `SQL` 语法以及它执行的数据验证检查，通过设置 `sql_mode` 可以完成不同严格程度的数据校验，有效地保障数据的准确性。

`MySQL` 服务器可以在不同的 `SQL` 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 `sql_mode` 系统变量的值。

`mysql-5.6` 和 `mysql-5.7` 默认的 `sql_mode` 模式参数是不一样的：

*  `mysql-5.6` 的 `mode` 默认为空，即：`NO_ENGINE_SUBSTITUTION`，其实表示一个空值，相当于没有什么模式设置，可以理解为 `宽松模式`，在这种设置下可以允许一些非法操作的，比如允许插入一些非法数据的插入
 *  `mysql-5.7` 的 `mode` 默认为 `STRICT_TRANS_TABLES`，也就是 `严格模式`，用于进行数据的严格校验，错误数据不能插入，会报错，并且事务回滚

`sql_model` 常用来解决下面几类问题：

*  通过设置 `sql_model`, 可以完成不同严格程度的数据校验，有效地保障数据准备性
 *  通过设置 `sql_model` 为宽松模式，来保证大多数 `sql` 符合标准的 `sql` 语法，这样应用在不同数据库之间进行迁移时，则不需要对业务 `sql` 进行较大的修改
 *  在不同数据库之间进行数据迁移之前，通过设置 `sql_model` 可以使 `MySQL` 上的数据更方便地迁移到目标数据库中

--------------------

### 3.2 sql\_mode 常用值 ###

<table> 
 <thead> 
  <tr> 
   <th align="left">值</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">ONLY_FULL_GROUP_BY</td> 
   <td align="left">对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现,那么这个 SQL 是不合法的，因为列不在 GROUP BY 从句中</td> 
  </tr> 
  <tr> 
   <td align="left">NO_AUTO_VALUE_ON_ZERO</td> 
   <td align="left">该值影响自增长列的插入。默认设置下插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为 0，而该列又是自增长的，那么这个选项就有用了</td> 
  </tr> 
  <tr> 
   <td align="left">STRICT_TRANS_TABLES</td> 
   <td align="left">在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制</td> 
  </tr> 
  <tr> 
   <td align="left">NO_ZERO_IN_DATE</td> 
   <td align="left">在严格模式下，不允许日期和月份为零</td> 
  </tr> 
  <tr> 
   <td align="left">NO_ZERO_DATE</td> 
   <td align="left">设置该值，mysql 数据库不允许插入零日期，插入零日期会抛出错误而不是警告</td> 
  </tr> 
  <tr> 
   <td align="left">ERROR_FOR_DIVISION_BY_ZERO</td> 
   <td align="left">在 INSERT 或 UPDATE 过程中,如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时 MySQL 返回 NULL</td> 
  </tr> 
  <tr> 
   <td align="left">NO_AUTO_CREATE_USER</td> 
   <td align="left">禁止 GRANT 创建密码为空的用户</td> 
  </tr> 
  <tr> 
   <td align="left">NO_ENGINE_SUBSTITUTION</td> 
   <td align="left">如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常</td> 
  </tr> 
  <tr> 
   <td align="left">PIPES_AS_CONCAT</td> 
   <td align="left">将 <code>||</code> 视为字符串的连接操作符而非或运算符，这和 <code>Oracle</code> 数据库是一样的，也和字符串的拼接函数 Concat 相类似</td> 
  </tr> 
  <tr> 
   <td align="left">ANSI_QUOTES</td> 
   <td align="left">启用 ANSI_QUOTES 后，不能用双引号来引用字符串，因为它被解释为识别符</td> 
  </tr> 
 </tbody> 
</table>

--------------------

### 3.3 模式查看与设置 ###

如果想要查看当前数据库用的 `sql_mode` 类型，可通过以下命令进行查看：

select @@session.sql_mode;
    # 或者
    select @@global.sql_mode;

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

临时设置方式：

# 修改当前会话的 sql_mode
    set session sql_mode = '';
    
    # 修改全局 sql_mode
    set global sql_mode = '';

永久设置方式：

在 `my.cnf` 或者 `my.ini` 文件中新增：

[mysqld]
    sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

设置完成需要重启 `mysql`

在实际生产上，一般是采用 `临时+永久` 结合的方式，临时设置保证当前设置其效果，永久设置保证重启 `mysql` 之后依然使用所配置的 `sql_mode`。

--------------------

下篇：[第二章、数据库目录结构与文件系统][Link 1]

[Link 1]: https://blog.csdn.net/xhmico/article/details/138151317
[82de2de3a8014d66909564831bf568be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/3d5462076f83414faf753f8c186c540c.png
[c348ba384e4948988a29510ac849842d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/1ac3f56e03614be68d815536eee35d67.png
[bilibili-_-MySQL]: https://www.bilibili.com/video/BV1iq4y1u7vj/?t=3.9&p=101
[Linux-_MySQL]: https://blog.csdn.net/xhmico/article/details/125197747
[54d0e005b8b7443aa96e2e6eea7decc6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/841f9242a8ce45b5aa837f4193d38479.png
[6daec0a0a0e646e28ada12d463fb795c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/995db3117968479e8b9faba0f6ea1e40.png
[2417ce198a2d4d50b5000046dcaffa4a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/163feb5ee1904f4b97970d415b363c89.png
[c3f5c07cdab041e7a75bc0b0364cf971.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/792df0f2599446bb8fb0e4cf2cfce06e.png
[972ca792044f44e08174cc9b6cc98871.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/6a3d581df5004cefb9bed241bd6bb10d.png
[906fe0ec88c342fa9aa44b15abd2b8f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/2c2a4a7746eb4c2ca81b2fe9f3dacd60.png
[44b43b828e264bc6a853fa15b6700484.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/5f7fe16e09724b158438c10439fb1a9b.png
[548b209c52464631b9b7c36b15292b2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/c39955e963864f44b052aa1aa58d591b.png
[ac777753d6694e4b9863d7a7a42e34af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/38cb26bae2984b5189c4005f6c0fdec9.png
[MySQL8]: https://blog.csdn.net/xhmico/article/details/136680013
[020ee23a2b7c4dcb9842cdded75a5271.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/06/df9ab97e0c65478593c02b5fe04149dd.png