MySQL存储过程和函数

事务安全

事务:transaction,一系列要发生的连续的操作.
事务安全:一种保护连续操作同时满足(实现)的一种机制.

事务安全的意义:保证数据操作的完整性.


需求:
有一张银行账户表,有A用户给B用户转账:A账户减少,B账户增加.但是A操作完成之后断电了.

解决方案:A减少钱,但是不要立即修改数据表,B收到钱之后,同时修改数据表.

事务操作

事务操作分为两种:自动事务(默认),手动事务.

手动事务:操作流程

1.开启事务:告诉系统以下所有的操作(写)不要直接写入到数据表,先存放到事务日志中

-- 开启事务
START TRANSACTION;

2.进行一系列事务操作

-- 事务操作:1李四账户减少
update my_account set money = money - 1000 where id = 2;

-- 事务操作:2张三账户增加
update my_account set money = money + 1000 where id = 1;

3.关闭事务:选择性的将日志文件中操作的结果保存到数据表(同步)或者说直接清空事务日志,(原来操作全部清空)

-- 提交事务:同步数据表(操作成功):commit;
-- 回滚事务:直接清空日志表(操作失败):rollback;

事务原理

事务操作原理:事务开启之后,所有的操作都会临时保存到事务日志,事务日志只有在得到commit命令才会同步到数据表,其他任何情况都会清空(rollback,断电,断开连接).

回滚点

回滚点:在某个成功的操作完成之后,后续的操作有可能成功有可能失败,但不管成功还是失败,前面操作都已经成功:可以在当前成功的位置,设置一个点:可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称之为回滚点.

设置回滚点语法:savepoint 回滚点名字;

回到回滚点语法:rollback to 回滚点名字;

-- 回滚点操作
-- 开启事务
start transaction;

-- 事务处理:张三价钱
update my_account set money = money + 10000 where id = 1;

-- 设置回滚点
SAVEPOINT sp1;

-- 银行扣税
update my_account set money = money - 10000 * 0.05 where id = 2; -- 错误

-- 回滚到回滚点
rollback to sp1;

-- 继续操作
update my_account set money = money - 10000 * 0.05 where id = 1;

-- 查看结果
select * from my_account;

-- 提交结果
commit;

事务自动处理

在mysql中:默认都是自动事务处理,用户操作完成会立即同步到数据表中.

自动事务:系统通过autocommit变量控制
show variables like 'autocommit';

关闭自动提交: set autocommit = off/0;

自动关闭之后,需要手动来选择处理: commit提交, rollback回滚

注意: 通常都会使用自动事务

事务特性

事务有四大特性:ACID

A:atomic 原子性,事务的整个操作是个整体,不可分割,要么全部成功,要么全部失败;
C:consistency 一致性:事务操作的前后,数据表中的数据没有变化.
I:isolation 隔离性,事务操作是相互隔离不受影响的.
D:durability 持久性,数据一旦提交,不可改变,永久的改变数据表数据.

锁机制:innodb默认是行锁,但是如果在事务操作的过程中,没有使用到索引,那么系统会自动全表检索数据,自动升级为表锁.

行锁: 只有当前行被锁住, 别的用户不能操作
表锁: 整张表被锁住, 别的用户都不能操作

变量

变量分为两种:系统变量和自定义变量.

系统变量

系统定义好的变量:大部分的时候用户根本不需要使用系统变量:系统变量是用来控制服务器的表现的:如autocommit,auto_increment等

查看系统变量

-- 查看所有系统变量
show variables;

查看具体变量值: 任何一个有数据返回的内容都是由select查看
Select @@变量名;

-- 查看系统变量值
select @@version,@@autocommit,@@auto_increment_offset,@@character_set_results;

修改系统变量

修改系统变量分为两种方式:会话级别和全局级别

会话级别:临时修改,当前客户端当次连接有效.

set 变量名=值/set @@变量名=值;

-- 修改会话级别变量
set autocommit = 0;

-- 修改会话级别变量
set @@autocommit = 0;

全局级别:一次修改,永久生效(对所有客户端都生效)
如果对方(其他)客户端当前已经连上服务器,那么当次修改无效,要退出重新登录才会生效

set global 变量名=值;

-- 修改全局级别变量
set global autocommit = 0;

自定义变量

定义变量

系统为了区分系统变量,规定用户自定义变量必须使用一个@符号，所有自定义级别的变量都是会话级别的:当前客户端当次连接有效，所有自定义变量不区分数据库(用户级别)

set @变量名=值;

