MySQL进阶：一、Linux环境安装MySQL

1.安装步骤

1.1 安装Docker

略…

1.2 拉取MySQL镜像

查看docker上的mysql的镜像

docker search mysql:5.7

拉取5.7版本的mysql

docker pull mysql:5.7

1.3 创建实例

启动MySQL镜像容器实例

docker run -itd --name mysql-test -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
# 主机端口 : 容器端口  本服务器3306端口已被占用

进入命令行

docker exec -it mysql57 /bin/bash

进入MySQL

MySQL -uroot -p

因仅用于学习使用，此处未作文件映射，删除实例会导致数据消失。

2.修改MySQL5.7字符集

在MySQL5.7之前，默认字符集是latin1，utf8默认等于utf8mb3。使用latin1编码的情况下，在插入中文数据时会报错。从MySQL8.0开始，默认编码已经改为utf8mb4，解决乱码问题。

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

show variables like 'char%'

# 可以看到5.7版本默认的字符集包括latin1的情况。
# 其中部分utf8是安装后修改的。
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

操作2： 修改字符集

# 编辑MySQL配置文件
# 本人文件路径为/etc/mysql/my.cnf
# 在文件中加上
[client]                   # 如果没有[client]段，就自己添加上去
default-character-set=utf8
[mysqld]
character-set-server=utf8		#5.7版本使用utf8mb4会导致服务无法启动
collation-server=utf8_general_ci

配置完成后需要重启MySQL服务。

systemctl restart mysqld

重启后，新创建的表的编码集会根据修改后的utf8，但不会影响修改前创建的数据库、表的编码集，可以通过alter语句自行修改

alter database dbtest1 character set 'utf8';

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

各级别的字符集

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

• 服务器级别
• 数据库级别
• 表级别
• 列级别(字段级别)

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

对于上面查询出来的字符集设置，分别对应

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

如果某一级别的字符集和比较规则没有指明，那么会默认继承该级别上一级的字符集和比较规则。即

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

字符集和比较规则

1.utf8和utf8mb4

utf8字符集表示一个字符需要使用1-4个字节，一般使用的字符只需要1-3个字节表示，而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以出现了以下两种情况：

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

对于emoji表情，就需要4个字节来表示。

2. 比较规则

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

最后一列Maxlen，代表该种字符集表示一个字符最多需要几个字节。

# 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

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

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

总而言之，所有的字符集需要保证和程序传递的数据字符集一致。

3.SQL大小写规范

3.1 Windows & Linux的区别

Windows系统默认对大小写不敏感。Linux系统大小写敏感。

# 查看大小写是否敏感的命令
show variables like '$lower_case_table_names%';

# Windows下的情况
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

#Linux下的情况
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

从上述结果可知：

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

对于大小写的区别：

MySQL在Linux下，数据库、表名、列名、别名的大小写规则如下：

1. 数据库名、表名、表的别名、变量名严格区分大小写。
2. 关键字、函数名称在SQL中不区分大小写。
3. 列名(字段名)、列的别名不区分大小写。

Windows下均不区分大小写。

SQL编写建议

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

4. sql_mode

7.1 宽松模式 vs 严格模式

宽松模式： 如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受， 并且不报错。

举例 ： 我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时 char(10) ，如果我 在插入数据的时候，其中name这个字段对应的有一条数据的 长度超过了10 ，例如’1234567890abc’，超 过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为 了’1234567890’，而’abc’就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没 有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。

应用场景 ： 通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据 库之间进行 迁移 时，则不需要对业务sql 进行较大的修改。

严格模式： 出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所以在 生产等环境 中，我们必须采用的是严格模式，进而开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。

开发经验 ： MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发 中，我们应该在自己 开发的项目程序级别将这些校验给做了 ，虽然写项目的时候麻烦了一些步骤，但是这 样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。

改为严格模式后可能会存在的问题：

1、停止MySQL服务

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

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

4、启动MySQL服务 若设置模式中包含了 NO_ZERO_DATE ，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而 不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配 DEFAULT ‘0000-00-00 00:00:00’（零时间戳），这显然是不满足sql_mode中的NO_ZERO_DATE而报错。

过程中使用的版本为MySQL5.7.36
参考资料： 尚硅谷MySQL2022–宋红康