视图/存储过程/触发器

视图

1.介绍：
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上
2.语法：
1).创建：CREATE [OR REPLACE] VIEW 视图名称 [(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
2).查询：查看创建视图语句：SHOW CREATE VIEW 视图名称；
               查看视图数据：SELECT * FROM 视图名称
3).修改：

• 方式一：CREATE [OR REPLACE] VIEW 视图名称 [(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
• 方式二：ALTER VIEW 视图名称 [(列名列表)] AS SELECT语句 [ WINTH [ CASCADED | LOCAL ] CHECK OPTION]
  4).删除：
  DROP VIEW [IF EXISTS] 视图名称[,视图名称]

例：

-- 创建视图
CREATE OR REPLACE  VIEW stu_v_l AS SELECT id,name FROM student WHERE id<=10;

-- 查询视图
SHOW CREATE VIEW stu_v_l;
SELECT * FROM stu_v_l;
SELECT * FROM stu_v_l WHERE id<3;

-- 修改视图
CREATE OR REPLACE VIEW stu_v_l AS SELECT id,name no FROM student WHERE id<=10;
ALTER VIEW stu_v_l AS SELECT id,name FROM student WHERE id<=10;

-- 删除视图
DROP VIEW IF EXISTS stu_v_l;

检查选项

当使用 WITH CHECK OPTION 子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如：插入、更新、删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性，为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL,默认值为CASCADED
1).CASCADED
级联
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图
创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
2).LOCAL
本地
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创
建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

视图的更新

要使视图可更新，视图中的行与基础表中的行必须存在一对一的关系，如果视图包含以下任何一项，则该视图不可更新：
A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL
例：

create view stu_v_count as select count(*) from student;
上述的视图中，就只有一个单行单列的数据，如果我们对这个视图进行更新或插入的，将会报错。
insert into stu_v_count values(10);

1659161179619.png

视图作用

1).简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
2).安全
数据库可以授权，但不能授权到数据库特定行和特定的列上，通过视图用户只能查询和修改他们所能见到的数据
3).数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响

案例：
1).为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到用户的基本字段，屏蔽手机号和邮箱两个字段
CREATE VIEW tb_user_view AS SELECT id,name,profession,age,gender,status,createtime FROM tb_user;
SELECT * FROM tb_user_view;

2).查询每个学生所选修的课程（三张表联查），这个功能在很多业务中都有使用到，为了简化操作，定义一个视图
CREATE VIEW tb_stu_course_view AS SELECT s.name student_name, s.no student_no,c.name couser_name FROM student s,student_course sc,course c WHERE s.id = sc.studentid AND sc.courseid=c.id;
SELECT * FROM tb_stu_course_view;

存储过程

介绍：存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据库和应用服务器的传输，对于提高数据处理的效率是有好处的，存储过程思想上很简单，就是数据库SQL语言层面的代码封装于重用

特点：

• 封装、复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可
• 可以接收参数，也可以返回数据：在存储过程中，可以传递参数，也可以接受返回值
• 减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输，而如果封装在存储过程中，我们只需要网络交互一次就可以了

语法：
1).创建 CREATE PROCEDURE 存储过程名称（[参数列表]）
BEGIN --SQL语句
END；
2).调用 CALL名称（[参数]）;
3).查看

• SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA ='XXX'; --查询指定数据库的存储过程及状态信息
• SHOW CREATE PROCEDURE 存储过程名称；-- 查询某个存储过程的定义
  4).删除 DROP PROCEDURE [IF EXISTS] 存储过程名称

注意:在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

示例：

-- 存储过程基本语法
-- 创建
CREATE PROCEDURE pl（）
BEGIN SELECT COUTN(*) FROM student;
end;

-- 调用
call pl（）;

-- 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'itcast'；
SHOW CREATE PROCEDURE pl；

-- 删除
DROP PROCEDURE IF EXISTS pl；

变量

在MySQL中变量分为三种类型：系统变量、用户定义变量、局部变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）
1).查看系统变量

• SHOW [SESSION | GLOBAL] VARIABLES;查看所有系统变量
• SHOW [SESSION | GLOBAL] VARIABLES LIKE '.....'; 可以通过LIKE模糊匹配方式查找变量
• SELECT @@[SESSION | GLOBAL] 系统变量名；查看指定变量的值
  2).设置系统变量
