[mysql]游标和触发器
目录
游标（或光标）
定义
使用过程
示例
总结
触发器
应用场景
定义
使用
创建
查看
删除
示例
一个注意点：
优缺点
拓展：MySQL 8.0的新特性—全局变量的持久化
游标（或光标）
定义
游标是一种 能够对结果集中的每一条记录进行定位（像指针一样，向前定位一条记录、向后定位一条记录，或者是 随意定位到某一条记录 ），并对指向的记录中的数据进行操作的数据结构。
游标让 SQL这种面向集合的语言有了面向过程开发的能力。
特点：
-
在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。
这里游标充当指针的作用,通过操作游标来对数据行进行操作.
-
游标必须在声明处理程序之前被声明
相对的，变量和条件则必须在声明游标和处理程序之前被声明。
使用过程
使用游标一般分4步，不同DBMS细节不同。
-
声明游标
-
mysql(还有SQL Server，DB2 和 MariaDB)
DECLARE cursor_name CURSOR FOR select_statement;
-
Oracle 或者 PostgreSQL
DECLARE cursor_name CURSOR IS select_statement;
select_statement:要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，所以这里要通过SELECT 语句，返回一个用于创建游标的结果集。
-
-
打开游标
定义好游标之后，使用游标前必须先打开游标。
打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。
OPEN cursor_name
-
使用游标
使用即通过它来获取数据
FETCH cursor_name INTO var_name [, var_name] ... #这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中， #游标指针指到下一行。
注意：
-
如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
-
var_name必须在声明游标之前就定义好。
-
游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致
-
-
关闭游标
CLOSE cursor_name
使用完游标后需要关闭掉该游标 。下次还要使用就要重新打开游标。
游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。
示例
创建存储过程“get_count_by_limit_total_salary()”，
声明IN参数 limit_total_salary，DOUBLE类型；
声明 OUT参数total_count，INT类型。
函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。
DELIMITER // CREATE PROCEDURE get_count_by_limit_total_salary( IN limit_total_salary DOUBLE,OUT total_count INT) BEGIN DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资 DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值 DECLARE emp_count INT DEFAULT 0; #记录循环个数 #定义游标 DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC; #打开游标 OPEN emp_cursor; REPEAT #使用游标（从游标中获取数据） FETCH emp_cursor INTO cursor_salary; SET sum_salary = sum_salary + cursor_salary; SET emp_count = emp_count + 1; UNTIL sum_salary >= limit_total_salary END REPEAT; SET total_count = emp_count; #关闭游标 CLOSE emp_cursor; END // DELIMITER ;
总结
-
优点：为逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
-
缺点：比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。
建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率
触发器
应用场景
有 2 个或者多个相互关联的表，如商品信息和库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。
这样一来，我们就必须把这两个关联的操作步骤同步写到程序里面。可对数据进行手动维护时，很容易忘记其中的一步 ，导致数据缺失。
这时可以使用触发器：创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。
定义
MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。
所谓事件就是指 用户的动作或者触发某项行为。
如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
使用
触发器不能更新或覆盖；为了修改一个触发器，必须先删再建。
使用触发器
-
insert 触发器
-
在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行；
-
在before insert触发器内，new中的值也可以被更新(允许更改被插入的值)；
-
delete 触发器
-
在delete触发器代码内，可以引用一个名为old的虚拟表，访问被删除的行；
-
old中的值全都是只读的，不能更新；
-
update 触发器
-
在update触发器代码内，可以引用一个名为old的虚拟表访问以前(update语句前)的值，引用一个名为new的虚拟表访问新更新的值；
-
在before update触发器中，new中的值可能也被更新(允许更改将要用于update语句中的值)；
-
old中的值全都是只读的，不能更新；
另外的 tips ：
-
创建触发器可能需要特殊的安全访问权限，但触发器的执行是自动的。如果insert、update或delete语句可以执行，则相应触发器也能执行；
-
应该用触发器来保证数据的一致性(大小写、格式等)；优点在于它总是进行这种处理，而且是透明的进行，与客户机应用无关；
-
触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易；
-
MySQL触发器不支持call语句，即不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
创建
CREATE TRIGGER 触发器名称
{BEFORE|AFTER}
{INSERT|UPDATE|DELETE}
ON 表名
FOR EACH ROW
触发器执行的语句块;
-
表名 ：表示触发器监控的对象。
-
BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
-
INSERT|UPDATE|DELETE ：表示触发的前提事件。
查看
查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
-
查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
-
查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
-
从系统库
information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。SELECT * FROM information_schema.TRIGGERS;
删除
DROP TRIGGER IF EXISTS 触发器名称;
触发器作为数据库对象之一，也适用于DROP删除。
示例
DELIMITER // CREATE TRIGGER salary_check_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN DECLARE mgrsalary DOUBLE; SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; IF NEW.salary > mgrsalary THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; END IF; END // DELIMITER ;
一个注意点：
如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此
时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子
表的UPDATE和DELETE语句定义的触发器并不会被激活。
优缺点
-
优点：
-
触发器可以确保数据的完整性。
-
触发器可以帮助我们记录操作日志
-
触发器还可以用在操作数据前，对数据进行合法性检查。
-
-
缺点：
-
触发器最大的一个问题就是可读性差
因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能 不受应用层的控制 。这对系统维护是非常有挑战的。
#创建触发器用于修改会员储值操作。如果触发器中的操作出了问题，会导致会员储值金额更新失败 update demo.membermaster set memberdeposit=20 where memberid = 2; ERROR 1054 (42S22): Unknown column 'aa' in 'field list'
这是因为，触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是会员信息表的结构出了问题。说不定你还会给会员信息表添加一个叫“aa”的字段，试图解决这个问题，结果只能是白费力
-
相关数据的变更，可能会导致触发器出错。
特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。
-
拓展：MySQL 8.0的新特性—全局变量的持久化
在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。
例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：
SET GLOBAL MAX_EXECUTION_TIME=2000;
但使用SET GLOBAL语句设置的变量值只会临时生效 : 即数据库重启 后，服务器又会从MySQL配置文件中读取变量的默认值。
MySQL 8.0版本新增了 SET PERSIST 命令。
例如：设置服务器的最大连接数为1000：
SET PERSIST global max_connections = 1000;
MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。