通过存储过程迁移数据:
创建表
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
结果