mysql事务变量_mysql学习四之事务、变量、触发器、函数、存储过程

需求：有一张银行账户表，有A用户给B用户转账；A账户先减少，B账户增加，但是A操作完之后断电了。

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

事务安全

事务：transaction,一系列要发生的连续的操作

事务安全：一种保护连续操作同时满足(实现)的一种机制

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

--创建一个账户表

create table my_account(

number char(16) not null unique comment '账户',

name varchar(20) not null,

money decimal(10) default 0.0 comment '账户余额'

)charset utf8;

insert into my_account values('0000000000000001','张三',1000),('0000000000000002','李四',1000);

alter table my_account add id int primary key auto_increment first;

update my_account set money = money - 1000 where id= 1;

事务操作

事务操作分为两种：自动事务(默认的)，手动事务

手动事务：操作流程

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

--开启事务

start transaction;

2、进行事务操作：一系列操作

a)李四账户减少

update my_account set money = money - 1000 where id = 2;

b)张三账户增加

update my_account set money = money + 1000 where id = 1;

3、关闭事务：选择性的将日志文件中操作的结果保存到数据表(同步)或者说

直接清空事务日志(原来操作全部清空)

a)提交事务：同步数据表(操作成功)commit;

b)回滚事务；直接清空(操作失败) rollback;

--提交事务

commit;

事务原理

开启事务后，后续的所用操作(写)；会先写到临时日志文件，

接收SQL语句，执行SQL语句；结果先写入到临时日志文件；

查询操作：会从数据表查询，经过临时日志文件结果加工后返回

事务结束：commint或rollback,清空临时日志文件，commit会同步到数据表，

rollback是直接清空

如果断开连接，临时事务文件会自动清空。

回滚点

在某个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管

成功还是失败，前面操作都已经成功；可以在当前成功的位置，设置一个点；

可以供后续失败操作返回该位置，而不是返回所有操作，这个点称为回滚点。

设置回滚点语法：savepoint 回滚点名字

回到回滚点语法：rollback to 回滚点名字

--回滚点操作

--开启事务

start transaction;

--事务处理1:张三加钱

update my_account set money=money+10000 where id = 1;

--查看结果

select * from my_account;

--设置回滚点

savepoint sp1;

--银行扣税

update my_account set money=money-10000*0.05 where id=2; --错误

--查看结果

select * from my_account;

--回滚到回滚点

rollback to sp1;

--查看结果

select * from my_account;

--继续操作

update my_account set money=money-10000*0.05 where id=1;

--查看结果

select * from my_account;

--提交事务

commit;

事务特性：四大特性

A：原子性，事务整个操作是一个整体，不可分割，要么全部成功，要么全部失败

C：一致性，事务操作的前后，数据表的数据没有变化

I：隔离性，事务操作是相到隔离不受影响的

D：持久性，数据一旦提交，不可改变，永久的改变数据表数据

锁机制：innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引，

那么系统会自动全表检索数据，自动升级为表锁。

行锁：只有当前行被锁住，别的用户不能操作

表锁：整张表被锁住，别的用户都不能操作。

变量

变量分为系统变量和自定义变量

系统变量：系统自定义好的变量，系统变量是用来控制服务器的表现的，如

autocommit,auto_increment_increment等

查看系统变量

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

查看具体变量值：任何一个有数据返回的内容都是由select查看

select @@变量名;

--查看变量值

select @@version,@@autocommit,@@auto_increment_offset;

修改系统变量

分为两种方式:会话级别和全局级别和全局级别

会话级别：临时修改

--修改会话级别变量 ，当前客户端当次连接有效

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

set autocommit = 0 ;

全局级别：一次修改，永久生效(对所有客户端生效)

set global 变量名 = 值;

自定义变量

系统为了区分系统变量，规定用户自定义变量必须使用一个@符号

set @变量名=值;

set @name = '张三';

select @name;

mysql定义的一个赋值符号:=

set @age := 19;

select @age;

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

1、边赋值，边查看结果(只能用:=，=号会解析为比较)

select @变量名 := 字段名 from 数据源; --从字段中取值赋值给变量名

--从表中获取数据赋值给变量

select @name := name from my_student;

2、只有赋值不看结果，要求很严格：数据记录只能有一条记录

select 字段表 from 数据源 into @变量名

select name,age from my_student where id=2 into @name,@age;

所有自定义的变量都是会话级别：当前客户端当次连接有效

所有自定义变量不区分数据库(用户级别)

需求：有两张，一张订单表，一张商品表，每生成一个订单，意味着商品的库存要

减少

触发器

事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候(增删改)系统

会自动触发代码执行。

事件类型，触发时间，触发对象

事件类型：增删改，三种类型 insert delete update

触发时间：前后，两种 before after

触发对象：表中的每一条记录(行)

一张表中只能拥有一种触发时间的一种类型的触发器；最多一张表能有6个触发器

创建触发器

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

基本语法

--临时修改语句结束符

delimiter 自定义符号：后续代码中只有碰到自定义符号才结束

create trigger 触发器名字 触发时间 事件类型 on 表名 for each row

begin --代表左大括号；开始

...里面就是触发器的内容：每行内容都必须使用语句结束符，分号

end --代表右带括号：结束

--语句结束符号

自定义符号

--将临时修改修正过来

delimiter

--创建表