• SET [SESSION | GLOBAL] 系统变量名 = 值；
• SET @@[SESSION | GLOBAL] 系统变量名 = 值；

注意:如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
        mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

示例：

--查看系统变量
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'auto%';
SHOW GLOBAL VARIABLES LIKE 'auto%';

SELECT @@GLOBAL.autocommit;
SELECT @@SESSION.autocommit;

--设置系统变量
SET SESSION autocommit = 1；
INSERT INTO course(id,name) VALUES(6,'ES');

SET GLOBAL autocommit = 0;
SELECT @@GLOBAL.autocommit;

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用‘@变量名’使用就可以，其作用域为当前连接
1).赋值(赋值时，可以使用 = ，也可以使用 := 。)
SET @var_name = expr [，@var_name = expr] ...;
SET @var_name :=expr [, @var_name = expr] ...;
SELECT 字段名 INFO @var_name FROM 表名；
2).使用
SELECT @var_name；

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

示例：

--赋值
SET @myname = ‘itcast’；
SET @myage = ‘10’；
SET @mygender :='男'，@myhobby :='java'；
SELECT @mycolor  :='red';
SELECT COUNT(*) INTO @mycount FROM tb_user;

--使用
SELECT @myname,@muage,@mygender,@myhobby;
SELECT @mycolor,@mycount;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明，可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN...END块
1).声明
DECLARE 变量名 变量类型 [DEFAULT ...];
变量类型就是数据库字段类型：INT / BIGINT / CHAR / VARCHAR / DATE / TIME等
2).赋值
SET 变量名 = 值；
SET 变量名 := 值；
SELECT 字段名 INFO 变量名 FROM 表名；

示例：

-- 声明局部变量 -declare
-- 赋值
CREATE PROCEDURE p2（）
BEGIN
  DECLARE stu_count INT default 0；
  SELECT COUNT(*) INTO stu_count FROM student；
  SELECT stu_count；
end；
CALL p2（）；

if

1).介绍
if用于做条件判断，具体的语法结构为：
IF 条件1 THEN ....
EISEIF 条件2 THEN .... (可选)
ELSE .....(可选)
END IF;
在if条件判断的结构中，ELSE IF结构可以有多个，也可以没有，ELSE结构可以有，也可以没有

2). 案例
根据定义的分数score变量，判定当前分数对应的分数等级。
score >= 85分，等级为优秀。
score >= 60分 且 score < 85分，等级为及格。
score < 60分，等级为不及格。

create procedure p3() 
begin
  declare score int default 58;
  declare result varchar(10); 
  if score >= 85 then set result := '优秀'; 
  elseif score >= 60 then set result := '及格'; 
  elseset result := '不及格';
  end if; 
select result; 
end; call p3();

上述的需求我们虽然已经实现了，但是也存在一些问题，比如：score 分数我们是在存储过程中定义
死的，而且最终计算出来的分数等级，我们也仅仅是最终查询展示出来而已。
那么我们能不能，把score分数动态的传递进来，计算出来的分数等级是否可以作为返回值返回呢？
答案是肯定的，我们可以通过接下来所讲解的 参数 来解决上述的问题

参数

1).介绍
参数的类型，主要分为以下三种：IN / OUT / INOUT，具体的含义如下：

用法：

CREATE PROCEDURE 存储过程名称和（[IN / OUT / INOUT 参数名 参数类型]）
BEGIN
 --SQL语句
END;

2).案例一
根据传入参数score，判定当前分数对应的分数等级，并返回。
score >= 85分，等级为优秀。
score >= 60分 且 score < 85分，等级为及格。
score < 60分，等级为不及格

CREATE PROCEDURE p4(IN score INT,OUT result VARCHAR(10))
BEGIN
  if score >= 85 then
    SET result := '优秀'；
  elseif score >= 60 then
    SET result := '及格'；
  else
    SET result := '不及格'；
  end if；
END；
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明 
call p4(18, @result); 
select @result; 

3).案例二
将传入的200分制的分数，进行换算，换算成百分制

CREATE procedure p5(inout score double)
begin
  SET score := score *0.5;
end;
SET @score = 198;
call p5(@score);
SELECT @score;

case

1).介绍
case结构及作用，和我们在基础篇中坐讲解的流程控制函数很类似，有两种语法格式：
语法1：

--含义：当case_value的值为when_value1时，执行statement_list1,当值为when_value2时，执行statement_list2，否则就执行statement_list
CASE case_value
  WHEN when_value1 THEN statement_list1
  [ WHEN when_value2 THEN statement_list2]
  [ELSE statement_list]
