MySQL基础（五）

简介

• 本篇主要介绍MySQL的事务机制
• 也会对变量、触发器及存储过程粗浅解释

事务

• 事务：一系列要发生的对数据的连续操作（注意是对数据，别想把表删了再回滚！）
• 事务安全：保证操作连续的机制，可以保护数据的完整和一致性

举个例子：A给B转账，如果中途断电了？或者有其他操作修改了A的账目？操作被打断，中间的过程如果不可控，双方造成损失

-- 那就创建个账户表
create table my_account (id int primary key auto_increment,
                        account char(16) not null comment '银行卡号',
                        name varchar(20) not null comment '用户名',
                        money decimal(10,2) default 0.0 comment '钱 ')charset utf8 engine=innodb;
-- 如果你的数据库版本<=5.5，记得配置或者像这样指定存储引擎！
-- 基础篇部分用的MySQL版本是5.5，现在到了8.0，略有不同！
-- MyISAM不支持事务

• 插入数据

insert into my_account values(default, '0000000000000001', 'Roy', 1234567);
insert into my_account values(default, '0000000000000002', 'Allen', 123459);
insert into my_account values(default, '0000000000000003', 'Tina', 15569);

事务操作

• 开启事务

  • 告诉系统以下所有操作（写）不要立即写入到数据表，先存放到事务日志
  • 指令：start transaction或begin

  -- 更新Roy的钱数
  start transaction
  update my_account set money =  money-1000 where id=1;
  -- 同时开两个client查看
  

  

  • 在事务期间，其他客户端的查询操作还是原始表数据

• 提交

  • 提交：将事务日志中的操作同步到数据表
  • 指令：commit

  

• 回滚：清空事务日志，相当于操作执行失败

  • 指令：rollback
  • 提交事务之后日志也会清空，不能回滚咯！
  • 所以说，想好了再提交！

• 可以看出，连接中断、commit、rollback都会将事务日志清空

• 只有commit会对数据表产生影响

回滚点

• 一次事务，某个操作成功完成后，后续的操作可能成功也可能失败，但如果失败，可以不回滚到原点，而是退回到已经成功的位置后！

• 很合理对吧？这个成功的位置就叫回滚点

  -- Roy加工资
  update my_account set money = money + 10000 where id = 1;
  savepoint act1;		-- 添加回滚点
  -- Allen扣工资
  update my_account set money = money - 1000 where id = 1;	-- 误操作
  -- 退回到回滚点
  rollback to act1;
  -- 重新扣钱！
  update my_account set money = money - 1000 where id = 2;
  -- 查看结果
  select * from my_account;
  -- 如果成功，提交结果
  commit;
  

  

自动提交事务

• MySQL中默认开启提交事务，查看：show variables like 'autocommit';
  
• 关闭自动提交事务：set autocommit = off;
• 通常是开启的！

事务特性(ACID)

• 原子性
  • Atomic：事务的操作是一个整体，全部成功或者全部失败
• 一致性
  • Consistency：事务必须使数据库从一个一致性状态变换到另一个一致性状态
  • 拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性！
  • 说白了，就是事务自身要搞一致，别出岔子；
• 隔离性
  • Isolation：当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
  • 也即是说，当用户开启事务，就会将表拿过来一份操作，其他事务的操作以及提交之后，都不会更新到这里！
• 持久性
  • Durability：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

并发问题

• 以下是由于并发引起的常见问题：
  • 脏读：事务A读取了事务B更新的数据，然后B回滚了woc，那么A读取到的数据是脏数据
  • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
  • 幻读：在一个事务的两次查询中数据记录不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的（就像外星人来了，幻觉吧！）
• 其中：不可重复读的和幻读很容易混淆
  • 不可重复读侧重于修改，解决不可重复读的问题只需锁住满足条件的行
  • 幻读侧重于新增或删除，解决幻读需要锁表
• 抓关键解决问题，概念都是别人定义的

隔离级别

• 为了解决这些并发问题，实现事务的四大特性，数据库定义了4种不同的事务隔离级别

• 隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key锁

  • 如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，因此可以防止幻读

• 串行化，最高隔离级别，所有事务依次执行，读写数据都会锁住整张表，不可能互相干扰（否：就是不会出现）

  事务隔离级别	名称	脏读	不可重复读	幻读
  read-uncommitted	读未提交，也叫脏读	是	是	是
  read-committed	不可重复读，也叫读已提交	否	是	是
  repeatable-read	可重复读，默认级别	否	否	否
  serializable	串行化	否	否	否

• 查看隔离级别：show variables like 'transaction_isolation';
  

乐观/悲观锁

