Java道经第2卷 - 第3阶 - MySQL（一）
传送门：JB2-3-MySQL（一）
传送门：JB2-3-MySQL（二）
心法：本章使用 Maven 父子结构项目进行练习
练习项目结构如下：
|_ v2-3-web-mysql
|_ test
武技：搭建练习项目结构
- 创建父项目 v2-3-web-mysql，删除 src 目录。
- 创建子项目 test，不需要添加任何依赖。
S01. 关系型数据库
E01. MySQL基础概念
心法：关系型数据库适用于数据结构固定、需要强事务一致性（如金融交易、订单管理）、复杂关联查询的场景，而非关系型数据库更适合数据结构灵活（如 JSON 文档）、高并发读写、无需复杂关联（如日志记录、用户行为分析、实时数据缓存）的场景。
数据库 DataBase, DB：一个可以持久化存储数据以及简单分析数据的应用软件。
关系型数据库 Relational Database Management System, RDMS：由二维表组成，格式一致，易于维护，支持事务，不灵活，如 MySQL，Oracle 等。
非关系型数据库 Non-Relational Database Management System, NRDMS：由文档，键值对，图片等组成，使用灵活，但不支持事务，如 Redis，MongoDB 等。
结构化查询语言 Structured Query Language, SQL：操作关系型数据库的标准语言，目前数据库厂商实现的都是 SQL92 或 SQL99 标准。
1. MySQL基础概念
心法：MySQL 是一款优秀的 RDMS 关系型数据库，由瑞典 mysql-ab 公司开发，目前属于 Oracle 旗下产品，采用双授权政策，分为社区版（开源免费，支持定制）和商业版，体积小，速度快。
MySQL 使用标准的 SQL 数据语言形式，跨平台，支持多种语言，如 C，C++，Python，Java，PHP 等。
仓库大小：MySQL 支持 5000 万条记录的数据仓库：
- 32 位系统表文件最大可支持 4GB。
- 64 位系统支持最大的表文件为 8TB。
存储引擎：
- MyISAM 引擎：MySQL 在 5 版本之前默认使用：
- 不支持事务和行级锁，只支持表级锁，查询效率较高，但在并发写入时性能较差。
- 适用于以读操作为主、对数据一致性要求不高的应用，如博客系统、新闻网站等。
- InnoDB 引擎：MySQL 在 5 版本之后默认使用：
- 支持事务处理、行级锁、外键约束等特性，提供了较好的数据一致性和并发支持。
- 适用于对数据一致性要求高、有大量并发读写操作的应用，如电商系统、银行系统等。
- Memory 引擎：MySQL 全版本支持切换使用，但不默认使用：
- 数据存储在内存中，读写速度非常快，但数据在服务器重启后会丢失。
- 支持表级锁，不支持事务。
- 使用哈希索引，适合于对速度要求极高、数据量较小且不需要持久化存储的数据。
- 常用于临时表、缓存表或用于存储一些实时性要求高但不需要长期保存的数据，如在线游戏中的临时数据、统计信息等。
2. MySQL组件架构
心法：一个 OS 中可以同时安装多个不同版本的 MySQL Server，一个 Server 中可以同时创建多个 MySQL Instance（用户是共享一个 Server 中的所有 Instance 的），一个 Instance 中可以同时创建多张 Table 表，一个 Table 中可以存放多条 Record 记录。
E02. MySQL服务搭建
武技：在 Docker 中搭建 MySQL 单机容器
1. 安装MySQL容器
- 准备相关目录：
# 创建MySQL相关目录
mkdir -p /opt/mysql/single/conf;
mkdir -p /opt/mysql/single/data;
mkdir -p /opt/mysql/single/log;
chmod -R 777 /opt/mysql;
- 开发配置文件：my.cnf 是 MySQL 数据库的核心配置文件，用于定义服务器的运行参数和行为：
# 创建空白配置文件
touch /opt/mysql/single/conf/my.cnf
填写内容如下：
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-time-zone = 'Asia/Shanghai'
default_authentication_plugin = mysql_native_password
- 降低配置文件权限：否则系统上任何用户都可以修改该文件，存在安全风险：
# 降低 my.cnf 文件的权限，否则系统上任何用户都可以修改该文件，存在安全风险
chmod 644 /opt/mysql/single/conf/my.cnf;
- 搭建 MySQL 单机容器：
# 拉取镜像（二选一）
docker pull mysql:8;
docker pull registry.cn-hangzhou.aliyuncs.com/joezhou/mysql:8;
# 创建并运行容器
# 参数 `--network my-net`: 使用自定义桥接网络
# 参数 `-e MYSQL_ROOT_PASSWORD=root`: 设置root用户的密码
# --restart=always 自启动
docker run -itd --name mysql \
-p 3306:3306 --network my-net \
-e MYSQL_ROOT_PASSWORD=root \
-v /opt/mysql/single/conf:/etc/mysql/conf.d \
-v /opt/mysql/single/data:/var/lib/mysql \
-v /opt/mysql/single/log:/var/log/mysql \
registry.cn-hangzhou.aliyuncs.com/joezhou/mysql:8;
# 查看容器
docker ps -a --format "table {{.ID}}\t{{.Names}}\t{{.Ports}}"
docker logs mysql --tail 30
# 永久开放3306端口
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
- 查看 MySQL 版本：
# 进入容器的内部: 可用 `exit` 命令退出容器
docker exec -it mysql bash
# 查看MySQL版本
mysqladmin --version
# 登录MySQL数据库
mysql -uroot -p
# 查看当前登录的用户名
select user();
2. 安装可视化工具
心法：MySQL 可视化工具是一类通过图形化界面（GUI）替代传统命令行操作，帮助用户更便捷地管理、操作和监控 MySQL 数据库的软件工具，它的核心价值在于降低技术门槛、提升操作效率，尤其适合非技术人员或需要频繁进行数据库管理的场景。
MySQL 常用可视化工具如下：
- MySQL GUI Tools：MySQL 官方提供的图形化管理工具，功能强大，可惜没有中文界面。
- MySQL Workbench：MySQL 官方推出的一个开源的可视化工具，支持数据库设计，SQL 开发。服务器管理等功能。
- Navicat for MySQL：一款商业化的 MySQL 可视化工具，提供了完整的数据库管理功能，推荐使用。
- phpMyAdmin：一款基于 Web 的 MySQL 可视化工具，可以通过浏览器访问和管理 MySQL 数据库。
- HeidiSQL：一款免费开源的 MySQL 可视化工具，支持多种数据库管理操作。
- DBeaver：一款跨平台的数据库管理工具，支持多种数据库管理操作。
- DataGrip：JetBrains 发布的多引擎数据库环境，支持多种数据库，包括 MySQL、PostgreSQL、Microsoft SQL Server、Oracle 等。它提供了强大的 SQL 编辑和调试功能，以及数据库结构和数据的可视化展示。
- SQLyog：一款易于使用、快速而简洁的图形化管理 MySQL 数据库的工具，能够在任何地点有效地管理数据库。它提供了直观的用户界面，支持数据库设计、查询执行、数据备份和恢复等功能。
- MySQL ODBC Connector：MySQL 官方提供的 ODBC 接口程序，系统安装该程序后，就可以通过 ODBC 来访问 MySQL，这样就可以实现 SQLServer、Access 和 MySQL 之间的数据转换，还可以支持 ASP 访问 MySQL 数据库。
武技：使用 IDEA 作为 MySQL 可视工具
- 点击
Database - 加号 - Data Source - MySQL进入 MySQL 配置页面：
- 在 General 选项卡中添加 MySQL 本地驱动 jar 包，该 jar 可以直接在你的 Maven 仓库中寻找：
- 在 General 选项卡中填写 MySQL 连接的基本信息：
- 在 SSH/SSL 选项卡中选择 SSH 配置，若 MySQL 未安装到虚拟机则略过此步骤：
S02. 数据控制语言
心法：数据控制语言 Data Control Language（DCL），用于对 DB 用户进行 grant 赋予权限和 revoke 回收权限操作。
注意：创建用户和删除用户的操作本质上属于 DDL 操作，但为了演示 DCL，也在本章直接讲解。
常用权限列表如下：权限范围按 粒度从小到大 排列（如列 < 表 < 库 < 服务器）：
| 权限名称 | 作用范围 | 具体权限描述 |
|---|---|---|
all | 整个服务 | 拥有对整个服务的所有操作权限。 |
select | 表 / 列 | 允许对表 / 列执行查询（读取）操作。 |
insert | 表 / 列 | 允许向表中插入新行数据。 |
update | 表 / 列 | 允许更新表中的行数据。 |
delete | 表 | 允许删除表中的行数据。 |
create | 库 / 表 / 索引 | 允许创建数据库、表或索引。 |
drop | 库 / 表 / 视图 | 允许删除数据库、表或视图。 |
reload | 整个服务 | 允许使用 flush 语句重新加载配置或刷新日志等。 |
grant option | 库 / 表 / 存储过程 | 允许将自身权限授予其他用户（授权权限）。 |
references | 库 / 表 | 允许操作外键约束的父表（与表关联关系相关）。 |
index | 表 | 允许创建或删除表的索引。 |
alter | 表 | 允许修改表结构（如添加 / 删除字段、修改字段类型等）。 |
show databases | 服务器 | 允许查看数据库列表（显示所有数据库名称）。 |
super | 服务器 | 拥有超级权限（如终止其他用户进程、修改系统级参数等）。 |
create temporary tables | 表 | 允许创建临时表。 |
lock tables | 库 | 允许对库中的表执行锁定操作（阻塞其他用户的读写）。 |
execute | 存储过程 | 允许执行存储过程。 |
replication client | 服务器 | 允许查看主 / 从服务器状态及二进制日志信息。 |
replication slave | 服务器 | 允许作为从服务器参与主从复制（获取主服务器数据）。 |
create view | 视图 | 允许创建视图。 |
show view | 视图 | 允许查看视图的定义和数据。 |
create routine | 存储过程 | 允许创建存储过程或函数。 |
alter routine | 存储过程 | 允许修改或删除已有的存储过程或函数（需注意权限依赖）。 |
create user | 服务器 | 允许创建、修改或删除用户账号。 |
event | 库 | 允许创建、修改、删除或查看数据库中的事件（定时任务）。 |
create tablespace | 服务器 | 允许创建、修改或删除表空间及日志文件（需谨慎操作，涉及物理存储）。 |
proxy | 服务器 | 允许代理其他用户执行操作（模拟其他用户身份）。 |
usage | 服务器 | 无实际权限（默认权限，表示用户仅能连接服务器，但无法执行任何操作）。 |
E01. DCL用户操作
武技：创建 joezhou 用户并赋予全部权限
-- 查看当前用户 [root]
select user();
-- 查看全部用户 [root]
select user, host from mysql.user;
-- 删除指定用户 [root]: 删除用户的时候必须指定IP地址
drop user if exists 'joezhou'@'%';
-- 创建新用户 [root]
-- 参数 `%` 表示允许该用户访问远程库，可替换为 `localhost` 或具体IP地址
-- 参数 `identified by '123'` 若整体省略表示不设置密码
create user 'joezhou'@'%' identified by '123';
-- 修改用户密码 [root]: 将密码修改为 joezhou
alter user 'joezhou'@'%' identified with MYSQL_NATIVE_PASSWORD by 'joezhou';
-- 查询用户权限 [root]: USAGE表示无权限
show grants for 'joezhou'@'%';
-- 回收权限 [root]: all表示回收全部权限，可替换为具体权限列表，逗号分隔
revoke all on *.* from 'joezhou'@'%';
-- 为用户赋权 [root]
-- 参数 `all privileges`: 表示赋予该用户全部权限，可替换为具体权限列表，逗号分隔
-- 参数 `on *.*`: 表示用户对全部库的全部表开启权限，可替换为具体库或具体表
-- 参数 `with grant option`: 表示该用户可以为其他用户授权，可选
grant all privileges on *.* to 'joezhou'@'%' with grant option;
-- 刷新权限 [root]: 用户赋权之后，要立刻执行一次刷新权限命令，否则可能导致赋权失败
flush privileges;
S03. 数据定义语言
心法：数据定义语言 Data Definition Language（DDL）用于对 DB 元数据如表，用户，列，索引等进行 create，drop，set 等操作。
E01. DDL实例操作
武技：创建数据库实例 mysql8 并设置 utf8mb4 字符编码。
-- 展示MySQL服务端中的全部数据库实例
show databases;
-- 删除指定数据库实例
drop database if exists test;
-- 创建数据库实例: 数据库实例名为 `mysql8`
create database mysql8 character set utf8mb4;
-- 切换当前数据库: 切换为 `mysql8`
use mysql8;
-- 查看当前数据库版本和名称
select version(), database();
-- 查看当前数据库的全局变量 - 时区: 支持直接使用like进行模糊筛选
show variables like '%time_zone%';
-- 将时区修改为Asia/Shanghai
set time_zone = 'Asia/Shanghai';
-- 查看当前数据库的编码
select @@character_set_database;
-- 修改指定数据库的编码
alter database mysql8 character set utf8mb4;
E02. DDL表格操作
心法：MySQL 的数据最终都存放在表中，每个表最多存储 4096 列数据，每个表的大小不能超过65535 字节，每个表的记录行数建议控制在 500 万以内。
1. 数据库三范式
心法：数据库设计三范式（3NF）是帮助我们设计更好的数据库表的规范，不一定非要严格执行这个标准，但它对你设计数据库来说，无疑是一个很好的建议和帮助。
第一范式 - 1NF：表中的每一列都保持了原子性，不能再拆分，则满足1NF：
- 如：用户表｛姓名，性别，电话｝，其中｛电话｝可以再拆成｛家庭电话，公司电话｝，所以不满足INF。
第二范式 - 2NF：满足 1NF 基础上，表有主键，且每一列都与主键相关，则满足 2NF：
- 如：订单表｛订单编号（PK），商品编号，下单日期｝，其中｛商品编号｝和｛订单编号｝无关，所以不满足2NF，应该删除，再使用中间表。
第三范式 - 3NF：满足 2NF 基础上，每一列都与主键直接相关，而不是间接相关，则满足 3NF：
- 如：订单表｛订单编号（PK），买家编号，买家姓名｝，其中所有字段都和订单相关，满足 2NF，但实际上是｛买家姓名｝和｛买家编号｝直接相关，｛买家编号｝和｛订单编号｝直接相关，导致｛买家姓名｝和｛订单编号｝不是直接相关，而是间接相关，所以不满足 3NF，应该将｛买家姓名｝移动到买家表中，而不应该出现在订单表中。
2. 表设计规范
心法：有时候为了效率，数据库表的设计可能会违反数据库三范式。
必设字段：MySQL 每张表都强烈建议设置以下三个字段：
id long auto_increment：主键，对应 Long 型属性。created datetime：首次创建时间，对应 LocalDateTime 型属性。updated datetime：最后修改时间，对应 LocalDateTime 型属性。
表名规范：
- MySQL 表名和字段名可由字母，数字，下划线组成。
- MySQL 表名建议使用 “全小写 + 下划线” 格式。
- MySQL 表名且禁止使用复数单词和缩写。
表名前缀：表名允许使用以下前缀作为业务说明：
- ums_XXX：用户管理模块，如用户表，角色表，权限表，菜单表等。
- pms_XXX：商品管理模块，如商品表，商品分类表等。
- oms_XXX：订单管理模块，如订单表，订单明细表，购物车表等。
- cms_XXX：内容管理模块，如评论表，举报表等。
- sms_XXX：营销管理模块，如广告轮播图表，活动表等。
3. 表数据类型
心法：数据库表字段的数据类型，一般能用小的就别用大的，能用固定的就别用变化的。
常用的数字类型如下：小数类型统一使用 decimal，禁止使用 float 和 double，规避丢失精度的问题：
| 数据类型 | 中文 | 类型大小 | 描述 |
|---|---|---|---|
| tinyint | 极小整数 | 1 字节 | 有符号范围 -128 ~ 127 无符号范围 0 ~ 255 |
| smallint | 小整数 | 2 字节 | 有符号范围：-32768 ~ 32767 无符号范围：0 ~ 65535 |
| mediumint | 中等整数 | 3 字节 | 有符号范围：-8388608 ~ 8388607 无符号范围：0 ~ 16777215 |
| integer(int) | 整数 | 4 字节 | 有符号范围 -232 ~ 232-1 无符号范围 0 ~ 约 42.9 亿 |
| bigint | 大整数 | 8 字节 | 有符号范围 -263 ~ 263-1 无符号范围 0 ~ 约 1019 |
| float | 单精度浮点数 | 4 字节 | 精确到小数点后 6 - 7 位 范围约 ±1.175494351×10-38 ~ ±3.402823466×1038 |
| double | 双精度浮点数 | 8 字节 | 精确到小数点后 15 - 17 位 范围约 ±2.2250738585072014×10-308 ~ ±1.7976931348623157×10308 |
| decimal(n, m) | 浮点数 | 取决于精度和规模 | m 表示小数位数，小数位数超出 m 则四舍五入，不足 m 则补 0 m + 整数位数 + 1(小数点) 的总和必须不超出 n，超出报错 |
常用的时间类型如下：日期类型推荐统一使用 datetime 类型：
| 数据类型 | 中文 | 类型大小 | 描述 |
|---|---|---|---|
| time | 时间 | 3 字节 | 范围：-838:59:59 ~ 838:59:59（可表示负数，常用于时间间隔计算） |
| date | 日期 | 3 字节 | 范围 1000-01-01 ~ 9999-12-31 |
| timestamp | 时间戳 | 4 字节 | 范围：‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC 自动更新为当前时间（需配合 ON UPDATE CURRENT_TIMESTAMP） |
| datetime | 日期 + 时间 | 8 字节 | 范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
常用的文本类型如下：前缀字节用于记录数据实际长度，例如 text 的 2 字节前缀可表示最大 2^16 - 1 = 65535 字节的数据长度：
| 数据类型 | 中文 | 类型大小 | 描述 |
|---|---|---|---|
| char(n) | 定长字符串 | 一个 U8 中文占 3 个字节（无字节前缀） | 固定长度为 n，实际内容不足时使用空格补充至 n |
| varchar(n) | 变长字符串 | 一个 U8 中文占 3 个字节（1 ~ 2 字节前缀） | 最大长度为 n，相比 char，空间利用率提高但查询效率降低 长度尽量保持在 5000 字节以内。 |
| tinytext | 微小文本 | 最大 255 字节（1 字节前缀） | 存储短文本，如状态描述、标签等 |
| text | 文本 | 最大 65535 字节（2 字节前缀） | 存储中等长度文本，如文章段落、评论内容 |
| mediumtext | 中等文本 | 最大 16777215 字节（3 字节前缀） | 存储较长文本，如小说章节、详细说明 |
| longtext | 长文本 | 最大 4294967295 字节（4 字节前缀） | 存储超大文本，如日志文件、长篇内容 |
常用的二进制类型如下：
| 数据类型 | 中文 | 类型大小 | 描述 |
|---|---|---|---|
| tinyblob | 微小二进制 | 最大 255 字节（1 字节前缀） | 存储小二进制数据，如缩略图、小文件 |
| blob | 二进制 | 最大 65535 字节（2 字节前缀） | 存储中等二进制数据，如普通图片、音频片段 |
| mediumblob | 中等二进制 | 最大 16777215 字节（3 字节前缀） | 存储较大二进制数据，如高清图片、视频片段 |
| longblob | 长二进制 | 最大 4294967295 字节（4 字节前缀） | 存储超大二进制数据，如大型文件、数据库备份 |
武技：在 mysql8 数据库中创建 user 表
-- 查看当前数据库中的表: 此命令无法看到临时表
show tables;
-- 删除表
drop table mysql8.user;
-- 创建表 `user`：表和字段均建议使用 comment 指定注释
-- auto_increment：标记为该列自增，只能用于数字列
-- primary key (`id`)：指定主键，每个Innodb表都建议设计一个主键以提升查询效率
-- engine innodb：指定引擎，默认追随数据库实例的配置
-- default charset utf8mb4：指定字符集，默认追随数据库实例的配置
create table mysql8.user
(
`id` bigint auto_increment comment '主键',
`username` varchar(64) not null default '' comment '登录账号',
`password` varchar(64) not null default '' comment '登录密码',
`realname` char(4) not null default '' comment '真实姓名',
`age` tinyint not null default 0 comment '用户年龄',
`gender` tinyint not null default 2 comment '用户性别，0女1男2保密',
`weight` decimal(5, 2) not null default 0.00 comment '用户体重，单位斤',
`created` datetime not null default CURRENT_TIMESTAMP comment '创建日期',
`updated` datetime not null default CURRENT_TIMESTAMP comment '修改日期',
primary key (`id`)
)
engine = innodb
default charset = utf8mb4
collate = utf8mb4_0900_ai_ci
comment '用户表';
-- 查看表 `user` 的建表语句
show create table mysql8.user;
-- 查看表 `user` 的表结构
desc mysql8.user;
-- 查看表user的详细表结构
select table_name '表名',
column_name '字段名称',
column_type '字段类型',
character_maximum_length '字段长度',
is_nullable '是否必填',
column_default '默认值',
column_comment
from information_schema.columns
where table_schema = 'mysql8' and table_name = 'user';
-- 增加一列: 为表user增加一列 `test`
alter table mysql8.user add test varchar(512) not null default '';
-- 修改列类型: 修改表user中的 `test` 列的类型为 `varchar(1024)`
alter table mysql8.user modify test varchar(1024) not null default '';
-- 修改列名称: 修改表user中的 `test` 列的名称为 `test02`
alter table mysql8.user change test test02 varchar(1024) not null default '';
-- 删除列: 删除表user中的 `test02` 列
alter table mysql8.user drop column test02;
-- 快速复制表user为表 `user_backups`: 包括表结构和表数据
create table mysql8.user_backups (select * from test.user);
-- 重命名表: 将表 `user_backups` 重命名为 `user_bak`
rename table mysql8.user_backups to user_bak;
-- 删除表: 删除表 `user_bak`
drop table if exists mysql8.user_bak;
-- 创建临时表 `tmp_user_20240225`: 使用 `temporary` 关键字
-- 临时表用于保存临时数据，仅当前连接可用，关闭连接时自动销毁并释放资源
-- 临时表的表名建议以 `tmp_` 为前缀并以日期为后缀
-- 临时表在Idea的Database面板中不可见，但支持正常的CRUD和drop操作，不支持重命名等操作
drop table if exists tmp_user_20240225;
create temporary table tmp_user_20240225
(
`id` int unsigned auto_increment comment '临时表主键',
primary key (id)
) comment '用户临时表';
-- 查看临时表结构
desc tmp_user_20240225;
E03. DDL约束操作
心法：约束就是对 column 添加一些强制校验规则以保证数据的正确性，添加约束前尽量保持表中无数据或者无错误数据。
1. 非空约束
心法：非空约束 not null 规定在字段插入 null 值时报错。
非空列可以提高查询效率，建议配合 default 一起使用，但不要对外键字段设置非空约束。
添加非空约束：
- 造表时：在某个字段末尾添加
not null default 默认值 - 造表后：
alter table 表名 modify 字段 类型 not null default 默认值
删除非空约束：alter table 表名 modify 字段 类型 null
武技：测试非空约束的操作
-- 创建非空约束测试表（造表时，直接添加非空约束）
create table null_test
(
id bigint,
name varchar(50) not null default ''
) comment '非空约束测试表';
-- 造表后，为指定字段添加非空约束
alter table mysql8.null_test modify id bigint not null default 0;
-- 删除指定字段上的非空约束
alter table mysql8.null_test modify id bigint null;
-- 查看 null_test 表的非空约束
desc mysql8.null_test;
2. 唯一约束
心法：唯一约束 unique key 规定在字段插入重复值时报错。
添加唯一约束：
- 造表时：在某个字段末尾添加
unique或在字段列表整体末尾添加unique (字段) - 造表后：
alter table 表名 add constraint 唯一约束名 unique (字段)：constraint 唯一约束名缺省时表示使用字段名作为唯一约束名。
删除唯一约束：alter table 表名 drop constraint 唯一约束名
武技：测试唯一约束的操作
-- 创建唯一约束测试表
create table unique_test
(
-- 造表时，直接添加唯一约束（方式一）
id bigint unique,
name varchar(50),
gender tinyint,
age int,
-- 造表时，直接添加唯一约束（方式二）
unique (name)
) comment '唯一约束测试表';
-- 造表后，为指定字段添加唯一约束，指定约束名为 uk_gender
alter table test.unique_test
add constraint uk_gender unique (gender);
-- 造表后，为指定字段添加唯一约束，默认唯一约束名为字段名
alter table test.unique_test
add unique (age);
-- 删除指定字段上的唯一约束，通过约束名称删除
alter table test.unique_test
drop constraint uk_gender;
-- 查看 unique_test 表的的唯一约束
select table_name, constraint_name, constraint_type, table_schema
from information_schema.table_constraints
where table_name = 'unique_test' and TABLE_SCHEMA = 'test';
3. 主键约束
心法：主键约束 primary key 规定该列唯一且非空，一张表只能存在一个主键约束列。
表中必须为唯一的自增列指定为主键。
添加主键约束：
- 造表时：在某个字段末尾添加
primary key或在字段列表整体末尾添加primary key (字段) - 造表后：
alter table 表名 add primary key (字段)：- 约束名默认为 PRIMARY
删除主键约束：alter table 表名 drop primary key
武技：测试主键约束的操作
-- 创建主键约束测试表
create table primary_test
(
id bigint,
-- 造表时，直接指定主键约束
primary key (id)
) comment '主键约束测试表';
-- 删除指定字段的主键约束
alter table mysql8.primary_test drop primary key;
-- 查看 primary_test 表的的主键约束
select table_name, constraint_name, constraint_type, table_schema
from information_schema.table_constraints
where table_name = 'primary_test'
and TABLE_SCHEMA = 'mysql8';
4. 外键约束
心法：外键约束 foreign key 就是用主表的某字段连接从表的主键或唯一约束字段，二者必须同类型，同长度，同符号。
建议尽量避免使用外键约束，虽提高了安全性，但影响效率，但一定要在关联字段上建立索引。
添加外键约束：
- 造表时：在字段列表整体末尾添加
foreign key (外键字段) references 从表(从表主键) - 造表后：
alter table 表名 add constraint 外键约束名 foreign key (字段) references 从表(主键)：constraint 外键约束名缺省时表示使用随机名作为外键约束名。
删除外键约束：alter table 表名 drop foreign key 外键约束名
武技：测试外键约束的操作
-- 创建班级表
create table mysql8.clazz
(
id bigint auto_increment,
title varchar(20),
primary key (id)
) comment '班级表';
-- 添加两条部门信息
insert into mysql8.clazz values (1, '三年一班'), (2, '三年二班');
-- 创建学生表
create table mysql8.student
(
id bigint auto_increment,
name varchar(20),
fk_class_id bigint,
primary key (id)
-- foreign key (fk_class_id) references mysql8.clazz (id)
) comment '学生表';
-- 主表的某字段，连接从表的主键（唯一约束字段）
alter table mysql8.student
add constraint fk_class_id foreign key (fk_class_id) references mysql8.clazz (id);
-- 查看 student 表的的外键约束
select table_name, constraint_name, constraint_type, table_schema
from information_schema.table_constraints
where table_name = 'student'
and TABLE_SCHEMA = 'mysql8';
E04. DDL视图操作
心法：视图 view 用于保存一条查询 SQL 语句的结果集，除了本身的 SQL 之外不包含任何数据。
武技：测试 DDL 视图常用操作
-- 查看视图
show tables;
-- 删除视图
drop view if exists v_user;
-- 创建/修改视图 v_user: 封装 user 表的全查语句
create or replace view v_user as
(
select id,username,password,realname,age,gender,weight,created,updated
from mysql8.user
);
-- 查看视图
select * from v_user;
S04. 数据操作语言
心法：数据操作语言 Data Manipulation Language（DML）用于对 DB 表中的数据进行 update，insert，delete 等操作。
E01. DML之增删改操作
1. DML新增记录
-- 单条增加表 `user` 记录
-- 值列表必须和字段列表的个数，顺序，类型一一对应
insert into mysql8.user (username, password, realname, age, gender, weight, created, updated)
values ('zhaosi', 'zhaosi', '赵国强', 58, 1, 150.20, '2023-02-01', '2023-02-02');
-- 批量增加表 `user` 记录
insert into mysql8.user (username, password, realname, age, gender,
weight, created, updated)
values ('liuneng', 'liuneng', '刘能', 59, 1, 152.20, '2023-02-02', '2023-02-03'),
('guangkun', 'guangkun', '谢广坤', 60, 1, 158.20, '2023-04-01', '2023-04-02');
-- 全字段添加时的简写形式
insert into mysql8.user
values (4, 'wangyun', 'wangyun', '王云', 18, 2, 100.20, '2021-02-01', '2021-02-02');
2. DML修改记录
-- 修改 `user` 记录: 必须设置修改内容和修改条件，否则SQL语句报错
update mysql8.user set realname = '赵四', age = 25 where id = 1;
3. DML删除记录
-- 清空 `user` 表记录
truncate table mysql8.user;
-- 删除 `user` 记录: 必须设置删除条件
delete from mysql8.user where id = 3;
E02. DML数据导入导出
1. IDEA导入导出
武技：利用 IDEA 导入/导出表
IDEA 导出表：
- 在 1 或 N 张表上右键并选择
Import/Export - Export Data to Files - 选择 SQL Insert：选择路径，导出 SQL 文件。
- 勾选 Add table definition（DDL），否则只导出了数据，没有表结构。
IDEA 导入表：
- 在指定的数据库目录上右键，选择
SQL Scripts -> Run SQL Script - 找到你要导入的 SQL 文件，点击 OK，即可完成导入。
2. CMD导入导出
武技：利用 CMD 命令行导入/导出表
CMD 导出表：
# 导出 mysql8 数据库中的 clazz 和 student 表
# 参数 `-p` 后面不能加密码，命令末尾不要添加分号
# 在库名前添加 `--default-character-set=utf8mb4` 可以指定编码
# 在库名前添加 `-d`，代表删除数据，只导出表结构
mysqldump -hlocalhost -uroot -p --default-character-set=utf8mb4 mysql8 clazz student > D:\mysql8.sql
CMD 导入表：
# 登录MySQL数据库
mysql -uroot -p
# 切换当前数据库
use mysql8;
# 导入sql文件
source D:\mysql8.sql
S05. 事务机制专题
心法：事务隔离的本质就是解决 “写写冲突” 和 “读写冲突” 的一个手段，而读和读不会发生冲突，毕竟无论怎么读，都不会改变数据的状态，大家一起来读，反而效率更高。
写写冲突：如果一个事务正在修改 A 记录，同时另一个事务也要修改 A 记录，可以么？
读写冲突：如果一个事务正在修改 A 记录，同时另一个事务过来读取 A 记录，可以么？
E01. 事务基础概念
心法：事务是一组不可分割的业务关系，在 SQL 中指的是一组不可拆分的 DML 语句，要么都成功，要么都失败。
事务主要用于处理操作量大，复杂度高的数据，用来维护数据库的完整性。
| 事务特性 | 中文 | 描述 |
|---|---|---|
| Atomicity | 原子性 | 一组事务，要么都成功提交 commit，要么都失败回滚 rollback |
| Consistency | 稳定性 | 一个事务执行前后，数据库都必须处于一致性状态，一般由日志机制实现 |
| Isolation | 隔离性 | 一个事务在执行时，不会受到其它事务的影响，多个并发事务之间相互隔离 |
| Durability | 持久性 | 一个事务一旦提交，它对数据库所做的修改就是永久性的 |
1. 事务基本操作
心法：一次 DML 事务的完整流程包括开始事务，操作数据和结束事务三个步骤。
开始事务：此时临时关闭了自动提交，以下两种 SQL 语句均表示在 MySQL 中开启一个新的事务：
start transaction：支持更多参数，更灵活，命令单词含义更明确，胜在更标准。begin：不支持更多参数，命令单词容易和开始关键字混淆，胜在更简单。
操作数据：
- 根据隔离级别决定是否对数据加锁，以及添加哪种类型的锁。
- 将数据状态记录到 UndoLog 文件中，以便后续回滚操作。
- 在数据库缓冲池 Buffer Pool 中执行DML语句（不直接访问数据库文件）。
- 将本次操作记录到 RedoLog 缓冲池 Redo Log Buffer Pool 中。
- 检查如外键约束、唯一性约束等，若操作违反了任何约束，事务将被回滚。
结束事务：使用 commit 提交事务，或使用 rollback 回滚事务：
- 将数据库缓冲池 Buffer Pool 中的数据刷盘到 idb 文件中。
- 将重做日志缓冲池 Redo Log Buffer Pool 中的数据刷盘到 RedoLog 文件中。
- 若存在锁，则所有由该事务持有的锁都会被释放，其他事务可以访问这些数据。
武技：测试 DML 事务机制
-- 查看当前事务自动提交状态，1表示开启
select @@autocommit;
-- 永久关闭自动提交: 重置为1则可重新打开自动提交
set autocommit = 0;
-- 模拟事务: 添加一条 `user` 记录
-- 开启事务: 此时会临时关闭自动提交
start transaction;
-- SQL-1: 添加一条 `user` 记录，SQL进入缓存，不会真正执行
insert into test.user (username) values ('事务01');
-- SQL-2: 添加一条 `user` 记录，SQL进入缓存，不会真正执行
insert into test.user (username) values ('事务02');
-- 当前事务中的SQL语句全都没有问题，提交缓存中的全部SQL
commit;
-- 永久恢复自动提交
set autocommit = 1;
2. 快照读与当前读
心法：快照读表示读取历史记录，当前读表示读取当前记录。
快照读：客户端手动发送的 select 查询语句：
- 如：
select * from user
当前读：执行下列语句时自动进行的 select 查询语句：
- 如：
insert into .. / update set .. / delete from ..：DML 操作语句前，都会触发一次当前读。 - 如：
select ... for update：添加写锁的查询语句触发的是当前读。 - 如：
select ... lock in share mode：添加读锁的查询语句触发的是当前读。
3. UndoLog日志
心法：UndoLog 回滚日志主要用于确保当事务回滚时，数据能恢复到初始状态。
DML 操作的对应记录：当事务中执行 DML 操作前，会将数据（不是SQL本身）记录在 UndoLog 日志中：
- 插入操作：记录插入的行数据，以便回滚时删除这些行。
- 修改操作：记录修改前的数据，以便回滚时恢复为修改前的数据。
- 删除操作：记录删除的行数据，以便回滚时重新插入这些行。
UndoLog 记录的清理：当事务提交或回滚后，不会立即删除 UndoLog 日志，因为 MVCC 可能还需要使用它读取历史版本数据：
- InnoDB 的后台线程 Purge 专门负责清理不再需要的 UndoLog 和 DB 表中的无效数据。
- 当某个 UndoLog 涉及的所有事务都已结束，且没有其他事务需要其中的数据时，Purge 才清理它。
UndoLog 日志存放位置：
- 默认存放在系统表空间 ibdata1 文件中，该配置会导致表空间文件巨大且难以管理。
- MySQL5.7 支持将 UndoLog 日志存放到独立的 Undo 表空间中，通常命名为 “undo_001, undo_002” 等。
UndoLog 日志格式：UndoLog 以版本链的方式记录了数据的版本历史，每一行数据都对应一个版本链：
- adress：内存地址，记录该条数据版本的内存地址。
- data：版本数据，如新插入的行数据，修改前的原数据或被删除的行数据。
- trx_id：事务编号，即最近一次改动当前这条数据的事务，编号自动递增。
- db_roll_ptr：前置指针，指向版本链中的上一条版本数据的内存地址。
| 位置 | 事务操作 | 内存地址 | 版本数据 | 事务编号 | 前置指针 |
|---|---|---|---|---|---|
| 链表尾 | 插入赵四 | 0x101 | 插入的数据：{id: 99, name: ‘赵四’} | 1 | null |
| 修改赵四为赵五 | 0x102 | 修改前数据：{id: 99, name: ‘赵四’} | 2 | 0x101 | |
| 链表头 | 删除赵五 | 0x103 | 删除的数据：{id: 99, name: ‘赵五’} | 3 | 0x102 |
武技：配置独立的 Undo 表空间
vim /opt/mysql/single/conf/my.cnf
修改内容如下：
[mysqld]
# 设置 Undo 表空间的数量
innodb_undo_tablespaces = 2
# 设置 Undo 表空间的存储目录
innodb_undo_directory = /opt/mysql/single/data/undo
4. RedoLog日志
心法：RedoLog 重做日志主要用于当 DB 崩溃后重启时，通过重放日志，数据能恢复到初始状态。
RedoLog 日志文件是可以循环利用的，当 InnoDB 存储引擎在写满一个 RedoLog 文件后，会自动切换到下一个 RedoLog 文件继续写入，当所有 RedoLog 文件都被写满后，InnoDB 会回到第一个文件并覆盖旧的日志记录，这种方式可以有效地管理日志文件的大小，并确保日志记录不会无限增长。
数据刷盘：当事务中执行 DML 操作后，会将对该数据页进行的操作（不是 SQL 本身）记录在内存的 RedoLog 缓冲池，而 InnoDB 有一个后台线程，可以定期将 RedoLog 缓冲池中的数据刷新到磁盘上，此过程称为刷盘：
- 当 RedoLog 缓冲区达到上限时，InnoDB 自动触发刷盘，以避免缓冲池溢出。
- 当用户执行
flush logs / flush redo_log命令时，表示手动执行刷盘操作。 - 当数据库关闭或重启时，会先强制执行一次刷盘操作。
- 刷盘时会将缓冲区中的数据标记为 “已写入”，它们可以被新数据覆盖，但不会直接被删除。
事务提交：当事务提交时，会先执行一次刷盘，然后再更新内存中的数据页，并最终提交事务。
事务回滚：当事务回滚时，RedoLog 缓冲池中的相关数据会被直接移除，但 RedoLog 文件中的相关数据会被保留，InnoDB 会在适当的时机清理它们。
RedoLog 相关配置项如下：
innodb_flush_log_at_timeout：InnoDB 定期将 RedoLog 缓冲区刷到磁盘上的时间间隔，默认 1 秒。innodb_log_buffer_size：RedoLog 缓冲区上限，默认 16M。innodb_flush_log_at_trx_commit：提交事务时的刷盘行为，默认为 1：- 0 表示每秒将数据刷到 OS 缓冲区，何时刷盘由 OS 决定。
- 1 表示每次事务提交时刷盘。
- 2 表示每次事务提交时，将数据刷到 OS 缓冲区，何时刷盘由OS决定。
innodb_log_file_size：每个 RedoLog 文件的大小，默认为 48 M。innodb_log_files_in_group：RedoLog 文件的数量，默认 2 个。
5. 事务隔离级别
心法：事务隔离级别指的是一个事务对数据库的修改与并行的另一个事务的隔离程度，以理解为当我操作数据库的时候，允许你做什么。
MySQL 支持全部 4 种事务隔离级别，默认是 RR 隔离级别。
未提交读 Read Uncommitted（RU）：允许读取其他事务未提交的数据：
- 加锁：读操作不加锁，写操作添加排他锁（X）。
- 问题：会出现脏读，不可重复读和幻读的问题。
- 其他：效率最高，级别最低，但一般数据库都不用。
已提交读 Read Committed（RC）：只能读取其他事务已提交的数据：
- 加锁：读操作使用 MVCC 机制，写操作添加排他锁（X）。
- 问题：会出现不可重复读，幻读的问题。
- 其他：Oracle 等数据库默认使用这个隔离级别。
可重复读 Repeatable Read（RR）：在同一事务中多次读取同一个数据的结果是一致的：
- 加锁：读操作使用 MVCC 机制，写操作添加临键锁。
- 问题：通过 MVCC 和临键锁机制基本解决了幻读问题，但在一些特殊情况下，如使用了全文索引、空间索引或者一些不使用索引的查询等，可能无法完全避免幻读。
- 其他：MySQL 等数据库默认使用这个隔离级别。
串行读 Serializable Read（SR）：只能等其他事务彻底退出才能接着执行自己的事务：
- 加锁：读操作共享锁（S），写操作添加排他锁（X）。
- 问题：没有任何问题。
- 其他：效率最低，级别最高，但一般数据库都不用。
武技：测试 MySQL 数据库的隔离级别
-- 查询当前会话的隔离级别
select @@transaction_isolation;
-- 设置当前会话的隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
E02. RU未提交读
心法：未提交读 Read Uncommitted（RU）允许读取其他事务未提交的数据，效率最高，但隔离级别最低，一般数据库都不使用。
RU 级别下，对读操作不加锁，对写操作添加排他锁（X）：
- 读无锁：T2 可以读取 T1 未提交的数据，会发生脏读问题。
- 写排他：T2 无法修改 T1 未提交的数据，可避免脏写问题。
总之，RU 避免了脏写问题，但无法避免脏读问题。
1. 脏写问题
心法：脏写指的是 T1 修改了 T2 更新但未提交的数据的问题。
假设原数据为 {id: 1, name: ‘赵四’}：
- T1 想要修改 1 号数据 “赵四 -> 刘能”，成功修改但未提交。
- T2 想要修改 1 号数据 “赵四 -> 广坤”，成功修改并提交。
- T1 提交事务，成功修改 “赵四 -> 刘能”。
- 此时 T2 的修改操作被覆盖，其写操作被称为脏写。
2. 排他锁
心法：排他锁也称为 X 锁，用于阻止其他事务同时读取或修改这些行。
| SQL 操作 | 排他锁情况 |
|---|---|
| insert | 在插入行时，MySQL 会在新插入的行上加排他锁，以防止其他事务同时修改或删除该行 |
| update | 在更新行时，MySQL 会在被更新的行上加排他锁，以防止其他事务同时读取或修改该行 |
| delete | 在删除行时，MySQL 会在被删除的行上加排他锁，以防止其他事务同时读取或修改该行 |
| select | 普通的查询语句不使用任何锁，但可以使用 for update 后缀显示添加排他锁 |
武技：RU 级别下，对写操作添加了排他锁（T1 写时禁止 T2 写），避免了脏写问题。
假设原数据为 {id: 1, name: ‘赵四’}：
- T1 想要修改 1 号数据 “赵四 -> 刘能”，T1 尝试获取 1 号数据的排他锁，获取成功，成功修改但未提交。
- T2 想要修改 1 号数据 “赵四 -> 广坤”，T2 尝试获取 1 号数据的排他锁，获取失败，阻塞等待。
- T1 提交事务，成功修改 “赵四 -> 刘能” 并释放锁。
- T2 获取 1 号数据的排他锁，获取成功并执行它的事务内容。
3. 脏读问题
心法：脏读指的是 T1 读取到了 T2 更新但未提交的数据的问题。
假设原数据为 {id: 1, name: ‘赵四’}：
- T1 想要修改 1 号数据 “赵四 -> 刘能”，T1 尝试获取 1 号数据的排他锁，获取成功，成功修改但未提交。
- T2 想要读取 1 号数据，RU 级别下读不加锁，直接返回到 T1 已修改的数据 “刘能”。
- T1 回滚操作，此时 T2 读取到脏数据。
武技：测试 RU 级别下的脏读现象
- 开启两个不同的会话并分别设置两个会话的隔离级别为 RU 级别：
-- 查询当前会话的隔离级别
select @@transaction_isolation;
-- 设置当前会话的隔离级别
set session transaction isolation level read uncommitted;
- 切换到会话 01：
-- 开启事务
start transaction;
-- 修改但不提交
update test.user set username = '飞机' where user_id = 1;
- 切换到会话 02：
-- 开启事务
start transaction;
-- 此时查询到"飞机"，该数据为T1事务已修改，但未提交的脏数据
select username from test.user where user_id = 1;
-- 提交
commit;
- 切换到会话 01：
-- 回滚
rollback;
- 将隔离级别提升至 RC，重新测试，发现解决了脏读问题。
4. 行锁范围
心法：行锁范围取决于条件字段是否有索引。
假设 DML 语句为 update ... where name = '赵四'：
- 若 name 有索引：则仅对满足条件的行添加行锁。
- 若 name 无索引：则先对表中的全部行添加行锁，然后调用 InnoDB 的 unlock_row 方法解锁不满足条件的行。
E03. RC已提交读
心法：已提交读 Read Committed（RC）只能读取其他事务已提交的数据，Oracle 等数据库默认使用这个隔离级别。
RC 级别下，对读操作使用 MVCC 机制，对写操作添加排他锁：
- 读使用 MVCC 机制：查询到的结果一定是已提交的，可以避免脏读。
- 写排他：T2 无法修改 T1 未提交的数据，可避免脏写问题。
总之，RC 避免了脏读和脏写的问题，但无法避免不可重复读的问题。
1. 重读问题
心法：重读指的是 T1 在同一个事务内的多次读取结果集的内容不一致的问题，主要针对修改和删除操作。
假设原数据为 {id: 1, name: ‘赵四’}：
- T1 想要修改 1 号数据 “赵四 -> 刘能”，T1 尝试获取 1 号数据的排他锁，获取成功，成功修改但未提交。
- T2 想要读取 1 号数据（第1次）：
- RC 读操作使用 MVCC 机制，此时 T1 未提交，忽略。
- 根据 UndoLog 版本链找到已提交的最新数据为 “赵四”，并返回查询结果。
- T1 提交事务，1 号数据修改成功 “赵四 -> 刘能”。
- T2 想要读取 1 号数据（第2次）：
- RC 读操作使用 MVCC 机制，此时 T1 已结束，忽略。
- 根据 UndoLog 版本链找到已提交的最新数据为 “刘能”，并返回查询结果。
- 此时已经发生不可重复读现象。
武技：测试 RC 级别下的重读现象
- 开启两个不同的会话并分别设置两个会话的隔离级别为 RC 级别：
-- 查询当前会话的隔离级别
select @@transaction_isolation;
-- 设置当前会话的隔离级别
set session transaction isolation level read committed;
- 切换到会话 01：
-- 开启事务
start transaction;
-- 第1次查询到的是赵四（此时事务并未结束）
select username from test.user where user_id = 1;
- 切换到会话 02：
-- 开启事务
start transaction;
-- 修改
update test.user set username = '飞机' where user_id = 1;
-- 提交
commit;
- 切换到会话 01：
-- 第2次查询到的是飞机，和本事务中第一次的查询结果不同，发生重读问题
select username from test.user where user_id = 1;
commit;
- 将隔离级别提升至 RR，重新测试，发现解决了重读问题。
2. MVCC版本控制
心法：InnoDB 存储引擎中的 RC 隔离级别与 RR 隔离级别都是基于 MVCC（多版本并发控制）进行事务隔离的，其宗旨就是确保不同事务能够看到一致性的视图。
ReadView：MVCC 的核心就是 ReadView 读视图数据：
- RC 隔离级别下：每执行一次快照读，都会创建一份对应的 ReadView 数据。
- RR 隔离级别下：仅在第一次执行快照读时创建一份对应的 ReadView 数据，后续快照读复用该数据。
- 其他隔离级别下，不会创建 ReadView 数据。
ReadView 组成结构：
- m_ids：当前活跃的事务编号数组，已提交和已结束的事务不在其中。
- min_trx_id：当前最小事务编号。
- max_trx_id：下一个预分配的事务编号，即当前最大事务编号 + 1 的值。
- creator_trx_id：ReadView 创建者的事务编号，即哪个事务创建的 ReadView 数据。
MVCC 原理：MVCC 的原理就是使用 ReadView（RV）和 UndoLog（UL）从头到尾依次匹配，以决定最终的查询结果：
- 从 UL 链表头提取最新 UL 数据，其中 trx_id 值表示执行这条写操作的事务 ID（读操作不记录在 UL 中）。
- 使用该条 UL 数据和一个读事务创建的 ReadView 数据进行比对。
UndoLog 和 ReadView 具体对比流程：
UndoLog.trx_id == ReadView.creator_trx_id：- 成功：读写事务都是自己创建，允许读，返回该条 UndoLog 数据。
- 失败：读写事务不是同一个人创建的，继续下一项比对。
UndoLog.trx_id < ReadView.min_trx_id：- 成功：写事务已结束，允许读，返回该条 UndoLog 数据。
- 失败：写事务未结束，禁止读，继续下一项比对。
UndoLog.trx_id >= ReadView.max_trx_id：- 成功：写事务在读事务之后开启，本轮失败，寻址下条 UndoLog 数据重新比对。
- 失败：写事务在读事务之前开启，继续下一项比对。
UndoLog.trx_id not in ReadView.m_ids：- 成功：写事务已结束或已提交，允许读，返回该条 UndoLog 数据。
- 失败：写事务未结束或未提交，本轮失败，寻址下条 UndoLog 数据重新比对；
3. RC快照读流程
心法：在 RC 级别下，每执行一次快照读，都会创建一份对应的 ReadView 数据。
假设创建 3 个写事务和 1 个读事务，数据库中有一条初始数据为 {id: 99, name: ‘赵0’}：
- 事务 T4 发起第 1 次快照读，生成 ReadView：
creator_trx_id = 4：本次快照读由 T4 事务发起。m_ids = [2, 4]：此时事务 T1 已结束，事务 T3 还未开始，所以存活的只有 T2 和 T4 两个事务。min_trx_id = 2：最小事务为 T2。max_trx_id = 5：下一个事务编号为 4 + 1。
- 事务 T4 比对第 1 条版本数据（地址 0x102，事务 trx_id = 2）和 ReadView 数据：
trx_id(2) == creator_trx_id(4)：比对失败，下一项。trx_id(2) < min_trx_id(2)：比对失败，下一项。trx_id(2) > max_trx_id(5)：比对失败，下一项。trx_id(2) not in m_ids(2, 4)：比对失败，根据指针找到0x101版本数据。
- 事务 T4 比对第 2 条版本数据（地址 0x101，事务 trx_id = 1）和 ReadView 数据：
trx_id(1) == creator_trx_id(4)：比对失败，下一步。trx_id(1) < min_trx_id(2)：比对成功，返回该版本数据 {id: 99, name: ‘赵1’} 作为查询结果。
- 事务 T4 发起第 2 次快照读，生成 ReadView：
creator_trx_id = 4：本次快照读由 T4 事务发起。m_ids = [3, 4]：此时事务 T1 和 T2 均已结束，所以存活的只有 T3 和 T4 两个事务。min_trx_id = 3：最小事务为 T2。max_trx_id = 5：下一个事务编号为 4 + 1。
- 事务 T4 比对第 1 条版本数据（地址 0x103，事务 trx_id = 3）和 ReadView 数据：
trx_id(3) == creator_trx_id(4)：比对失败，下一项。trx_id(3) < min_trx_id(3)：比对失败，下一项。trx_id(3) > max_trx_id(5)：比对失败，下一项。trx_id(3) not in m_ids(3, 4)：比对失败，根据指针找到 0x102 版本数据。
- 事务 T4 比对第 2 条版本数据（地址 0x102，事务 trx_id = 2）和 ReadView 数据：
trx_id(2) == creator_trx_id(4)：比对失败，下一项。trx_id(2) < min_trx_id(3)：比对成功，返回该版本数据 {id: 99, name: ‘赵2’} 作为查询结果。
E04. RR可重复读
心法：可重复读 Repeatable Read（RR）在同一事务中多次读取同一个数据的结果是一致的，MySQL 等数据库默认使用这个隔离级别。
RR 级别下，对读操作使用 MVCC 机制，对写操作添加临键锁：
- 读使用 MVCC 机制：查询到的结果一定是已提交的最新数据，可以避免脏读和不可重复读。
- 写使用临键锁：T2 无法修改 T1 未提交的数据，可避免脏写问题，且 T2 无法在 T1 间隙插入新数据，避免幻读问题。
总之，RR 避免了脏读，脏写和不可重复读的问题，且通过 MVCC 和临键锁机制基本解决了幻读问题，但在一些特殊情况下，如使用了全文索引、空间索引或者一些不使用索引的查询等，可能无法完全避免幻读。
1. RR快照读流程
心法：在 RR 级别下，仅在第一次执行快照读时创建一份对应的 ReadView 数据，后续快照读复用该 ReadView
- 如果事务中是多次连续快照读的操作，则每次快照读都是用的同一份 ReadView 数据：
- ReadView 数据不变，UndoLog 日志版本链数据也不变，所以最终结果也不会变。
- 验证结果：RR 级别下，同一个事务中的多次快照读结果一致，不会产生重复读现场。
- 如果事务中，两次快照读中间存在一次当前读，则第二次的快照读还是会重新产生一份 ReadView 数据：
- 验证结果：RR 级别下，会产生幻读。
2. 幻读问题
心法：幻读指的是 T1 在同一个事务内的两次读取结果集的行数变多的问题，主要针对新增操作。
RR 写操作：T1 对 A 数据添加 X 锁，T2 想操作 A 数据，也要获取 X 锁，会阻塞等待，避免脏写问题。
RR 读操作：使用优化的 MVCC 机制：查询到的结果一定是 “独立”，“最新” 且 “一致” 的：
- 独立：查询结果数据一定没有被其他事务关联，即无法读取其他事务未提交的数据，避免脏读问题。
- 最新：在满足 “独立” 的情况下，数据一定是 UndoLog 版本链中最新的那一个版本。
- 一致：同一个事务中的多次快照读结果会保持一致，避免重读问题。
总之，RR 避免了脏读问题和重读问题，但无法避免幻读问题（在不使用临键锁机制之前），当快照读查询多条记录时，会获取每一条记录的最新版本，若此时其他事务新增了数据，会一并被返回。
3. 间隙锁和临键锁
心法：间隙锁和临键锁主要用来防止幻读，因为它可以防止其他事务在当前事务查询的范围内插入新的记录。
间隙锁 Gap Lock：用于锁定索引范围之间的间隙，以避免其他事务在这个范围内插入新的数据：
- 仅在 RR 隔离级别下使用加锁的查询语句时才会生成间隙锁，间隙锁是自动生成的，无需手动指定。
临键锁 Next-Key Lock：由行锁和间隙锁组合而成：
- 间隙锁的范围一般都是左开右开的，如 (1, 5) 等，范围内整数为 2，3，4。
- 临键锁的范围一般都是左开右闭的，如 (1, 5] 等，范围内整数为 2，3，4，5。
RR 级别下，所有查询都会默认添加临键锁，比如表中4条记录 1, 5, 7, 9，则：
- 间隙包括：(-∞, 1)，(1, 5)，(5, 7)，(7, 11)，(9, +∞)。
- 临键包括：(-∞, 1]，(1, 5]，(5, 7]，(7, 11]，(9, +∞]。
| 隔离级别 | 是否启用间隙锁功能 | 其他 |
|---|---|---|
| RU | 默认不启用 | 不推荐开启 |
| RC | 默认不启用 | 可通过 SET innodb_locks_unsafe_for_binlog = 0 启用间隙锁功能 |
| RR | 默认启用 | 若查询条件未使用索引列，则为全表的间隙加锁，性能低。 若查询条件使用了索引列，则为这些明确的行及其间隙加锁，性能高。 |
| SR | 默认启用 | 锁更复杂 |
武技：测试临键锁范围
create table test.dog
(
id bigint auto_increment comment '主键',
age int not null default 0 comment '狗的年龄',
primary key (id),
index (age)
) comment '狗表';
insert into test.dog (id, age)
values (1, 1),
(5, 5),
(7, 7),
(9, 9);
对唯一索引做等值查询，且记录存在，仅会生成行锁，而对唯一索引做范围查询，会从左到右锁住不满足条件的第一个值（右侧范围闭合）。
-- 1. 添加临键锁 (5, 7]
-- 2. 范围内最后一个值7满足查询条件，临键锁退化为行锁7。
where id = 7;
-- 1. 添加临键锁 (1, 5]
-- 2. 范围内最后一个值5不满足查询条件，临键锁退化为间隙锁 (1, 5)
where id = 3;
-- 1. 条件 id>=5：添加临键锁 (1, 5]，范围内最后一个值5满足查询条件，临键锁退化为行锁5。
-- 2. 条件 id<6：添加临键锁 (5, 7]，由于id是唯一索引，最终结果不会向间隙锁退化。
-- 3. 两个条件整合在一起最终的范围是 [5, 7]
where id >= 5 and id < 6;
-- 1. 条件 age>=5：添加临键锁 (1, 5]，由于age不是唯一索引，不退化。
-- 2. 条件 age<6：添加临键锁 (5, 7]，范围内最后一个值7不满足查询条件，退化为间隙锁 (1, 7)。
-- 3. 两个条件整合在一起最终的范围是 (1, 7)
where age >= 5 and age < 6;
E05. SR序列化读
心法：串行读 Serializable Read（SR）只能等其他事务彻底退出才能接着执行自己的事务，效率最低，级别最高，一般数据库都不用。
SR 级别下，对读操作添加共享锁（S），对写操作添加排他锁（X），完全避免了脏读，脏写，不可重复读，幻读等问题。
1. 共享锁
心法：共享锁也称为 S 锁，允许多个事务同时读取同一数据行，但不允许写入。
insert, update, delete 等 DML 操作若使用共享锁，会导致数据不一致的问题，不推荐。
select 普通的查询语句不使用任何锁，但可以使用 lock in share mode 后缀显示添加共享锁。
Java道经第2卷 - 第3阶 - MySQL（一）
传送门：JB2-3-MySQL（一）
传送门：JB2-3-MySQL（二）
扫一扫
633
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