END CASE;  

语法2：
--含义：当条件search_comdition1成立时，执行statement_list1,当条件search_condition2成立时，执行statement_list2,否则就执行statement_list

CASE 
  WHEN search_comdition1 THEN statement_list1
  [ WHEN search_comdition2 THEN statement_list2]
  [ELSE statement_list]
END CASE;  

2).案例
根据传入的月份，判定月份所属的季节（要求采用case结构）。
1-3月份，为第一季度
4-6月份，为第二季度
7-9月份，为第三季度
10-12月份，为第四季度

CREATE procedure p6(IN month INT)
BEGIN
  decalre result VARCHAR(10);
  CASE 
    WHEN month >= 1 AND month <= 3 THEN
      SET result :='第一季度'
    WHEN month >= 4 AND month <= 6 THEN
      SET result :='第一季度'
    WHEN month >= 7 AND month <= 9 THEN
      SET result :='第一季度'
    WHEN month >= 10 AND month <= 12 THEN
      SET result :='第一季度'
    ELSE 
      SET result :='非法参数'；
  END CASE;
  SELECT CONCAT('您输入的月份为：'，month，‘所属的季度为：’，result)；
END;
call p6（16）；

注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。

while

1).介绍
while循环是有条件的循环控制语句。满足条件后，再执行循环体重的SQL语句，具体语法为：

--先判断条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑
END WHILE;

2).案例
计算从1累加到n的值，n为传入的值

--1.定义局部变量，记录累加之后的值
--2.每循环一次，就会对n进行减1，如果n减到0，则退出循环

CREATE procedure p7(IN n INT)
BEGIN
  declare total INT default 0;
  WHILE n>0 do
    SET total := total+n
    SET n := n-1
  END EHILE;
  SELECT total;
END;

call p7(100)

repeat

1).介绍
repeat是有条件的循环控制语句，当满足until声明的条件的时候，则退出循环，具体语法为

--先执行一次逻辑，然后判断until条件是否满足，如果满足则退出，如果不满足，则继续下一次循环
REPEAT
  SQL逻辑
  UNTIL 条件
END REPEAT

2).案例：

计算从1累加到n的值，n为传入的值（使用repeat实现）

CREATE procedure p8(IN n INT)
BEGIN
  declare total int default 0;
  REPEAT
    SET total := total + n;
    SET n := n-1;
  until n <=0
  END REPEAT;
  SELECT total;
END;

call p8（10）;
call p8（100）;

loop

1).介绍
loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环
loop可以配合以下两个语句使用：
leave：配合循环使用。退出循环
iterate：必须用在循环里，作用是跳过当前循环剩下的语句，直接进入下一次循环

[begin_label：] LOOP
  SQL逻辑
END LOOP [ end_label]
LEAVE label; -- 退出指定标记的循环体 
ITERATE label; -- 直接进入下一次循环

上述语法中出现的 begin_label，end_label，label 指的都是我们所自定义的标记
2). 案例一
计算从1累加到n的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
CREATE procedure p9(IN n INT)
BEGIN
  declare total int default 0;
  sum:loop
    if n <=0 then
      leave sum;
    end if;
    SET total := total + n;
    SET n := n-1;
  end loop sum;
  SELECT total;
END;
call p9(100);

3). 案例二
计算从1到n之间的偶数累加的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
create procedure p10(in n int) 
begin
  declare total int default 0;
  sum:loop 
    if n<=0 then 
      leave sum; 
    end if; 
    if n%2 = 1 then 
      set n := n - 1; 
       iterate sum; 
    end if; 
    set total := total + n; 
    set n := n - 1; 
  end loop sum; 
  select total; 
end; 
call p10(100);

游标

1).介绍
游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEM、FETCH、和CLOSE，其语法分别如下：

#声明游标
DECLARE 游标名称 CURSOR FOR 查询语句
#打开游标
OPEN 游标名称
# 获取游标记录
FETCH 游标名称 INFO 变量[,变量]
# 关闭游标
CLOSE 游标名称

2). 案例
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
（name）和专业（profession），并将用户的、姓名和专业插入到所创建的一张新表
(id,name,profession)中

