python之路--触发器, 储存过程, 事务

2022-10-26 11:05:29

一. 触发器

　　使用触发器可以定制用户对某一张表的数据进行 [增, 删 ,改] 操作时前后的行为, (注意没有查询),在进行增删改的时候出发的某个动作叫做触发器. 其实就是在增删改的时候另外执行了一段SQL语句. 触发器器是被动调用的不能由用户直接调用

　　一. 创建触发器

# 插入前

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN #begin和end里面写触发器要做的sql事情，注意里面的代码缩进，并且给触发器起名字的时候，名字的格式最好这样写，有表示意义，一看名字就知道要做什么，是给哪个表设置的触发器

    ...

END

# 插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 删除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 删除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 更新前

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

    ...

END

　　下面模拟一下触发器的工作过程

#准备表

CREATE TABLE cmd (  #这是一张指令信息表，你在系统里面执行的任何的系统命令都在表里面写一条记录

    id INT PRIMARY KEY auto_increment,  #id

    USER CHAR (32),  #用户

    priv CHAR (10),  #权限

    cmd CHAR (64),   #指令

    sub_time datetime, #提交时间

    success enum ('yes', 'no') #是否执行成功，0代表执行失败

);

CREATE TABLE errlog ( #指令执行错误的信息统计表，专门提取上面cmd表的错误记录

    id INT PRIMARY KEY auto_increment, #id

    err_cmd CHAR (64),  #错误指令

    err_time datetime   #错误命令的提交时间

);

#现在的需求是：不管正确或者错误的cmd，都需要往cmd表里面插入，然后，如果是错误的记录，还需要往errlog表里面插入一条记录

#若果没有触发器，我们会怎么实现，我们完全可以通过咱们的应用程序来做，根据cmd表里面的success这个字段是哪个值（yes成功，no表示失败），在给cmd插入记录的时候，判断一下这个值是yes或者no，来判断一下成功或者失败，如果失败了，直接给errlog来插入一条记录

#但是mysql说，你的应用程序可以省事儿了，你只需要往cmd表里面插入数据就行了，没必要你自己来判断了，可以使用触发器来实现，可以判断你插入的这条记录的success这个字段对应的值，然后自动来触发触发器，进行errlog表的数据插入

#创建触发器

delimiter //      （或者写$$，其他符号也行，但是不要写mysql不能认识的，知道一下就行了），delimiter 是告诉mysql，遇到这句话的时候，就将sql语句的结束符分号改成delimiter后面的//

CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW               #在你cmd表插入一条记录之后触发的。

BEGIN           #每次给cmd插入一条记录的时候，都会被mysql封装成一个对象，叫做NEW，里面的字段都是这个NEW的属性

    IF NEW.success = 'no' THEN           #mysql里面是可以写这种判断的，等值判断只有一个等号，然后写then

            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ;     #必须加分号，并且注意，我们必须用delimiter来包裹，不然，mysql一看到分号，就认为你的sql结束了，所以会报错

      END IF ;       #然后写end if，必须加分号

END//      #只有遇到//这个完成的sql才算结束

delimiter ;        #然后将mysql的结束符改回为分号

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志

INSERT INTO cmd (

    USER,

    priv,

    cmd,

    sub_time,

    success

)

VALUES

    ('chao','','ls -l /etc',NOW(),'yes'),

    ('chao','','cat /etc/passwd',NOW(),'no'),

    ('chao','','useradd xxx',NOW(),'no'),

    ('chao','','ps aux',NOW(),'yes');

#查询错误日志，发现有两条

mysql> select * from errlog;

+----+-----------------+---------------------+

| id | err_cmd         | err_time            |

+----+-----------------+---------------------+

|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |

|  2 | useradd xxx     | 2017-09-14 22:18:48 |

+----+-----------------+---------------------+

rows in set (0.00 sec)

　　二. 删除触发器

drop trigger tri_after_insert_cmd;

二. 存储过程

　　一. 先说一下存储过程的优缺点

　　　　存储过程的优点:

#1. 用于替代程序写的SQL语句，实现程序与sql解耦

#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

　　　　存储过程的缺点:

#1. 程序员扩展功能不方便

　　二. 创建存储过程(无参)

delimiter //

create procedure p1()

BEGIN

    select * from blog;

    INSERT into blog(name,sub_time) values("xxx",now());

END //

delimiter ;

#在mysql中调用

call p1(); #类似于MySQL的函数，但不是函数昂，别搞混了，MySQL的函数(count()\max()\min()等等)都是放在sql语句里面用的，不能单独的使用，存储过程是可以直接调用的  call 名字+括号;

#MySQL的视图啊触发器啊if判断啊等等都能在存储过程里面写，这是一大堆的sql的集合体，都可以综合到这里面

#在python中基于pymysql调用

cursor.callproc('p1')

print(cursor.fetchall())

　　三. 创建存储过程(有参)

对于存储过程，可以接收参数，其参数有三类：

#in          仅用于传入参数用

#out        仅用于返回值用

#inout     既可以传入又可以当作返回值

　　in: 传入参数:

delimiter //

create procedure p2(

    in n1 int,  #n1参数是需要传入的，也就是接收外部数据的，并且这个数据必须是int类型

    in n2 int

)

BEGIN

    select * from blog where id > n1;  #直接应用变量

END //

delimiter ;

#调用存储过程的两种方式：或者说是两个地方吧

　　#在mysql中调用

　　call p2(3,2)

　　#在python中基于pymysql调用

　　cursor.callproc('p2',(3,2))

　　print(cursor.fetchall())

　　out : 返回值 :