create table my_goods(

id int primary key auto_increment,

name varchar(20) not null,

price decimal(10,2) default 1,

inv int comment '库存'

)charset utf8;

insert into my_goods values(null,'iphone6s',5288,100),(null,'s6',5300,100);

create table my_order(

id int primary key auto_increment,

g_id int not null comment '商品ID',

g_number int comment '商品数量'

)charset utf8;

--触发器：订单生成一个，商品库存减少

--临时修改语句结束符

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\G;

--查看触发器创建语句

show create trigger 触发器名字;

show create trigger after_order\G

所有触发器都会存到一个系统表中information_schema.triggers

select * from information_schema.triggers

使用触发器

不需要手动调用，而是当某种情况发生时，会自动触发

--订单插入记录会自动触发

--触发器工作了，订单生成之后，对应商品数量减少了

--当前商品减少的，不是订单中产生的商品；而是固定的商品(不合理)

--插入订单

insert into my_order values(null,1,2);

修改触发器&删除触发器

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

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

update my_goods set inv = inv - new.g_number where id = new.g_id;

end

$$

delimiter ;

insert into my_order values(null,1,2);

代码执行结构

三种：顺序结构 、分支结构、循环结构

分支结构

实现准备多个代码块，按照条件选择性的执行某段代码

在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循环

while 条件判断 do

--满足条件要执行的代码

--变更循环条件

end while;

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

mysql中没有对应的continue和break;但是有替代吕

iterate 迭代，类似continue ,后面的代码不执行，循环重新来过

leave 离开 ，类似break;

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

--定义循环名字

循环名字：while 条件 do

--循环体

--循环控制

leave/iterate 循环名字

end while;

函数

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

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

系统函数：直接调用即可

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

mysql中字符串的基本单位，最常用的是字符

substring 字符串截取,字符为单位substring(str,pos,len);

char_length 字符长度

length字节长度

instr判断字符串是否在某个具体字符串中存在

lpad左填充，将字符串，按照某个指定的填充方式，填充指定长度

insert 字符串替换

strcmp 字符串比较

--定义两个变量

set @cn = '世界你好';

set @en = 'hello world';

--字符串截取,mysql中字符串下标从1开始，截取单位为字符

select substring(@cn,1,1);

select substring(@en,1,1);

--字符长度

select char_length(@cn),char_length(@en),length(@cn),length(@en);

--字符串寻找,0代表没有找到

select instr(@cn,'界'),instr(@en,'ll'),instr(@cn,'知道');

--字符串填充

select lpad(@cn,20,'欢迎'),lpad(@en,20,'hello');

--字符串替换

select insert(@en,3,3,'y'),@en;

自定义函数

函数要素：函数名、参数列表(形参和实参)，返回值，函数体

创建语法

create function 函数名([形参列表]) returns 数据类型 --规定要返回的数据类型

begin

--函数体

--返回值 return类型(指定数据类型)

end

--创建函数

create function display1() returns int

return 100;

--调用函数

select display1();

--查看所有函数

show function status [like ''];

函数属于指定数据，只有在对应数据库下调用

查看函数的创建

show create function 函数名;

show create function display1;

修改函数&删除函数

函数不能修改，先删除后新增

drop function 函数名;

drop function display1;

函数参数

参数分为两种：定义时的参数叫形参，

--函数：计算1-指定数之间的和

-- 求和：任何变量要修改必须使用set关键字

--@定义的变量是全局变量，没有的是局部变量

delimiter $$

create function display1(int_1 int) returns int

begin

set @i = 1 ;

set @res = 0 ;

while @i <= int_1 do

set @res =@res+ @i;

set @i = @i+1;

end while;

return @res;

end

$$

delimiter ;

在函数内部使用@定义的变量在函数外部也可以访问

作用域

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 ;

存储过程

存储过程是一种没有返回值的函数

创建过程

create procedure 过程名字([参数列表])

begin

--过程体

end

--创建过程

--假设过程中需要显示数据：使用select

create procedure pro1()

select * from my_student;

查看过程

查看所有过程

show procedure status [like ''];

--查看过程创建语句

show create procedure pro1;

调用过程

过程没有返回值，select是不能访问的

过程有一个专门的调用关键字 call

call pro1();

修改过程&删除过程

过程不能修改，只能先删除后新增

drop procedure 过程名;

drop procedure pro1;

过程参数

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

过重还有自己的类型限定：三种类型

in:数据只是从外部传入内部使用(值传递)；可以是数值，也可以是变量

out:只允许过程内部使用(不用外部数据)，给外部使用的(引用传递，外部的数据会被

先清空才会进入到内部)，只以是变量

inout:外部可以在内部使用，内部修改也可以给外部使用；典型的引用传递，只能是变量

基本使用：

create procedure 过程名(in 形参名字 数据类型,out 形参名字 数据类型,inout 形参名字 数据类型)

--过程参数

--int_2的值一定是null，Out数据会被先清空

delimiter $$

create procedure pro1(in int_1 int,out int_2 int,inout int_3 int)

begin

select int_1,int_2,int_3;

end

$$

delimiter ;

调用 :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;

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;

select @int_1,@int_2,@int_3;

end

$$

delimiter ;

select @int_1,@int_2,@int_3;

最后：在存储过程调用结束之后，系统会将局部变量重新返回给全局变量，只有out,inout;

分享到：

2016-03-23 18:49

浏览 961

分类:数据库

评论