--逻辑
-- A. 声明游标, 存储查询结果集 
-- B. 准备: 创建表结构 
-- C. 开启游标 
-- D. 获取游标中的记录 
-- E. 插入数据到新表中
-- F. 关闭游标
CREATE proceedure p11（IN uage INT）
BEGIN
  declare uname VARCHAR(100);
  declare upro varchar(100);
  declare u_cursor cursor for SELECT name,profession FROM tb_user WHERE age<=uage;
  DROP TABLE if exists tb_user_pro;
  CREATE TABLE if NOT exists tb_user_pro(
    id INT primary key auto_increment,
    name VARCHAR(100),
    profession VARCHAR(100)
  );
  open u_cursor;
  while true do
    fetch u_cursor INTO uname,upro;
    INSERT INTO tb_user_pro VALUES(null,uname,upro);
  END while;
  close u_cursor;
end;
call p11(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有
退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行

1659593921043.png

但是此时，tb_user_pro表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构
中的数据。

上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报
错。 接下来，我们就需要来完成这个存储过程，并且解决这个问题。
要想解决这个问题，就需要通过MySQL中提供的 条件处理程序 Handler 来解决

条件处理程序

1).介绍
条件处理程序（handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

DECLARE headler_axtion HANDLER FOR condition_value [,condition_value] ... statement;
handler_action的值：
  CONTINUE:继续执行当前程序
  EXIT:终止执行当前程序
condition_value的值：
  SQLSTATE sqlstate_value状态码如 02000
  SQLWARNING:所以以01开头的SQLSTATE代码的简写
  NOT FOUND：所有以02开头的SQLSTATE代码的简写
  SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

2). 案例
我们继续来完成在上一小节提出的这个需求，并解决其中的问题。
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
(id,name,profession)中。
A. 通过SQLSTATE指定具体的状态码

-- 逻辑: 
-- A. 声明游标, 存储查询结果集 
-- B. 准备: 创建表结构 
-- C. 开启游标 
-- D. 获取游标中的记录 
-- E. 插入数据到新表中 
-- F. 关闭游标
CREATE procedure p11（IN uage INT）
BEGIN
  declare uname VARCHAR(100);
  declare upro VARCHAR(100)；
  declare u_cursor cursor for SELECT name,profession FROM tb_user WHERE age<=uage;
  -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
  declare exit handler for SQLSTATE '02000' close u_cursor;
  DROP TABLE if exists tb_user_pro;
  CREATE TABLE if not exists tb_user_pro(
    id INT primary key auto_increment,
    name VARCHAR(100),
    profession VARCHAR(100)
  );
  open u_cursor;
  while true do
    fetch u_cursor INTO uname,upro;
    INSERT INTO tb_user_pro VALUES(null,uname,upro);
  end while;
  close u_cursor;
end;
call p11(30);

B. 通过SQLSTATE的代码简写方式 NOT FOUND
02 开头的状态码，代码简写为 NOT FOUND

create procedure p12(in uage int) 
begin
  declare uname varchar(100); 
  declare upro varchar(100); 
  declare u_cursor cursor for select name,profession from tb_user where age <= uage;
  -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出 
  declare exit handler for not found close u_cursor; 
  drop table if exists tb_user_pro; 
  create table if not exists tb_user_pro( 
   id int primary key auto_increment,
   name varchar(100), 
   profession varchar(100) 
  );
  open u_cursor; 
  while true do 
    fetch u_cursor into uname,upro; 
    insert into tb_user_pro values (null, uname, upro); 
  end while; 
  close u_cursor; 
end;
call p12(30);

具体的错误状态码，可以参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

存储函数

1).介绍
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的，具体语法如下：

CREATE FUNCTION 存储函数名称（[参数列表]）
RETURNS type [characteristic...]
BEGIN
  --SQL语句
  RETURN...;
END;
characteristic说明：
  DETERMINISTIC：相同的输入参数总是产生相同的结果
  NO SQL：不包含SQL语句
  READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

2). 案例
计算从1累加到n的值，n为传入的参数值。

CREATE function fun1（n INT）
return INT deterministic
BEGIN
  declare total INT default 0;
  while n>0 do
    SET total := total+n;
    SET n := n-1;
  end while;
  return total;
end;
SELECT fun1(50);

在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定
characteristic特性，否则就会报如下错误：

1659600314313.png

触发器

1).介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前（BEFORE）或之后(AFTER)，触发并执行触发器中定义的SQL语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他数据库是相似的，现在触发器还只支持行级触发，不支持语句级触发