-- 自定义变量
set @name='张三';

自定义变量也是类似系统变量查看
Select @变量名;

-- 查看变量
select @name;

在mysql中, “=”会默认的当做比较符号处理(很多地方), mysql为了区分比较和赋值的概念: 重新定义了一个新的的赋值符号:  :=

-- 定义变量
set @age := 18;

mysql允许从数据表中获取数据,然后赋值给变量: 两种方式

1.边赋值,变查看结果
select @变量名=字段名 from 数据源; --从字段中取值赋值给变量名, 如果使用=会变成比较

-- 从表中获取数据赋值给变量
select @name := name,name from my_student;

2.只有赋值不看结果:要求很严格:数据记录最多只允许获取一条:mysql不支持数组
select 字段列表 from 表名 into 变量列表

SELECT name,age FROM my_student WHERE id='1' INTO @name,@age;

SELECT @name;
SELECT @age;

触发器

需求:有两张表,一张订单表,一张商品表,每生成一个订单,意味着商品的库存要减少.

触发器:trigger,事先为某张表绑定好一段代码,当表中的某些内容发生改变的时候(增删改),系统自动触发代码,执行.

触发器(六种):事件类型,触发时间,触发对象
    事件类型:增删改,三种类型. insert,delete和update
    触发时间:前后,before和after 
    触发对象:表中的每一条记录(行)
    
一张表中只能拥有一种触发时间的一种类型的触发器,最多一张表能有6个触发器.

创建触发器

在mysql高级结构中:没有大括号,都是用对应的字符符号代替


触发器基本语法
-- 临时修改语句结束符
delimiter 自定义符号:后续代码只有碰到自定义符号才算结束

create trigger 触发器名字 触发时间 事件类型 on 表名for each row
begin   -- 代表左大括号:开始

end     -- 代表右大括号:结束
-- 语句结束符
自定义符号

--将临时修改修正过来
delimiter ;

-- 触发器:订单生成一个,商品库存减少.
delimiter $$
CREATE TRIGGER after_order AFTER INSERT ON my_order FOR EACH ROW
BEGIN 
	-- 触发器内容开始
	UPDATE my_goods SET inv=inv-1 WHERE id = 2; 
END 
-- 结束触发器
$$
-- 修改临时语句结束符
delimiter ;

查看触发器

查看所有触发器或者模糊匹配
show triggers [like ‘pattern’];

可以查看触发器创建语句
show create trigger 触发器名字;

所有的触发器都会保存一张表中: Information_schema.triggers

使用触发器

触发器:不需要手动调用,而是当某种情况发生时,会自动触发.(订单里面插入内容之后);

-- 插入订单
INSERT INTO my_order VALUES(null,1,1);

SELECT * FROM my_goods;

SELECT * FROM my_order;

修改触发器&删除触发器

触发器不能修改,只能先删除,后新增.

drop trigger 触发器名字;

-- 删除触发器
DROP TRIGGER after_order;

触发器记录

触发器记录:不管触发器是否触发了,只要当某种操作准备执行,系统就会将当前要操作的记录的当前状态和即将执行之后的新的状态给分别保留下来,供触发器使用:其中,要操作的当前状态保存到old中,操作之后的形态保存给new.

old代表的是旧记录,new 代表的是新纪录.

删除的时候是没有new的;插入的时候是没有old的.

old和new都是代表记录本身:任何一条记录除了有数据,还有字段名字.
使用方式:old.字段名/new.字段名(new 代表的是假设发生之后的结果).


-- 临时修改语句结束符
delimiter $$

create trigger after_order after insert on my_order for each row
begin
    -- 触发器内容开始: 新增一条订单: old没有,new代表新的订单记录
    update my_goods set inv = inv - new.g_number where id = new.g_id;
   
end
-- 结束触发器
$$


如果触发器内部只有一条要执行的SQL指令, 可以省略大括号(begin和end)
Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row
一条SQL指令;

触发器: 可以很好的协调表内部的数据处理顺序和关系. 但是从PHP角度出发, 触发器会增加数据库维护的难度, 所以较少使用触发器.

代码执行结构

代码执行结构有三种:顺序结构,分支结构和循环结构.

分支结构

分支结构:实现准备多个代码块,按照条件选择性执行某段代码.

在mysql中只有if分支

基本语法
if 条件判断 then 
 -- 满足条件要执行的代码;
else 
 -- 不满足条件要执行的代码; 
end if;

触发器结合if分支:判断商品库存是否足够,不够不能生产订单

-- 触发器: 订单生成之前要判断库存是否满足

