一、存储过程

1. 为什么需要使用存储过程

项目后台程序使用python编写，主要功能是通过<sip,dip,sport,dport>四元组聚合网络流量，在流级别上统计流量特征，对于嗅探到的每一个网络数据包都需要根据SQL查询语句的查询结果判断数据包是否属于某个已经存在的网络流，因此这里需要频繁执行SQL查询语句。但是数据库的SQL语句在执行的时候需要先编译，然后执行，对于需要频繁执行SQL语句的应用程序，这样的处理方式无疑大大降低了系统的性能。
存储过程采用另一种方式来执行SQL语句
一个存储过程是一个可编程函数，它在数据库中创建并保存，一般由SQL语句和一些特殊的控制结构组成。使用存储过程的优点如下：

1. 封装性
   存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
2. 可增强SQL语句的功能和灵活性
   存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
3. 可减少网络流量
   由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
4. 高性能
   存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。
5. 提高数据库的安全性和完整性
   使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

2. 在mysql中创建带输入输出参数的存储过程

delimiter |

drop procedure if exists pro_select_tcpport;

create procedure pro_select_tcpport
(in s_ip varchar(15), in d_ip varchar(15), in s_port smallint, in d_port smallint, out result_count int)
begin
select count(*) 
into result_count
from rtsp_tcptb where sip=s_ip and dip=d_ip and sport=s_port and d_port;
end;

drop procedure if exists pro_select_udpport;

create procedure pro_select_udpport
(in s_ip varchar(15), in d_ip varchar(15), out c_port json, out s_port json)
begin 
select client_port,server_port
into c_port,s_port
from rtsp_udptb where sip in (s_ip,d_ip) and dip in (s_ip,d_ip) and client_port is not null and server_port is not null;
end |

delimiter ;

3. 在python程序中调用存储过程并获得存储过程的输出参数

import mysql.connector
from mysql.connector import Error

def call_pro_select_tcpport():
    s_ip = '192.168.3.xx'
    d_ip = '192.168.3.cc'
    s_port=10554
    d_port=10666
    try:
        db = mysql.connector.connect(user='xxx', password='xxx', host='localhost', port='3306', database='xxx')
        cursor = db.cursor()
        results = cursor.callproc('pro_select_tcpport', (s_ip, d_ip, s_port, d_port, 0)) #输出参数使用数字0占位
        print(results) #输出结果包含四个元素（s_ip, d_ip, s_port, d_port, count(*)）,例如：(192.168.3.112,192.168.3.108,10554,10666,1),输出参数为1
        print(results[4]) #打印存储过程中的输出参数
    except Error as e:
        print(e)

def call_pro_select_udpport():
    s_ip = '192.168.3.xx'
    d_ip = '192.168.3.cc'
    try:
        db = mysql.connector.connect(user='xxx', password='xxx', host='localhost', port='3306', database='xxx')
        cursor = db.cursor()
        results = cursor.callproc('pro_select_udpport', (s_ip, d_ip, 0, 0)) #两个输出参数使用数字0占位
        print(results) #输出结果包含四个元素（s_ip, d_ip, s_port, d_port）
        print(results[2],results[3]) #打印存储过程中的两个输出参数
    except Error as e:
        print(e)

---

二、触发器

1. 为什么使用触发器

为了提高查询效率，在流过期后会从数据库中删除相关的流记录信息，精简主表，但是这样并不利于后期的查询需求，因此需要创建触发器，在删除一条主表记录的同时将删除的数据备份到另一张表格中。
mysql中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

2. 在mysql中创建触发器

DELETE触发器：

• 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
• OLD 中的值全部是只读的，不能被更新。

DELIMITER $$
USE `flow_db`$$

drop trigger if exists rtsp_tcptb_BEFORE_DELETE;
CREATE DEFINER=`root`@`localhost` TRIGGER `rtsp_tcptb_BEFORE_DELETE` BEFORE DELETE ON `rtsp_tcptb` FOR EACH ROW BEGIN
insert ignore into rtsp_tcptb_backup
values(old.sip,old.dip,old.sport,old.dport,old.session,old.starttime,current_timestamp);
END$$

drop trigger if exists rtsp_udptb_BEFORE_DELETE;

CREATE DEFINER=`root`@`localhost` TRIGGER `rtsp_udptb_BEFORE_DELETE` BEFORE DELETE ON `rtsp_udptb` FOR EACH ROW BEGIN
insert ignore into rtsp_udptb_backup 
values (old.sip,old.dip,old.sport,old.dport,old.session,old.starttime,current_timestamp,old.client_port,old.server_port);
END$$

DELIMITER ;

触发器是由预定义事件触发的，不需要在应用程序中额外调用。

欢迎指正！