2).语法

#1. 创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EAVH ROW --行级触发器
BEGIN
  trigger_stmt;
END;

#2. 查看
SHOW TRIGGERS;

#3. 删除
DROP TRIGGER [schema_name.] trigger_name; --如果没有指定schema_name，默认为当前数据库

3).案例
通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改，删除
表结构准备:

--准备工作：日志表user_logs
CREATE TABLE user_logs(
  id INT(11) not null auto_increment,
  operation VARCHAR(20) not null comment '操作类型，insert/update/delete',
  operate_time DATETIME not null comment '操作时间',
  operate_id INT(11) not null comment '操作的ID',
  operate_params VARVHAR(500) comment '操作参数',
  primary key('id')
)engine=innodb default charset=utf8;

A. 插入数据触发器

CREATE trigger tb_user_insert_trigger
  after INSERT ON tb_user for each row
BEGIN
  INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)
VALUES
  (null,'insert',now(),new.id,concat('插入的数据内容为:id' , new.id , new.name , ',phone=' , NEW.phone , ',email=' , NEW.email , ',profession=' , NEW.profession));
end;

测试：

--查看
show triggers;

--插入数据到tb_user
INSERT INTO tb_user(id,name,phone,email,profession,age,gender,status,createtime)  VALUES(26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

测试完毕之后，检查日志表中的数据是否可以正常插入，以及插入数据的正确性。

B. 修改数据触发器

CREATE trigger tb_user_update_trigger
  after UPDATE ON tb_user for each row
BEGIN 
  INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)
VALUES
  (null,'update',now(),new.id,concat('更新之前的数据:id=' , old.id , ',name=' , old.name , ',phone=' , old.phone , ',email=' , old.email , ',profession=' , old.profession , '|更新之后的数据:id=' , new.id , ',name=' , new.name , ',phone=' , new.phone , ',email=' , new.email , ',profession=' , new.profession));
end;

测试:

--查看
show triggers;

--更新
UPDATE tb_user SET profession=‘会计’ WHERE id=23;
UPDATE tb_user SET profession='会计' WHERE id<=5;

C. 删除数据触发器

CREATE trigger tb_user_delete_trigger
  after DELETE ON tb_user for each row
BEGIN
  INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)
VALUES
  (null,'delete',new.old.id,concat('删除之前的数据:id=' , old.id , ',name=' , old.name , ',phone=' , old.phone , ',email=' , old.email , ',profession=' , old.profession));
end;

测试:

--查看
show triggers;

--删除数据
DELETE FROM tb_user WHERE id=26;

锁

1.概述

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂
MySQL中的锁，按照锁的粒度分，分为以下三类：

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁住整张表
• 行级锁：每次操作锁住对应的行数据

全局锁

1).介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都会被阻塞
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
2).语法

#1.加全局锁
flush tables with read lock;

#2.数据备份
mysqldump -uroot -p1234 itcast > itcast.sql

#3.释放锁
unlock tables;

3).特点
数据库中加全局锁，是一个比较重的操作，存在以下问题

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本就得停摆

• 如果从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致从主延迟
  在innodb引擎中，我们可以在备份时加上参数 --single-transaction参数来完成不加锁的一致性数据备份

  mysqkdump --single-transaction -uroot -p123456 itcast > itcast.sql
  

表级锁

1).介绍
表级锁，每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低，应用在MyISAM、InnoDB、BDB等存储引擎中
对于表级锁，主要分为以下三类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

1.表锁

对于表锁，分为两类：

• 表共享读锁（read lock）

• 表独占写锁（write lock）
  语法：

  • 加锁：lock tables 表名...read/write
  • 释放锁：unlock tables / 客户端断开连接

  特点
  a.读锁

  
  
  1659686803701.png
  
  

  b.写锁

  
  
  1659686869872.png

结论：读锁不会阻塞其他客户端的读，但是会阻塞写，写锁即会阻塞其他客户端的读，又会阻塞其他客户端的写

2.元数据锁

meta data lock，元数据锁，简写MDL
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候，不可以对元数据进行写入操作，为了避免DML与DDL冲突，保证读写的正确性
这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的
在MySQL5.6中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）
常见的SQL操作时，所添加的元数据锁：

演示：
当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的

1659752340108.png

当执行SELECT语句，时添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的

1659753082427.png