• 悲观锁：数据库中最严格的并发控制策略，在查询完数据的时候就把事务锁起来，直到提交事务释放了这个锁
  • 出发点是设想最坏的情况，所有不同事务的处理一定会出现干扰
• 乐观锁：在大部分操作里不许加锁，在修改数据的时候把事务锁起来，通过version的方式来进行锁定，在提交操作时检查是否违反数据完整性
  • 出发点是设想不同事务的处理不一定会出现干扰
• 乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量
• 乐观或悲观锁更强调的是锁的时机，和接下来锁的机制并不冲突

锁机制

• 学完了事务，详细了解下MySQL的锁机制，这个锁并不是只针对事务完整性的，而是应用在任何操作数据的场景中

• 在关系型数据库中，可以按照锁的粒度把数据库锁分为

  • 行级锁（INNODB引擎）
  • 表级锁（MYISAM引擎）
  • 页级锁（BDB引擎）

• InnoDB默认行锁（row-level locking）

  • 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁
  • 行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大
  • 行级锁分为共享锁 和 排他锁

• 共享锁: 又叫做读锁

  • 当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个
  • 读锁的主要目的是为了防止在读期间写入

• 排他锁: 又叫做写锁

  • 当用户要进行数据的写入时，对数据加上排他锁
  • 排他锁只可以加一个，他和其他的排他锁，共享锁都相斥
  • 读写锁类似于看房和买房，一起看，但只能一个人买

• InnoDB是基于索引来完成行锁，在使用主键索引的情况下，例如：

  start transaction;
  -- 索引机制后续介绍（我还不会）
  update my_account set money = money + 1000 where id = 1;
  

• 也可以通过如下方式实现行锁

  select * from tab_with_index where id = 1 for update;
  

• 如果没有使用主键索引，则自动开始全表索引，即会形成表锁，例如

  update my_account set money = money + 1000 where name = 'Roy';
  -- 然后再开始另一个事务，会发现其在等待
  -- 说白了，就不能并发了！
  -- 当然，我们可以给name字段加index
  

  

• 所以说，一定要有索引，并使用索引加锁

  表已锁，相当于到了serializable隔离级别，只能等待第一个事务解锁：Lock wait timeout exceeded; try restarting transaction

• 又是隔离级别又是锁的（人家心好乱），什么关系呢？他们是有对应关系的

  • 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
  • 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
  • 在Repeatable Read（可重复读）级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
  • SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

变量

• 变量，即在MySQL执行过程中，系统或用户定义的可以设置的参数
• 分为系统变量和用户自定义变量

系统变量

• 系统定义好的控制服务器的变量，一般不会修改

  -- 查看变量名
  show variables; -- 查看所有，大概300个
  -- 查看version这个变量值
  select @@var_name;-- 必须使用 @@
  -- 也可具体查看：
  -- 查看会话变量
  select @@session.var_name;
  show session variables like "%var%";
  -- 查看全局变量
  select @@global.var_name;
  show global variables like "%var%";
  

• 修改系统变量分为会话级别和全局修改

  -- 会话级别修改
  set autocommit = 0;
  set @@autocommit = 0;
  -- 全局级别
  set global autocommit = 0;	-- 一经修改，永久有效
  

  • 即系统变量又分为会话变量和全局变量
  • 啥叫会话（session）？就是你当前连接的这个窗口
  • 啥叫全局（global）？就是你一修改在所有窗口都有效（需要super权限）
  • 蛋，全局修改不能跨重启，即重启后所有设置的全局变量均失效（恢复默认值）
    • 注意：是MySQL重启，不是重连
  • 要想让全局变量重启后继续生效，需要更改相应的配置文件
    • 在8.0版本中，新加了个persist，可直接将设置持久化在文件

自定义变量

• 为了与系统变量区分，必须使用一个@符号

  set @name = 'Roy';
  -- 查看
  select @name;
  

• 在mysql中=默认用来比较而不是赋值，所以也可以使用:=赋值，以示区分

  set @name := 'Allen';-- 由于使用set，系统知道是赋值，可以使用 = 
  

• MySQL也允许从表中获取数据复制 给变量

  -- 方案一：
  select @height := height from my_stu where id =1;-- 如果不使用where条件查询到多条，则会将最后一条赋值给变量
  -- 方案二：
  select height sex from my_stu where id = 1 into @height, @sex;-- 只能查到一条进行赋值，不然报错：Result consisted of more than one row
  select @height, @sex;
  

• 所有自定义的变量都是会话级别（在内存中），作用域与生命周期均限于当前客户端连接，但不区分数据库