-- 修改语句结束符
delimiter %%

create trigger before_order before insert on my_order for each row
begin
	-- 判断商品库存是否足够

	-- 获取商品库存: 商品库存在表中
	select inv from my_goods where id = new.g_id into @inv;

	-- 比较库存
	if @inv < new.g_number then
		-- 库存不够: 触发器没有提供一个能够阻止事件发生的能力(暴力报错)
		insert into XXX values(XXX);
		
	end if;

end
%%
-- 改回语句结束符
delimiter ;

-- 插入订单
insert into my_order values(null,1,1000);

循环结构

循环结构:某段代码在指定条件执行重复执行.

while循环(没有for循环)

while 条件判断 do
    --满足条件要执行的代码
    --变更循环条件
end while;

循环控制:在循环内部进行循环判断和控制

mysql中没有对应continue和break.但是相关替代.
iterate:迭代,类似continue,后面的代码不执行,循环重新来过
leave:离开类似break,整个循环结束.

使用方式:iterate/leave 循环名字

-- 定义循环名字
循环名字:while 条件 do
 --循环体
 --循环控制
 leave/iterate循环名字;
end while; 

函数

函数:将一段代码块封装到一个结构中,在需要执行代码块的时候,调用结构执行即可(代码复用).

函数分为两类:系统函数和自定义函数

系统函数

系统定义好的函数,直接调用即可.

任何函数都有返回值,因此函数的调用是通过select调用.

mysql中,字符串的基本操作单位(最常见的是字符)

1.substring:字符串截取(字符单位).

-- 定义两个变量
set @cn='世界你好';
set @en='hello world';

-- mysql中字符串的下标从1开始,0代表false,以字符为单位
SELECT SUBSTRING(@cn,1,1);

SELECT SUBSTRING(@en,1,1);

2.char_length:字符长度 length:字节长度.

-- 字符串长度
select char_length(@cn),char_length(@en),length(@cn),length(@en);


3.instr:判断字符串是否在某个具体的字符串中存在,返回字符串的第一个出现位置.

-- 字符串寻找
select instr(@cn,'界'),instr(@en,'ll'),instr(@cn,'拜拜');

4.lpad:左填充,将字符串按照某个指定的填充方式,填充到指定长度(字符)

-- 字符串填充
select lpad(@cn,20,'欢迎'),lpad(@en,20,'hello');

5.insert:替换,找到目标位置,指定长度的字符串,替换成目标字符串

-- 字符串替换
select insert(@en,3,3,'y'),@en;

6.strcmp:compare字符串比较

-- 字符串比较
set @f = 'hello';
set @s = 'hey';
set @t = 'HEY';

select strcmp(@f,@s),strcmp(@s,@t),strcmp(@s,@f);

自定义函数

函数要素:函数名,参数列表(形参和实参),返回值,函数体(作用域)

创建函数

创建语法

create function 函数名([形参列表]) returns 数据类型 --规定要返回的数据类型
begin
    --函数体
    --返回值:return类型(指定数据类型);
end

创建函数

定义函数

-- 创建函数
CREATE FUNCTION display1() RETURNS INT
BEGIN
RETURN 100;
END

自定义函数与系统函数的调用方式是一样: select 函数名([实参列表]);

SELECT display1();

查看函数

查看所有函数: show function status [like ‘pattern’];

SHOW FUNCTION STATUS LIKE 'display1';

查看函数的创建语句: show create function 函数名;

-- 查看函数创建语句
show create function display1;

修改函数&删除函数

函数只能先删除后在新增,不能修改.

-- 删除函数
drop function display1;

函数参数

参数分为两种:定义时的参数叫形参,调用时的参数叫实参(实参可以是数值也可以是变量)

形参:要求必须制定数据类型

function 函数名(形参名字 字段类型) returns 数据类型.

-- 做函数: 计算1-指定数之间的和
delimiter $$
create function display1(int_1 int) returns int
begin
	-- 定义条件变量
	set @i = 1;	-- @定义的变量是全局变量,没有的可以理解为局部变量
	set @res = 0; -- 保存结果

	-- 循环求和
	while @i <= int_1 do
		-- 求和: 任何变量要修改必须使用set关键字
		-- mysql中没有+=,没有++
		set @res = @res + @i;

		-- 修改循环变量
		set @i = @i + 1;
	end while;

	-- 返回值
	return @res;
end
$$	-- 函数结束
delimiter ;

SELECT display1(10);

SELECT @i,@res;

作用域

