mysql 存储过程实现从一张表数据迁移到另一种表

通过存储过程迁移数据：

创建表

CREATE TABLE `test1` (
  `idp` varchar(255) DEFAULT NULL,
  `brandIdp` varchar(255) DEFAULT NULL,
  `namep` varchar(1000) DEFAULT NULL,
  `urlp` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=keybcs2;
INSERT INTO `test1` VALUES ('1001', '3004', 'lili', 'www.baidu.com');
INSERT INTO `test1` VALUES ('1002', '3005', 'lucy', 'www.baidu.com');
INSERT INTO `test1` VALUES ('1003', '3004', 'lile', 'www.baidu.com');

CREATE TABLE `test2` (
  `idp` varchar(255) DEFAULT NULL,
  `brandIdp` varchar(255) DEFAULT NULL,
  `namep` varchar(1000) DEFAULT NULL,
  `urlp` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=keybcs2;

CREATE TABLE `test3` (
  `idp` varchar(255) DEFAULT NULL,
  `brandIdp` varchar(255) DEFAULT NULL,
  `namep` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=keybcs2;

建立存储过程：

drop PROCEDURE IF EXISTS p;
CREATE PROCEDURE p()
BEGIN

-- 需要定义接收游标数据的变量
DECLARE idp VARCHAR(255);
DECLARE brandIdp VARCHAR(255);
DECLARE namep VARCHAR(1000);
DECLARE urlp VARCHAR(1000);

-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT
test1.idp,
test1.brandIdp,
test1.namep,
test1.urlp
from test1;

-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 打开游标
OPEN cur;

-- 开始循环
read_loop: LOOP

-- 提取游标里的数据
FETCH cur INTO idp,brandIdp,namep,urlp;

-- 声明结束的时候
IF done THEN

LEAVE read_loop;

END IF;

-- 这里做你想做的循环的事件
INSERT INTO test2 VALUES (idp,brandIdp,namep,urlp);
INSERT INTO test3 VALUES (idp,brandIdp,namep);

END LOOP;

-- 关闭游标
CLOSE cur;

END

执行存储过程

call p

使用存储过程，中间遇到了很多坑

CREATE DEFINER=root@localhostPROCEDUREuser_logs`()
begin
– 声明一个标志done， 用来判断游标是否遍历完成
DECLARE done INT DEFAULT FALSE;
– 声明一个变量，用来存放从游标中提取的数据
– 特别注意这里的名字不能与由游标中使用的列明相同，否则得到的数据都是NULL
DECLARE lid varchar(255) DEFAULT NULL;
DECLARE lname varchar(255) DEFAULT NULL;
DECLARE lpid varchar(255) DEFAULT NULL;
DECLARE lzindex varchar(255) DEFAULT NULL;
DECLARE listype varchar(50) DEFAULT NULL;
DECLARE ldescpt varchar(50) DEFAULT null;
DECLARE lcode varchar(50) DEFAULT NULL;
DECLARE licon varchar(50) DEFAULT NULL;
DECLARE lpage varchar(50) DEFAULT NULL;
DECLARE linsert_time varchar(50) DEFAULT NULL;
DECLARE lupdate_time varchar(50) DEFAULT NULL;
– 声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR select id,name, pid,zindex,istype,descpt,code,icon,page,insert_time,update_time from permission;
– 在游标循环到最后会将 done 设置为 TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
– 执行查询
open cur;
– 遍历游标每一行
read_loop: LOOP
– 提取游标里的数据
FETCH cur INTO lid,lname,lpid,lzindex,listype,ldescpt,lcode,licon,lpage,linsert_time,lupdate_time ;
– 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO permissions(id,name, pid,zindex,istype,descpt,code,icon,page,insert_time,update_time) VALUES( lid,lname,lpid,lzindex,listype,ldescpt,lcode,licon,lpage,linsert_time,lupdate_time);
delete from permission;
END LOOP;
– 关闭游标
CLOSE cur;

end
 

存储过程定时实现循环查询结果更新到新表中

-- 查询统计数据
select * from cold_chain_report ;

-- 查看定时策略是否开启
show variables like '%event_sche%';
-- 开启定时任务策略
SET GLOBAL event_scheduler = ON;

-- 删除存储过程
DROP PROCEDURE plush_report_data;

-- 创建存储过程
USE fad_20201206;
delimiter //
create procedure plush_report_data ()
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE org_Id1 varchar(255);
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR SELECT ID FROM sysorganization_base WHERE isdel=0    AND JB<>4;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into org_Id1;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 执行业务逻辑
    DELETE from cold_chain_report where ORGID = org_Id1;
    INSERT INTO cold_chain_report (`ORGID`) VALUES (org_Id1);
    UPDATE cold_chain_report set LRZPC = (SELECT COUNT(1) FROM e_storage_order_info a  WHERE a.DZ_ID LIKE CONCAT(org_Id1,'%'))  where ORGID = org_Id1;
-- 将游标中的值再赋值给变量，供下次循环使用
fetch report into org_Id1;
-- 当s等于1时表明遍历以完成，退出循环
end while;
-- 关闭游标
close report;
END
delimiter ;

-- 删除定时任务
DROP EVENT plush_report_data_event;

-- 创建定时任务  每天凌晨1点执行
create event plush_report_data_event
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour)  
on completion preserve disable
do call plush_report_data ();

-- 查看定期任务
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
 
-- 开启
alter event plush_report_data_event on completion preserve enable;