利用存儲過程將mysql數據庫中兩個表數據導入另一個庫兩個關聯表中

網站維護 2024-04-11 次 wordpress翻譯插件 外貿網站

先創建一個視圖

select `a`.`id` AS `id`,`a`.`cate_id` AS `cate_id`,`a`.`title` AS `title`,`a`.`seotitle` AS `seotitle`,`a`.`keywords` AS `keywords`,`a`.`description` AS `description`,`a`.`note` AS `note`,`a`.`hits` AS `hits`,`b`.`val` AS `val` from (`yb`.`cms_list` `a` join `yb`.`cms_list_c` `b`) where (`a`.`id` = `b`.`id`)

下面是存儲過程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_into_tables`()
BEGIN
declare done INT DEFAULT FALSE;
declare v_title VARCHAR(255);
declare v_keywords VARCHAR(255);
declare v_description LONGTEXT;
declare v_hits INT(10);
declare v_val MEDIUMTEXT;
declare cur CURSOR FOR select title,keywords,description,hits,val FROM news_view;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
FETCH cur into v_title,v_keywords,v_description,v_hits,v_val;
IF done THEN
LEAVE read_loop;
END IF;

insert into cf_1_news (catid, title,keywords,description,hits,status,uid,author,link_id,tableid) VALUES (999, v_title,v_keywords,v_description,v_hits,1,1,"danhanshang",0,0);
insert into cf_1_news_data_0 (id,uid, catid,content) VALUES (LAST_insert_ID(),1,999, v_val);
END LOOP;

CLOSE cur;
END