Mysql 游标(cursor)、视图(View)、触发器(trigger)、定时任务
目录Mysql 游标（cursor）Mysql 视图（View）Mysql 触发器 trigger数据准备：DROP TABLE IF EXISTS `book_2`;CREATE TABLE `book_2` (`id` int(11) PRIMARY KEY auto_increment,`title` varchar(128) NOT NULL,`price` float(8,2) DEFA
·
目录
数据准备：sql/mysql/新建书籍与分类表.sql · 汪少棠/material - Gitee.com
Mysql 游标（cursor）
1、游标用于操作查询的结果集，类似 JDBC 的 ResultSet，主要用于存储过程中操作结果数据。
| 1、定义游标:DECLARE 光标名 CURSOR FOR select 语句; 2、打开游标: open 光标名; 3、取出当前游标指向的行：fetch 光标名 into 其它变量; 4、关闭游标：close 游标名; |
2、越界标识：
declare flag int default 1;
declare continue|exit|undo handler for not found set flag := 0;
# flag 变量声明在游标之前，当游标取值为空时，会触发越界标识，将 flag 置为 0
# continue：后面的语句继续执行
# exit：后面的语句结束
# undo：前面的语句撤销3、举例如下：
drop procedure if exists param10; #如果存在存储过程 param10，则删除它
delimiter // #临时改变语句结束符
create procedure param10(in total int) #申明存储过程
begin #开始
declare v_id int; #定义变量,用于接收值
declare v_title varchar(128); #定义变量,用于接收值
declare v_prive float(8,2); #定义变量,用于接收值
declare v_publish date; #定义变量,用于接收值
declare v_info varchar(128); #定义变量,用于接收值
declare flag int default 1; #定义变量，作为结束标识
declare cur_book2 cursor for select * from book_2 limit total; #定义游标
declare exit handler for not found set flag := 0; #设置游标越界标识
open cur_book2; #打开游标
while flag !=0 do # while 循环，flag 不等于0时，一直循环,等于0时推出
fetch cur_book2 into v_id,v_title,v_prive,v_publish,v_info; #获取游标当前记录的值赋值给变量
select v_id,v_title,v_prive,v_publish,v_info from dual; #获取变量的值
end while; #循环结束
close cur_book2; #关闭游标
end // #结束
delimiter ; #重新设置语句结束符号为 ";"
call param10(3); #调用存储过程Mysql 视图（View）
1、视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。
| create [or replace] view 视图名 as 查询语句; create [algorithm=undefined | merge | temptable] view 视图名 as 查询语句; | 创建视图，[or replace] 表示存在时，则替换 |
| alter [or replace] view 视图名 as 查询语句 ; | 修改视图 |
| drop view 视图名 ; | 删除视图 |
-- 查询每个书籍及其类型，使用视图
CREATE OR REPLACE VIEW vw_book AS SELECT
t1.id,
t1.title,
t1.price,
t1.publish,
t1.info,
t2.NAME AS 'type'
FROM book t1 LEFT JOIN book_type t2 ON t1.type_id = t2.id;
SELECT * FROM vw_book; -- 查询视图3、视图引用方式(算法)有三种，[algorithm = {undefined | merge | temptable}]：
| undefined：默认为 undefined，表示由系统选择，一般是 merge merge:会将查询视图的语句与视图定义 where 合并起来 temptable: 视图的结果将被置于临时表中，然后使用它执行语句 navicat 工具中，选择设计视图，高级选项中可以看到详细设置 |
Mysql 触发器 trigger
数据准备：sql/mysql/新建学生与班级表.sql · 汪少棠/material - Gitee.com
1、很多关系数据库中都提供一种技术，可以在用户进行某种操作的时候，自动的进行另外一个操作，这种技术称为触发器技术.
2、触发器是指存放在数据库中，被隐含执行的存储过程，可以支持 dml 触发器，还支持基于系统事件(启动数据库,关闭数据库,登陆)和 ddl 操作建立触发器。
| 创建触发器 | create trigger 触发器名称 after|before insert|update|delete on 表名 for each row --new 关键字：当触发插入(insert)和更新(update)事件时可用，指向的是被操作的新记录 |
| 查看触发器 | show triggers; |
| 删除触发器 | drop trigger 触发器名 |
示例1：student 表插入数据时触发
drop trigger if exists tri_insert_student; # 当存储触发器 tri_insert_student 时，删除它
# 创建触发器：当 student 表新增学生时，往班级表 classes 对应的班级人数加 1
delimiter // #临时修改语句结束符为 "//"
create trigger tri_insert_student after insert on student for each row #创建触发器
begin #开始
update classes set total = total + 1 where name = new.classes_name; #sql操作
end // #结束
delimiter ; # 再将结束符修改为分号
drop trigger if exists tri_update_classes; # 当存储触发器 tri_update_classes 时，删除它
# 创建触发器：当新增时，如果没有 info 为 null，则默认让它等于 '尖子班'。
# Mysql 无法像 Oralce 一样设置在新增和更新操作时同时触发.
delimiter // #临时修改语句结束符为 "//"
create trigger tri_update_classes BEFORE INSERT on classes for each row #创建触发器
begin #开始
IF new.info IS NULL THEN
set new.info = '尖子班'; # 修改即将插入的字段值.
END IF;
end // #结束
delimiter ; #再将结束符号修改回分号示例2：student 表删除数据时触发
drop trigger if exists tri_delete_student; # 当存储触发器 tri_delete_student 时，删除它
# 创建触发器，当 student 表删除学生时，往班级表 classes 对应的班级人数减 1
delimiter // #临时修改语句结束符为 "//"
create trigger tri_delete_student after delete on student for each row #创建触发器
begin #开始
update classes set total = total - 1 where name = old.classes_name; #sql操作
end // #结束
delimiter ; #再将结束符号修改回分号示例3：更新操作后触发，将修改前的数据备份到 his 表
-- 创建备份表
create table dept_his as SELECT * from dept where 1=2;
-- 创建触发器，更新操作后触发，将修改前的数据备份到 his 表
DELIMITER //
CREATE TRIGGER TRIG_dept_Update
AFTER UPDATE ON dept
FOR EACH ROW
BEGIN
INSERT INTO dept_his (deptno,dname,loc) VALUES (OLD.deptno,OLD.dname,OLD.loc);
END //
DELIMITER ;
定时任务
1、Mysql 事件是一种在特定时间点自动执行的数据库操作，也可以称呼为定时任务，它可以自动执行更新数据、插入数据、删除数据等操作，无需人工干预。创建事件或定时任务可以解决很多重复性工作，配合着动态 sql 和存储过程能起到实时更新数据功能，不需要人工干预，提高了工作效率
-
自动化： 可以定期执行重复性的任务，无需手动干预。
-
灵活性： 可以根据需求定制事件，灵活控制任务的执行时间和频率。
-
提高效率： 可以在非高峰时段执行耗时任务，减少对数据库性能的影响。
2、常见的应用场景有定时备份数据库，清理和统计数据。
查看事件:
-- 查看事件调度器是否开启：ON 表示已开启。
show variables like '%event_scheduler%';
set global event_scheduler = ON; # 开启事件调度器
set global event_scheduler = OFF; # 关闭事件调度器
-- 使用 show 或者 select 语句查看当前数据库中所有的事件
show events;
select * from information_schema.events;启动事件：alter event event_name enable;
关闭事件：alter event event_name disable;
删除事件：drop event [if exists] event_name;
创建事件
1、create event 创建事件基本语法：
create
[definer = user] # 可选，用于定义事件执行时检查权限的用户
event
[if not exists] # 可选，一般都加上，用于判断要创建的事件是否存在
event_name # 是用来唯一标识事件的名称。在同一个数据库中，事件名称必须是唯一的。
on schedule schedule_body # 用于定义执行的时间和时间间隔
[on completion [not] preserve] # 可选，指定事件是否循环执行，默认为一次执行，即 not preserve。
[enable | disable | disable on slave] # 可选，指定事件的一种属性，enable 表示启动，disable 表示关闭或者下线，disable on slave 表示从属性上禁用，默认启动
[comment 'comment'] # 可选，添加事件的注释
# 必选，event_body 用于指定事件启动时所要执行的代码，
# 可以是任何有效的sql 语句、存储过程或者一个计划执行的事件。
# 如果包含多条语句，可以使用 begin … end 复合结构。
do event_body; 2、schedule_body 语法：
at timestamp [+ interval interval] ... | every interval
[starts timestamp [+ interval interval] ...]
[ends timestamp [+ interval interval] ...]
# 相当于“三周两天后”。此类子句的每个部分必须以+ interval。
at current_timestamp + interval 3 week + interval 2 day2.1、at timestamp：用于一次性活动，指定事件仅在 timestamp 给出的日期和时间执行一次，时间戳必须同时包含日期和时间，或者必须是解析为日期时间值的表达式，如果日期已过，则会出现警告。
2.2、every interval：每隔一段时间执行事件，指定时间区间内每隔多长时间发生一次，interval 其值由一个数值和单位(quantity)组成，格式如下，如 4 week 表示 4 周，’1:10’ HOUR_MINUTE 表示1小时10分钟。
interval:
quantity {year | quarter | month | day | hour | minute |
week | second | year_month | day_hour | day_minute |
day_second | hour_minute | hour_second | minute_second}2.3、starts timestamp：指定事件的开始时间，timestamp 为时间戳，日期时间值表达式。
2.4、ends timestamp：指定事件的结束时间，timestamp 为时间戳，日期时间值表达式。
常见时间调度
| 示例 | 描述 |
|---|---|
| on schedule every 30 minute | # 每30分钟执行一次，任务创建时立刻会执行一次。 |
| on schedule every 1 hour starts '2024-01-03 18:00:00' | # 从 2024-01-03 18:00:00 开始，每1小时执行一次 |
| on schedule every 12 hour starts current_timestamp + interval 30 minute ends current_timestamp + interval 4 week | # 从现在起30分钟后开始，四周后结束，这段期间内每12小时执行一次 |
精选示例
# 新建表
drop table if exists face_sales_data;
create table if not exists face_sales_data
(
sales_date date comment '销售日期',
order_code varchar(255) comment '订单编码',
user_code varchar(255) comment '客户编号',
product_name varchar(255) comment '产品名称',
sales_province varchar(255) comment '销售省份',
sales_number int comment '销量',
create_time datetime default current_timestamp comment '创建时间',
update_time datetime default current_timestamp on update current_timestamp comment '更新时间'
);示例1：构造实时数据，需求：每分钟录入关于产品、省份的订单销售数据。
-- 如果任务已经存在，则先删除
drop event if exists face_sales_data_task1;
-- #将sql语句的结束符由默认的分号";"临时改为"//"(可以自定义符号)，这样里面才能正常使用分号";"，否则会被当做结束符处理。
delimiter //
# 创建事件任务，多条语句用 begin ... end; 包住。
create event if not exists face_sales_data_task1
on schedule every 1 minute
# starts '2024-01-14 17:35:00'
on completion preserve enable
do
begin
set @user_code = floor(rand()*900000000 + 100000000);-- 随机生成用户编码，
set @order_code = md5(floor(rand()*900000000 + 100000000));-- 根据随机用户编码加密成编码
-- 随机从中选择产品
set @product_name = ELT(CEILING(RAND() * 8) ,'iPhone 15','iPhone 15 Pro','iPhone 15 Pro Max','Xiaomi 14','Xiaomi 14 Pro','Huawei Mate 60','Huawei Mate 60 Pro','Huawei Mate 60 Pro+');-- 随机从中选择产品
-- 随机从中选择省份
set @sales_province = ELT(CEILING(RAND() * 34) ,'河北省','山西省','辽宁省','吉林省','黑龙江省','江苏省','浙江省','安徽省','福建省','江西省','山东省','河南省','湖北省','湖南省','广东省','海南省','四川省','贵州省','云南省','陕西省','甘肃省','青海省','台湾省','内蒙古自治区','广西壮族自治区','西藏自治区','宁夏回族自治区','新疆维吾尔自治区','北京市','上海市','天津市','重庆市','香港特别行政区','澳门特别行政区');
set @sales_number = floor(rand()*1000);-- 随机生成销量
select @user_code,@order_code,@product_name,@sales_province,@sales_number;-- 查看生成的数据
-- 数据录入
insert into face_sales_data(sales_date, order_code, user_code, product_name, sales_province, sales_number)
values (curdate(),@order_code,@user_code,@product_name,@sales_province,@sales_number);
end;
// # 结束
delimiter ; #将语句的结束符号恢复为分号
-- 过一段时间再一次查看表，可以发现每条记录 create_time 相差1，说明任务调度设置成功，这样就可以自动模拟实时销售数据啦。
select * from face_sales_data;示例2： 调用存储过程
drop procedure if exists hello_world; -- 如果存在指定的存储过程，则删除
#将sql语句的结束符由默认的分号";"临时改为"$$"(可以自定义符号)，这样里面才能正常使用分号";"，否则会被当做结束符处理
delimiter $$
create procedure hello_world(in id int) # 声明存储过程
begin # 开始
set @user_code = floor(rand()*900000000 + 100000000);-- 随机生成用户编码，
set @order_code = md5(floor(rand()*900000000 + 100000000));-- 根据随机用户编码加密成编码
-- 随机从中选择产品
set @product_name = ELT(CEILING(RAND() * 8) ,'iPhone 15','iPhone 15 Pro','iPhone 15 Pro Max','Xiaomi 14','Xiaomi 14 Pro','Huawei Mate 60','Huawei Mate 60 Pro','Huawei Mate 60 Pro+');-- 随机从中选择产品
-- 随机从中选择省份
set @sales_province = ELT(CEILING(RAND() * 34) ,'河北省','山西省','辽宁省','吉林省','黑龙江省','江苏省','浙江省','安徽省','福建省','江西省','山东省','河南省','湖北省','湖南省','广东省','海南省','四川省','贵州省','云南省','陕西省','甘肃省','青海省','台湾省','内蒙古自治区','广西壮族自治区','西藏自治区','宁夏回族自治区','新疆维吾尔自治区','北京市','上海市','天津市','重庆市','香港特别行政区','澳门特别行政区');
set @sales_number = floor(rand()*1000);-- 随机生成销量
select @user_code,@order_code,@product_name,@sales_province,@sales_number;-- 查看生成的数据
-- 数据录入
insert into face_sales_data(sales_date, order_code, user_code, product_name, sales_province, sales_number)
values (curdate(),@order_code,@user_code,@product_name,@sales_province,@sales_number);
end $$ # 结束
delimiter ; #将语句的结束符号恢复为分号
drop event if exists get_hello_world_task1; # 如果任务已经存在则先删除
create event if not exists get_hello_world_task1 # 如果任务不经存在则创建
on schedule every 1 minute # 每隔1分钟执行一次
starts current_timestamp # 立刻开始执行
ends current_timestamp + interval 1 week # 1周后结束
on completion preserve enable
do call hello_world(111); # 调用存储过程
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)