MySQL一个表迁移到另一个库_mysql 存储过程实现从一张表数据迁移到另一种表

BEGIN

-- 需要定义接收游标数据的变量

DECLARE idp VARCHAR(255);

DECLARE brandIdp VARCHAR(255);

DECLARE namep VARCHAR(1000);

DECLARE urlp VARCHAR(1000);

DECLARE downloadUrlp VARCHAR(1000);

DECLARE sitep VARCHAR(255);

DECLARE appIdp VARCHAR(255);

DECLARE pathp VARCHAR(255);

DECLARE siteMemop VARCHAR(255);

DECLARE sizep BIGINT(255);

DECLARE errorp VARCHAR(255);

DECLARE totalsp int(10);

DECLARE checkp int(11);

DECLARE textp int(11);

DECLARE statusp TINYINT(255);

DECLARE createUserp VARCHAR(255);

DECLARE createDatep TIMESTAMP;

DECLARE updateDatep TIMESTAMP;

DECLARE updateUserp VARCHAR(255);

-- 遍历数据结束标志

DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT

brand_shuomingshu.pdfId AS id,

home_brand.id AS brandId,

brand_shuomingshu.pdfName AS `name`,

brand_shuomingshu.url AS url,

brand_shuomingshu.downloadUrl AS downloadUrl,

brand_shuomingshu.site AS site,

brand_shuomingshu.appId AS appId,

brand_shuomingshu.pdfPath As path,

brand_shuomingshu.memo AS siteMemo,

brand_shuomingshu.pdfSize AS size,

brand_shuomingshu.pdfError AS error,

brand_shuomingshu.pdfTotals AS totals,

brand_shuomingshu.pdfCheck AS `check`,

brand_shuomingshu.pdfText AS text,

brand_shuomingshu.pdfStatus AS `status`,

brand_shuomingshu.createUser AS createUser,

brand_shuomingshu.createDate AS createDate,

brand_shuomingshu.updateUser AS updateUser,

brand_shuomingshu.updateDate AS updateDate

from brand_shuomingshu,home_brand where brand_shuomingshu.brandName = home_brand.`name` AND brand_shuomingshu.`status` >0

AND brand_shuomingshu.pdfStatus>0;

-- 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 打开游标

OPEN cur;

-- 开始循环

read_loop: LOOP

-- 提取游标里的数据

FETCH cur INTO idp,brandIdp,namep,urlp,downloadUrlp,sitep,appIdp,pathp,siteMemop,sizep,errorp,totalsp,checkp,textp,statusp,createUserp,createDatep,updateUserp,updateDatep;

-- 声明结束的时候

IF done THEN

LEAVE read_loop;

END IF;

-- 这里做你想做的循环的事件

INSERT INTO home_brand_manual_pdf VALUES (idp,brandIdp,namep,urlp,downloadUrlp,sitep,appIdp,pathp,siteMemop,sizep,errorp,totalsp,checkp,textp,statusp,createUserp,createDatep,updateUserp,updateDatep);

END LOOP;

-- 关闭游标

CLOSE cur;

END