• 上面是会话变量，还有局部变量

  drop procedure if exists add;
  create procedure add
  (
      in a int,
      in b int
  )
  begin
      declare c int default 0;
      set c = a + b;
      select c as c;
  end;
  

• 一般用于SQL的语句块中，后面的SQL编程中会说到

触发器

• 需求：订单表和库存表，每生成一个订单，库存对应减少

  • 一般我们会放到后端逻辑中实现此功能，但也可用触发器实现

• 触发器（trigger）：事先为某张表绑定一段代码，当表中的内容发生改变时，执行代码！

  -- 库存表
  create table my_goods(id int primary key auto_increment,
                       name varchar(20) not null,
                       price decimal default 1,
                       inv int comment '库存数量')charset utf8 engine=innodb;
  insert into my_goods values(1, 'p30', 6666, 30), (2, 'iPhone', 8888, 50);
  -- 订单表
  create table my_order(id int primary key auto_increment,
                        g_id int comment '商品id',
                        g_num int comment '商品数量')charset utf8 engine=innodb;
  

触发三要素

• 触发事件类型：增删改

• 触发时间：before/after

• 触发对象：表中的每一行记录

  一张表中只能有一种触发时间的一种触发类型的触发器，即一张表中最多有2x3=6个！

创建触发器

• MySQL的高级结构中没有大括号，使用符号代替

  -- 临时修改语句结束符，否则触发内容给会被分号中断
  delimiter $$
  -- after_order是触发器名；after insert组合，表示在插入之后触发，需要使用关键字on
  -- 这行别使用分号分割，以下是一整条指令!!!
  create trigger after_order after insert on my_order for each row
  -- begin/end代表{}
  begin
  	-- 触发以下代码执行：
  	update my_goods set inv = inv - 1 where id = 1;	-- 这里先指定id；分号起到的作用是分隔指令，不再代表输入结束
  end$$	-- 触发器创建结束
  delimiter ;	-- 修改回来
  

查看触发器

• 可以查看所有触发器或者其创建语句

  show triggers\G -- 查看所有
  show create trigger after_order\G -- 查看创建语句
  

• 通过系统变量查看

  select * from information_schema.triggers\G;
  

  

使用触发器

• 创建的触发器限定了id=1，即不是动态的

  insert into my_order values(default, 1, 'iPhone');
  

  

删除触发器

• 触发器不能直接修改，只能删除再创建

  drop trigger after_order;
  

触发器记录

• 无论是否触发，只要当相关操作准备执行，系统就会将当前要操作记录的当前状态和执行后的新状态分别保留下来，供触发器使用

• 当前状态保存在old中，之后的状态保存在new中（看到上面的红框框了吗？）

  类似事务，还没更新到数据表，但是可以查看操作完成后的状态

  删除的时候没用new，插入的时候没有old

• 重新创建我们的触发器，修改id问题

  delimiter $$
  create trigger after_order after insert on my_order for each row;
  begin
  	-- 插入没有old，看清楚下面的调用方式
  	-- 插入后的订单记录保存在new中
  	update my_goods set inv = inv - new.g_num where id = new.g_id
  end
  $$
  delimiter ;
  

  如果触发器中只有一条命令，可以省略大括号（begin/end）

  -- 测试一下吧！
  insert into my_order values(default, 2, 5);
  

SQL编程

• 所有的编程代码都在写三个东西：ifelse、loop、recursion
• 语句结构被分为顺序结构、分支结构、循环结构
• 剩下的就是语法问题了，熟能生巧

分支结构

-- if语句
-- 判断库存够不够
delimiter $$
create trigger after_order after insert on my_order for each row;
begin
	select inv from my_goods where id = new.g_id into @inv;	-- 使用变量
	if @inv < new.g_num then -- 库存不够
		insert into XXX values(XXX);-- 暴力报错
end
$$
delimiter ;
-- 也会阻止订单的产生

循环结构

-- while循环，mysql没有for循环
-- 举例：累加函数
delimiter //
create function accumulation(a int) returns int
begin
	set @i = 1;		-- set定义的@标志的变量是全局的
	set @res = 0; 	-- 返回值
	while @i <= a do	-- 循环
		set @res = @res + @i;	-- mysql 没有 += ++
		set @i = @i + 1;		-- 修改变量必须使用set
	end while;		-- 结束循环
	return @res;
end
//
delimiter ;

函数

• 将一段代码封装在一个结构中，可以调用
• 任何函数都有返回值，因此都是通过select调用
• 包括系统函数和自定义函数

系统函数