SELECT object_type,object_schema,object_name,lock_type,lock_duration FROM performance_schema.metadata_looks;

我们在操作过程中，可以通过上述的SQL语句，来查看源数据锁的加锁情况

1659753326917.png

3.意向锁

3-1.介绍
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析意向
首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

1659753593915.png

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就
会从第一行数据，检查到最后一行数据，效率较低。

1659753628617.png

1659753664492.png

1659753697137.png

3-2.分类

• 意向共享锁（IS）：由语句select ... lock in share mode添加，与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
  *意向排他锁（IX）：由insert、update、delete、select ...for update添加，与表锁共享锁（read）及排他锁（write）都互斥，意向锁之间不会互斥

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

可以通过以下SQL，查看意向锁及行锁的加锁情况：

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks;

演示：
A. 意向共享锁与表读锁是兼容的

1659754131690.png

B. 意向排他锁与表读锁、写锁都是互斥的

1659754185629.png

行级锁

1).介绍
行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最大，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁。对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete，在RC、RR
  隔离级别下都支持

  
  
  1659754629137.png
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

1659754777952.png

• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

  
  
  1659754915065.png

1.行锁

1-1.介绍
InnoDB实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁

• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
  两种锁的兼容情况如下：

  
  
  1659762596954.png
  
  

  常见的SQL语句，在执行时，所加的行锁如下：

1-2.演示
默认情况下，InnoDB在REPEATABLE事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时就会升级为表锁

可以通过一下SQL，查看意向锁及行锁的加锁情况：

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks;

示例演示
数据准备:

CREATE TABLE `stu` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  `name` varchar(255) DEFAULT NULL, 
  `age` int NOT NULL 
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

INSERT INTO `stu` VALUES (1, 'tom', 1); 
INSERT INTO `stu` VALUES (3, 'cat', 3); 
INSERT INTO `stu` VALUES (8, 'rose', 8); 
INSERT INTO `stu` VALUES (11, 'jetty', 11); 
INSERT INTO `stu` VALUES (19, 'lily', 19); 
INSERT INTO `stu` VALUES (25, 'luci', 25);

演示行锁的时候，我们就通过上面这张表来演示一下。
A. 普通的select语句，执行时，不会加锁

1659763435104.png

B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容

1659763482810.png

1659763699815.png

数据。 而如果客户端二想获取id为1这行的排他锁，会处于阻塞状态，以为共享锁与排他锁之间互
斥。

C. 排它锁与排他锁之间互斥

1659764234207.png

当客户端一，执行update语句，会为id为1的记录加排他锁； 客户端二，如果也执行update语句更
新id为1的数据，也要为id为1的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互
斥的。 直到客户端一，把事务提交了，才会把这一行的行锁释放，此时客户端二，解除阻塞。

D. 无索引行锁升级为表锁
stu表中数据如下:

1659764292592.png

我们在两个客户端中执行如下操作:

1659764408029.png

此时行锁会升级为表锁(因为行锁是对索引项加的锁，而name没有索引)。
接下来，我们再针对name字段建立索引，索引建立之后，再次做一个测试：

1659764461719.png

间隙锁&临键锁

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读

• 索引上的等值查询（唯一索引），给不存在的·记录加锁时，优化为间隙锁
• 索引上的等值查询（非唯一普通索引），向右遍历时最后一个值不满足查询条件时，next-key lock退化为间隙锁
• 索引上的范围查询（唯一索引） --会访问到不满足条件的第一个值为止

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

示例演示
A. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

1659765190640.png

B. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key
lock 退化为间隙锁。
介绍分析一下：
我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。 假如，我们要根据这个二级索引查询值
为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？ 并不是，因为是非唯一索引，这个
结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也
就是29）。此时会对18加临键锁，并对29之前的间隙加锁。

1659765301053.png

1659765441162.png

分：
[19]
(19,25]
(25,+∞]
所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临
键锁(正无穷及之前的间隙)。

InnoDB引擎

1.逻辑存储结构

InnoDB的逻辑存储结构如下图所示:

1660116576458.png

1). 表空间
表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在
8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空
间，用于存储记录、索引等数据。
2). 段
段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段
（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的
非叶子节点。段用来管理多个Extent（区）。
3). 区
区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一
个区中一共有64个连续的页。
4). 页
页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，
InnoDB 存储引擎每次从磁盘申请 4-5 个区。
5). 行
行，InnoDB 存储引擎数据是按行进行存放的。
在行中，默认有两个隐藏字段：

• Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
• Roll_pointer：每次对某条引记录进行改动时，都会把旧版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

2.架构

概述

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛，下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构

1660117369863.png

内存结构

1660117447489.png

在左侧的内存结构中，主要分为这么四大块儿，Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。接下来介绍一下这四个部分
1).Buffer Pool

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中访问，速度相差很大，为了尽可能弥补这两者的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O
在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等
缓冲池Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
缓存池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

• free page：空闲page，未被使用
• clean page：被使用page，数据没有被修改过

• dirty page：脏页，被使用过page，数据被修改过，数据也与磁盘的数据产生了不一致
  在专用服务器，通常将多达80%的物理内存分配给缓冲池。参数设置show variables like ‘innodb_buffer_pool_size’;

  
  
  1660119569268.png

2).Change Buffer

Change Buffer,更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中,Change Buffer的意义是什么呢？
先来看一幅图，这个是二级索引的结构图：

1660119961048.png

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引，同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量磁盘IO，有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

3).Adaptive Hash Index

自适应索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为我们前面讲到过，hash索引在进行等值匹配时，一般性能时要高于b+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。
InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引
自适应哈希索引，无需人工干预，是系统根据情况自动完成
参数：adaptive_hash_index

4).Log Buffer

Log Buffer:日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log）默认大小为16
mb，日志缓冲区的日志会定期刷新到磁盘中，如果需要更新，插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
参数：
innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：
1：日志在每次事务提交时写入并刷新到磁盘，默认值
0：每秒将日志写入并刷新到磁盘一次
2：日志在每次事务提交后写入，并每秒刷新到磁盘一次

1660178599339.png

磁盘结构

接下来，再来看看InnoDB体系结构的右边部分，也就是磁盘结构：

1660178658231.png

1).System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。（在MySQL5.x版本中还包含InnoDB数据字典、undolog等）
参数：innodb_data_file_path

1660179512844.png

2).File-Per-Table Tablespaces

如果开启了innodb_file_per_table开关，则每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中
开关参数：innodb_file_per_table，该参数默认开启

1660179716592.png

那也就是说，我们没创建一个表，都会产生一个表空间文件，如图：

1660179770255.png

3).General Tablespaces
通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，可以指定该表空间
A. 创建表空间
CRAEATE TABLESPACE ts_name ADD DATAFILE ‘file_name’ ENGINE = engine_name;

1660180197503.png

B. 创建表时指定表空间
CREATE TABLE XXX... TABLESPACE ts_name;

1660180284111.png

4).Undo Tablespaces
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志

5).Temporary Tablespaces
InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

6).Doublewrite Buffer Files
双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

1660180897986.png

7).Redo Log
重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用
以循环写入重做日志文件，涉及两个文件：

1660182159164.png

前面我们介绍了InnoDB的内存结构，以及磁盘结构，那么内存中我们所更新的数据，又是如何到磁盘中的呢？此时，就涉及到一组后台线程，接下来，就介绍一些InnoDB中涉及到的后台线程

1660182331894.png

后台线程

1660182413135.png

在InnoDB的后台线程中，分为4类，分别是：Master Thread、IO Thread、Purge Thread、Page Cleaner Thread
1).Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收

2).IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调

我们可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息
show engine innodb status \G;

1660183251648.png

3).Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收

4).Page Cleaner Thread

协助Master Thread 刷新脏页到磁盘的线程，它可以减轻Master Thread 的工作压力，减少阻塞

3.事务原理

事务基础

1).事务
事务时一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

2).特性

• 原子性（Atomicity）：事务时不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性（Consistency）：事务完成时，必须使所有的数据保持一致状态
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
  那实际上，我们研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的四大特性的

  
  
  1660184068811.png

而对于这四大特性，实际上分为两个部分。其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志，而持久化是通过数据库的锁，加上MVCC来保证的

1660184231103.png

我们在讲解事务原理的时候，主要就是来研究一下redolog，undolog以及MVCC。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性
该日志文件由两部分组成：重做日志缓冲（rado log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中，当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用
如果没有redolog，可能会存在什么问题的？ 我们一起来分析一下
我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页，而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性

1660186543918.png

那么，如何解决上述的问题呢？ 在InnoDB中提供了一份日志 redo log，接下来我们再来分析一
下，通过redolog如何解决这个问题