#查看存储过程的一些信息：show create procedure p3; #查看视图啊、触发器啊都这么看，还可以用\G，show create procedure p3\G;\G的意思是你直接查看表结构可能横向上显示不完，\G是让表给你竖向显示，一row是一行的字段

delimiter //

create procedure p3(

    in n1 int,

    out res int

)

BEGIN

    select * from blog where id > n1;

    set res = 1;  #我在这里设置一个res=1，如果上面的所有sql语句全部正常执行了，那么这一句肯定也就执行了，那么此时res=1，如果我最开始传入的时候，给res的值设置的是0，

#那么你想，最后我接收到的返回值如果是0，那么说明你中间肯定有一些sql语句执行失败了

#注意写法：out的那个参数，可以用set来设置，set设置之后表示这个res可以作为返回值，并且不需要像python一样写一个return，你直接set之后的值，就是这个存储过程的返回值

END //

delimiter ;

#在mysql中调用

set @res=0; #这是MySQL中定义变量名的固定写法(set @变量名=值)，可以自己规定好，0代表假（执行失败），1代表真（执行成功），如果这个被改为1了，说明存储过程中的sql语句执行成功了

call p3(3,@res);#注意：不要这样写：call p3（3，1），这样out的参数值你写死了，没法确定后面这个1是不是成功了，也就是说随后这个out的值可能改成0了，也就是失败了，但是这样你就判断不了了，你后面查看的这个res就成1了，所以这个参数应该是一个变量名昂，定义变量名就是上一句,如果你直接传一个常量数字，会报错的，写法不对。

select @res; #看一下这个结果，就知道这些sql语句是不是执行成功了，大家明白了吗~~~

#在python中基于pymysql调用，在python中只需要知道存储过程的名字就行了

cursor.callproc('p3',(3,0)) #0相当于set @res=0，为什么这里这个out参数可以写常数0啊，因为你用的pymysql,人家会帮你搞定，pymysql其实会帮你写成这样：第一个参数变量名：@_p3_0=3，第二个：@_p3_1=0，也就是pymysql会自动帮你对应上一个变量名，pymysql只是想让你写的时候更方便

#沿着网络将存储过程名和参数发给了mysql服务端，比咱们发一堆的sql语句肯定要快对了，mysql帮你调用存储过程

print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') #@_p3_0代表第一个参数，@_p3_1代表第二个参数，即返回值

print(cursor.fetchall())

#别忘了关掉：

cursor.close()

conn.close()

#注意昂：存储过程在哪个库里面建的，就只能在哪个库里面用

　　inout : 既可以传入又可以返回值:

delimiter //

create procedure p4(

    inout n1 int

)

BEGIN

    select * from blog where id > n1;

    set n1 = 1;

END //

delimiter ;

#在mysql中调用

set @x=3;

call p4(@x);

select @x;

#在python中基于pymysql调用

cursor.callproc('p4',(3,))

print(cursor.fetchall()) #查询select的查询结果

cursor.execute('select @_p4_0;')

print(cursor.fetchall())

三. 事务

　　事务的四大特性

　　　　原子性(sql不可分离,要么都成功,要么都失败.) (最主要)

　　　　一致性(不能丢东西,要一直, 转账,这面扣钱,那边就得收钱)

　　　　隔离性(不完全成功后,外人看不到,外人看到的信息还是你硬盘上的,只有commit之后刷到硬盘上,外人才能看到)

　　　　持久性(执行完了也就是commit之后,不能更改,只有去硬盘上操作)

create table user(

id int primary key auto_increment,

name char(32),

balance int

);

insert into user(name,balance)

values

('wsb',1000),

('chao',1000),

('ysb',1000);

#原子操作

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='chao'; #中介拿走10元

update user set balance=1090 where name='ysb'; #卖家拿到90元

commit;  #只要不进行commit操作，就没有保存下来，没有刷到硬盘上

#出现异常，回滚到初始状态

start transaction;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='chao'; #中介拿走10元

uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到

rollback;  #如果上面三个sql语句出现了异常，就直接rollback，数据就直接回到原来的状态了。但是执行了commit之后，rollback这个操作就没法回滚了

#我们要做的是检测这几个sql语句是否异常，没有异常直接commit，有异常就rollback，但是现在单纯的只是开启了事务，但是还没有说如何检测异常，我们先来一个存储过程来捕获异常，等我们学了存储过程，再细说存储过程。

commit;

#通过存储过程来捕获异常：(shit！，写存储过程的是，注意每一行都不要缩进！！！按照下面的缩进来写，居然让我翻车了！！！我记住你了~~~)，我的代码直接黏贴就能用。

delimiter //

create PROCEDURE p5()

BEGIN

DECLARE exit handler for sqlexception

BEGIN

rollback;

END;

START TRANSACTION;

update user set balance=900 where name='wsb'; #买支付100元

update user set balance=1010 where name='chao'; #中介拿走10元

#update user2 set balance=1090 where name='ysb'; #卖家拿到90元

update user set balance=1090 where name='ysb'; #卖家拿到90元

COMMIT;

END //

delimiter ;

# 然后调用这个过程
call p5();

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  1 | wsb  |    1000 |

|  2 | chao |    1000 |

|  3 | ysb  |    1000 |

+----+------+---------+

rows in set (0.00 sec)

码农公寓

一. 触发器

一. 创建触发器

二. 删除触发器

二. 存储过程

一. 先说一下存储过程的优缺点

二. 创建存储过程(无参)

三. 创建存储过程(有参)

三. 事务

相关文章

　　一. 创建触发器

　　二. 删除触发器

　　一. 先说一下存储过程的优缺点

　　二. 创建存储过程(无参)

　　三. 创建存储过程(有参)