目录
游标(或光标)
定义
使用过程
示例
总结
触发器
应用场景
定义
使用
创建
查看
删除
示例
一个注意点:
优缺点
拓展: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 文件中,下次启动时会读取该文件,用其中的配置来覆盖默认的配置文件。