• 字符串函数

  -- substring()  字符串截取
  set @cn = '杨瑞';
  set @en = 'Roy';
  select substring(@cn, 1, 1);  -- 目标字符串 起始位置（从1开始） 长度（以字符为单位）
  select substring(@en, 1, 1);
  -- 可以在定义变量前设置字符集，set names gbk;	
  -- 一般均以字符为最小单位分割，英文字符站1字节，中文字符占3字节而已
  
  -- char_length() 字符长度
  -- length() 字节长度
  select char_length(@cn), char_length(@en), length(@cn), length(@en);
  
  -- instr() 判断字符串在某个字符串的具体位置
  select instr(@cn, '瑞'), instr(@en, 'o'), instr(@cn, 'fuc');-- 如果没有返回 0
  
  -- lpad() 左填充，使用给出的字符填满到定义的长度
  select lpad(@cn, 10, '谁最帅？'),lpad(@en, 10, 'Great');
  
  -- insert() 替换
  select insert(@en, 1, 3, 'f');-- 从1到3 的位置换为一个f
  
  -- strcmp() 比较，大于返回1，小于返回-1， 相等返回0
  set @a = 'abc';
  set @b = 'abd';
  set @c = 'ABD';
  select strcmp(@a, @b), strcmp(@b, @a), strcmp(@b, @c);
  

  

自定义函数

• 定义：所有的块定义都是用begin和end包起来

  -- 比较特殊，先指定返回值，使用returns
  delimiter //
  create function say() returns int
  begin	-- 可省略
  	return 100;
  end
  //
  delimiter ;
  
  create function test(a int) returns int
  return a;
  

  全局级别，除非手动删除

• 查看函数

  show function status\G	-- 可以使用模糊匹配 like %_
  show create function test\G
  

• 删除函数

  -- 函数不能直接修改，只能先删除后新增
  drop function test;
  

• 作用域

  -- MySQL中的作用域赫尔js完全相同
  -- 全局变量：使用set定义，@标志
  -- 局部变量：使用declare定义
  -- 举例：定义累加器，不加6的倍数
  delimiter //
  create function accumulation1(b int) returns int
  begin
  	declare i int default 1;	-- 允许有属性
  	declare res int default 0;
  	while_case: while i <= b do
  		if i%6 = 0 then 	-- 这里的等号是比较
  			set i = i + 1;	-- 所有变量修改都是用set
  			iterate while_case; -- 迭代，作用是结束此次循环，相当于continue
  		end if;
  		set res = res + i;
  		set i = i + 1;
  	end while;
  	return res;
  end
  //
  delimiter ;
  

存储过程

• 可以理解为没有返回值的函数，使用procedure关键字

• 创建

  create procedure pro()
  select * from my_class;	-- 省略大括号
  

• 查看：类似函数，替换关键字即可

  show procedure status\G
  show create procedure pro\G
  

• 调用： 没有返回值了，select不能用，有个专门的关键字call

  call pro();
  

• 删除

  drop procedure pro;	-- 同样的，不能直接修改
  

• 参数：函数的参数需要指定类型，过程的参数还需要限定，限定分为三种

  • in：供内部使用，只是简单的将外部值复制过来，和常见的传参相同；可以是变量或数值
  • out：变量传入后先被清空，再被内部使用，外部可以使用内部修改的数据；只能是变量
  • inout：外部可以使用内部修改的数据，内部也可以使用外部传入的数据，典型引用传递

  out和inout都是引用传递，内部修改会影响外部

  delimiter //
  create procedure pro1(in a int, out b int, inout c int)
  begin
  	select a, b, c;	-- 局部变量
  end
  //
  delimiter ;
  

  set @a = 1;
  set @b = 2;
  set @c = 3;
  select @a, @b, @c;
  call pro1(@a, @b, @c);
  select @a, @b, @c;	-- 观察结果
  

  

• 在过程执行完后，out和inout限定的局部变量会把值更新到对应的全局变量

  delimiter //
  create procedure pro2(in a int, out b int, inout c int)
  begin
  	set a = 4;
  	set b = 5;
  	set c = 6;
  	select a, b, c; -- 修改局部变量
  	
  	select @a, @b, @c;
  	set @a = 10;
  	set @b = 100;
  	set @c = 1000;	-- 修改全局变量
  	select @a, @b, @c;
  end
  //
  delimiter ;
  

  

  存储过程因为会将SQL语句预编绎，运行的速度比较快
  因为类似于函数，所以也具有函数的部分性质

小结

• 本篇对MySQL的事务、触发器、SQL编程做了较为详细的总结
• 很多知识点可能用不到，但系统的了解可能会为以后解决问题提供方案
• 更贴合实际应用的部分可以看我的MySQL面试和面试指南