8.7.4 mysql 内置功能
一 存储过程
一 存储过程介绍
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点：
#1. 用于替代程序写的SQL语句，实现程序与sql解耦 #2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
使用存储过程的缺点：
#1. 程序员扩展功能不方便
补充：程序与数据库结合使用的三种方式
#方式一：
MySQL：存储过程
程序：调用存储过程
#方式二：
MySQL：
程序：纯SQL语句
#方式三：
MySQL:
程序：类和对象，即ORM（本质还是纯SQL语句）
二 创建简单存储过程（无参）
delimiter // create procedure p1() BEGIN select * from score; END // delimiter ; #在mysql中调用 call p1() #在python中基于pymysql调用 cursor.callproc('p1') print(cursor.fetchall())
mysql> use cmz; Database changed mysql> show tables; +---------------+ | Tables_in_cmz | +---------------+ | class | | class_grade | | course | | score | | student | | teacher | | teacher2cls | +---------------+ 7 rows in set (0.00 sec) mysql> delimiter // # 表示sql语句的结束是// 而不是; mysql> create procedure p1() -> BEGIN -> select * from score; -> END // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; # 还原sql语句的结束标志位; mysql> call p1(); # 调用存储过程 +-----+------------+-----------+-------+ | sid | student_id | course_id | score | +-----+------------+-----------+-------+ | 1 | 1 | 1 | 60 | | 2 | 1 | 2 | 59 | | 3 | 2 | 2 | 59 | | 4 | 3 | 2 | 80 | +-----+------------+-----------+-------+ 4 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
pycharm下调用存储过程
#!/usr/bin/env python # _*_ coding: utf-8 _*_ import pymysql # 建立连接 conn = pymysql.connect( host="127.0.0.1", port=3306, user="cmz", passwd="cmz", db="cmz", # 建有存储过程的库 charset="utf8" ) # 拿到游标 cursor = conn.cursor() cursor.callproc("p1") # 调用存储过程,p1 位存储过程名字 print(cursor.fetchall()) # 拿到数据 cursor.close() conn.close()
结果是
C:\Python35\python.exe D:mysql模块之存储过程.py ((1, 1, 1, 60), (2, 1, 2, 59), (3, 2, 2, 59), (4, 3, 2, 80))
结果和在终端上一致
三 创建存储过程（有参）
对于存储过程，可以接收参数，其参数有三类： #in 仅用于传入参数用 #out 仅用于返回值用 #inout 既可以传入又可以当作返回值
mysql> delimiter // mysql> create procedure p2(in n1 int,in n2 int,out res int) -> BEGIN -> select * from score where course_id=n1 and score >n2 ; -> set res = 1; -> END // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> set @x=0; Query OK, 0 rows affected (0.00 sec) mysql> call p2(2,60,@x); # 在mysql中调用 +-----+------------+-----------+-------+ | sid | student_id | course_id | score | +-----+------------+-----------+-------+ | 4 | 3 | 2 | 80 | +-----+------------+-----------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> select @x; # 查看执行后的结果 +------+ | @x | +------+ | 1 | +------+ 1 row in set (0.00 sec)
在pycharm中
import pymysql # 建立连接 conn = pymysql.connect( host="127.0.0.1", port=3306, user="cmz", passwd="cmz", db="cmz", charset="utf8" ) # 拿到游标 cursor = conn.cursor() cursor.callproc('p2',(2,60,0)) # 在python中基于pymysql调用，0 相当于set @x=0 print(cursor.fetchall()) cursor.execute("select @_p2_2") # @_p2_0=2 表示第一个参数,@_p2_1=60 表示第二个参数,@_p2_2=0表示第三个参数 print(cursor.fetchall()) # 查询select查询结果 cursor.close() conn.close()
结果
C:\Python35\python.exe D:MySQL/mysql模块之存储过程.py ((4, 3, 2, 80),) ((1,),)
应用程序与数据库结合使用
方式1：
python: 调用存储过程
MySQL: 编写存储过程
方式2：
Python 编写纯生SQL
MySQL
方式3：
Python ORM->纯生SQL
MySQL
四 触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
一 创建触发器
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
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, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum ('yes', 'no') #0代表执行失败 ); CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime ); #创建触发器 delimiter // CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN IF NEW.success = 'no' THEN #等值判断只有一个等号 INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号 END IF ; #必须加分号 END// delimiter ; #往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('cmz','0755','ls -l /etc',NOW(),'yes'), ('cmz','0755','cat /etc/passwd',NOW(),'no'), ('egon','0755','useradd xxx',NOW(),'no'), ('egon','0755','ps aux',NOW(),'yes');
运行过程：
mysql> CREATE TABLE cmd ( -> id INT PRIMARY KEY auto_increment, -> USER CHAR (32), -> priv CHAR (10), -> cmd CHAR (64), -> sub_time datetime, #提交时间 -> success enum ('yes', 'no') #0代表执行失败 -> ); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE errlog ( -> id INT PRIMARY KEY auto_increment, -> err_cmd CHAR (64), -> err_time datetime -> ); Query OK, 0 rows affected (0.03 sec) mysql> delimiter // mysql> CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW -> BEGIN -> IF NEW.success = 'no' THEN #等值判断只有一个等号 -> INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号 -> END IF ; #必须加分号 -> END// Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> INSERT INTO cmd ( -> USER, -> priv, -> cmd, -> sub_time, -> success -> ) -> VALUES -> ('cmz','0755','ls -l /etc',NOW(),'yes'), -> ('cmz','0755','cat /etc/passwd',NOW(),'no'), -> ('egon','0755','useradd xxx',NOW(),'no'), -> ('egon','0755','ps aux',NOW(),'yes'); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2018-03-26 17:19:38 | | 2 | useradd xxx | 2018-03-26 17:19:38 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec)
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
删除触发器
drop trigger tri_after_insert_cmd;
四 事物
drop table user; create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('leco',1000), ('loocha',1000), ('cmz',1000); #原子操作 start transaction; update user set balance=900 where name='leco'; #买支付100元 update user set balance=1010 where name='loocha'; #中介拿走10元 update user set balance=1090 where name='cmz'; #卖家拿到90元,出现异常没有拿到 commit; select * from user; #-------------------------------------------------- drop table user; create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('leco',1000), ('loocha',1000), ('cmz',1000); start transaction; update user set balance=900 where name='leco'; #买支付100元 update user set balance=1010 where name='loocha'; #中介拿走10元 update user set balance=1090 where name='cmz'; #卖家拿到90元,出现异常没有拿到 select * from user; rollback; commit; select * from user;
1 为什么要事务 事务是一组不可被分割执行的SQL语句集合，如果有必要，可以撤销。银行转账是经典的解释事务的例子。用户A给用户B转账5000元主要步骤可以概括为如下两步。 第一，账户A账户减去5000元； 第二，账户B账户增加5000元； 这两步要么成功，要么全不成功，否则都会导致数据不一致。这就可以用到事务来保证，如果是不同银行之间的转账还需要用到分布式事务。 2 事务的性质 事务的机制通常被概括为“ACID”原则即原子性（A）、稳定性（C）、隔离性（I）和持久性（D）。 原子性：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。 稳定性：数据库在事务执行前后状态都必须是稳定的。 隔离性：事务之间不会相互影响。 持久性：事务执行成功后必须全部写入磁盘。 3 事务隔离性实现原理 数据库事务会导致脏读、不可重复读和幻影读等问题。 脏读：事务还没提交，他的修改已经被其他事务看到。 不可重复读：同一事务中两个相同SQL读取的内容可能不同。两次读取之间其他事务提交了修改可能会造成读取数据不一致。 幻影数据：同一个事务突然发现他以前没发现的数据。和不可重复读很类似，不过修改数据改成增加数据。 针对可能的问题，InnoDB提供了四种不同级别的机制保证数据隔离性。 事务的隔离用是通过锁机制实现的，不同于MyISAM使用表级别的锁，InnoDB采用更细粒度的行级别锁，提高了数据表的性能。InnoDB的锁通过锁定索引来实现，如果查询条件中有主键则锁定主键，如果有索引则先锁定对应索引然后再锁定对应的主键（可能造成死锁），如果连索引都没有则会锁定整个数据表。
mysql> #原子操作 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update user set balance=900 where name='leco'; #买支付100元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1010 where name='loocha'; #中介拿走10元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1090 where name='cmz'; #卖家拿到90元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | leco | 900 | | 2 | loocha | 1010 | | 3 | cmz | 1090 | +----+--------+---------+ 3 rows in set (0.00 sec)
中间有异常
mysql> drop table user; update user set balance=900 where name='leco'; #买支付100元 update user set balance=1010 where name='loocha'; #中介拿走10元 update user set balance=1090 where name='cmz'; #卖家拿到90元,假如出现异常没有拿到 select * from user; rollback; commit; select * from user;Query OK, 0 rows affected (0.01 sec) mysql> create table user( -> id int primary key auto_increment, -> name char(32), -> balance int -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> insert into user(name,balance) -> values -> ('leco',1000), -> ('loocha',1000), -> ('cmz',1000); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update user set balance=900 where name='leco'; #买支付100元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1010 where name='loocha'; #中介拿走10元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1090 where name='cmz'; #卖家拿到90元,假如出现异常没有拿到 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | leco | 900 | | 2 | loocha | 1010 | | 3 | cmz | 1090 | +----+--------+---------+ 3 rows in set (0.00 sec) mysql> rollback; # 回滚，到修改之前的数据 Query OK, 0 rows affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | leco | 1000 | | 2 | loocha | 1000 | | 3 | cmz | 1000 | +----+--------+---------+ 3 rows in set (0.00 sec)