1660186600789.png

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚（保证事务的原子性）和MVCC（多版本并发控制）
undo log和redo log记录物理日志不一样，它是逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对于相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC
undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment 回滚段中，内部包含1024个undo log segment

4.MVCC

基本概念

1).当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁，对于我们日常的操作，如：select ... lock in share mode（共享锁），select ... for update、update、insert、delete（排他锁）都是一种当前读
测试：

1660197108127.png

在测试中我们可以看到，即使在默认的RR隔离级别下，事务A依然可以读取到事务B最新提交的内容，因为在查询语句后面加上了lock in share mode共享锁，此时是当前读操作，当然，当我们加排他锁的时候，也是当前读操作。

2).快照读
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非堵塞读

• Read Committed：每次select，都生成一个快照读
• Repeatable Read：开启事务后第一个select语句才是快照读的地方
• Serializable:快照读会退化为当前读

测试:

1660197500860.png

在测试中，我们看到即使事务B提交了数据，事务A中也查询不到，原因就是因为普通的select是快照读，而在当前默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读

3).MVCC

全称Multi-Version Concurrency Control，多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能，MVCC的具体实现，还需要依赖于数据库记录的三个隐式字段、undo log日志、readView
接下来，我们再来介绍一下InnoDB引擎的表中涉及到的隐藏字段 、undolog 以及 readview，从
而来介绍一下MVCC的原理。

隐藏字段

介绍

1660199111352.png

当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了
这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

而上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，
如果有主键，则不会添加该隐藏字段。
测试
1). 查看有主键的表 stu
进入服务器中的 /var/lib/mysql/itcast/ , 查看stu的表结构信息, 通过如下指令:

idb2sdi stu.ibd

查看到的表结构信息中，有一栏 columns，在其中我们会看到处理我们建表时指定的字段以外，还有
额外的两个字段 分别是：DB_TRX_ID 、 DB_ROLL_PTR ，因为该表有主键，所以没有DB_ROW_ID
隐藏字段

1660199771762.png

1660199790860.png

2). 查看没有主键的表 employee

建表语句
CREATE TABLE employee（id INT,name VARCHAR（10））;

此时，我们再通过以下指令来查看表结构及其其中的字段信息：

ibd2sdi employee.ibd

查看到的表结构信息中，有一栏 columns，在其中我们会看到处理我们建表时指定的字段以外，还有
额外的三个字段 分别是：DB_TRX_ID 、 DB_ROLL_PTR 、DB_ROW_ID，因为employee表是没有
指定主键的。

1660199973041.png

1660200025351.png

1660200051013.png

undolog

介绍
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只要回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

版本链

有一张表原始数据为：

1660200798832.png

DB_REX_ID:代表最近修改事务id，记录插入这条或最后一次修改该记录的事务id，是自增的
DB_ROLL_PTR:由于这条数据是插入的，没有被更新过，所以该字段值为null

然后，有四个并发事务同时在访问这张表。
A. 第一步

1660201012147.png

当事务2执行第一条修改语句时，会记录undo log日志，记录数据变更之前的样子; 然后更新记录，
并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

1660201147093.png

1660201228858.png

录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

1660201271863.png

1660201676140.png

1660201731856.png

最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条
记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
ReadView中包含了四个核心字段：

而在readview中就规定了版本链数据的访问规则：
trx_id 代表当前undolog版本链对应事务ID。

不同的隔离级别，生成ReadView的时机不同：

• READ COMMITTED：在事务中每一次执行快照读时生成readview
• REPEATABLE READ:仅在事务中第一次执行快照读时生成readview，后续复用该readview

5.原理分析

RC隔离级别：RC隔离级别下，在事务中每一次执行快照读时生成readview
我们就来分析事务5中，两次快照读读取数据，是如何获取数据的?
在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读
都会生成一个ReadView，那么两次生成的ReadView如下。

1660203882670.png

那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，
到undolog版本链中匹配数据，最终决定此次快照读返回的数据。

A. 先来看第一次快照读具体的读取过程：

1660203940926.png

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：
先匹配

1660204463847.png

都不满足，则继续匹配undo log版本链的下一条。
再匹配第二条

1660204499214.png

1660204539499.png

，这条记
录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照
读，返回的数据就是版本链中记录的这条数据。

RR隔离级别
RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可
重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。
那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了

1660204586205.png

我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该
ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返
回的结果也是一样的。
所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。
而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证

1660204630694.png