mysql中的作用域与JS中的作用域完全一样
    全局变量可以在任何地方使用;局部变量只能在函数内部使用.
    
全局变量:使用set关键字定义,使用@符号标志
局部变量:使用declare 关键字声明,没有@符号:所有局部变量的声明,必须在函数体开始之前.

-- 求和: 1-到指定数之间的和,要求5的倍数不加
delimiter $$
create function display2(int_1 int) returns int
begin
	-- 声明变量: 循环变量, 结果变量
	declare i int default 1;
	declare res int default 0; -- 定义局部变量可以有属性

	-- 循环判断
	mywhile:while i <= int_1 do
		-- 相加: 判断
		if i % 5 = 0 then
			-- 修改循环条件
			set i = i + 1;
			-- 不符合条件: 循环重新来过
			iterate mywhile;
		end if;

		-- 相加
		set res = res + i;

		-- 改变循环变量
		set i = i + 1;
	
	end while;

	-- 返回结果
	return res;
end
$$
delimiter ; 



delimiter $$
CREATE FUNCTION display3(int_1 int) RETURNS INT
BEGIN
	-- 循环变量,结果变量
	DECLARE i INT DEFAULT 1;
  DECLARE res INT DEFAULT 0; -- 定义局部变量可以有属性
  my_while:WHILE i<=int_1 DO
  IF i%5 <> 0 THEN
  SET res=i+res;
  SET i=i+1;
  ITERATE my_while;
  END IF;
  SET i=i+1;
  END WHILE;
  RETURN res;
END
$$
delimiter ;

-- 注意
create function display3() returns int
return 'a'; -- 错误: 编译阶段不会出错,执行阶段出错

存储过程

存储过程简称procedure,是一种用来处理数据的方式.
存储过程是一种没有返回值的函数

创建过程

create procedure 过程名字([参数列表])
begin
-- 过程体
end

-- 创建存储过程
CREATE PROCEDURE pro1()
BEGIN
 SELECT * FROM my_student;
END

查看过程

函数的查看方式完全适用于过程:关键字换成procedure

查看所有的过程:show procedure status [like 'parttern']

-- 查看过程
show procedure status like 'pro%';

查看过程创建语句: show create procedure 过程名;

-- 查看过程创建语句
show create procedure pro1;

调用过程

过程没有返回值:select是不能访问的.
过程有一个专门的调用关键字: call

-- 调用过程
call pro1();

修改过程&删除过程

过程只能先删除,后新增.

drop procedure 过程名;

-- 删除过程
drop procedure pro1;

过程参数

函数的参数需要数据类型指定,过程比函数更严格.

过程还有自己的类型限定:三种类型
    in:数据只是从外部传入给内部使用(值传递):可以是数值也可以是变量.
    out:只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部):只能是变量.
    inout:外部可以在内部使用,内部修改也可以给外部使用:典型的引用传递:只能是变量.
    
基本使用
create procedure 过程名 (in 形参名字 数据类型,out 形参名字 数据类型,inout 形参名字 数据类型)
    
调用:out和inout必须是变量,不能是数值.

正确:调用传递变量.

-- 设置变量
set @int_1=1;
set @int_2=2;
set @int_3=3;

SELECT @int_1,@int_2,@int_3;

call pro1(@int_1,@int_2,@int_3);


存储过程对于变量的操作(返回)是滞后的:是在存储过程调用结束的时候,才会重新将内部修改的值赋值给外部传入的全局变量.

-- 过程参数
delimiter $$
create procedure pro2(in int_1 int,out int_2 int,inout int_3 int)
BEGIN
	-- 先查看三个变量
	select int_1,int_2,int_3; -- int_2的值一定是null(三个当前是局部变量)
  
  -- 修改局部变量
  set int_1=10;
  set int_2=100;
  set int_3=1000;

  -- 查看局部变量
  select int_1,int_2,int_3;
  
  -- 查看全局变量
  select @int_1,@int_2,@int_3;

	-- 修改全局变量
  set @int_1='a';
	set @int_2='b';
	set @int_3='c';   
  
  -- 查看全局变量
  select @int_1,@int_2,@int_3;
 
END
$$
delimiter ;

-- 设置变量
set @int_1=1;
set @int_2=2;
set @int_3=3;

SELECT @int_1,@int_2,@int_3;

call pro2(@int_1,@int_2,@int_3);

SELECT @int_1,@int_2,@int_3;

在存储过程调用之后,out类型和inout类型会将过程内部对应的局部变量值返回给对应的传入的全局变量.