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

分手后的思念是犯贱 2024-03-30 13:14 52阅读 0赞

## 通过[存储过程][Link 1]迁移数据： ##

### 创建表 ###

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

![2be95ef132bd4cb3bb133e6ddf391817.png][]

![6c1ba43a93f3439c8d2e91fa5af69116.png][]

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

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  
![f2b84472e7c1497785ff5e3f4fbcf5db.png][]

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

\-- 查询统计数据  
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;

[Link 1]: https://so.csdn.net/so/search?q=%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B&spm=1001.2101.3001.7020
[2be95ef132bd4cb3bb133e6ddf391817.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/f8e17210f69e49aeb431bf36daa8775d.png
[6c1ba43a93f3439c8d2e91fa5af69116.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b7a0453097b84ae58fbfaa205edbd941.png
[f2b84472e7c1497785ff5e3f4fbcf5db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/1fbb0c21769041178e9597e65370422a.png