原表记录:
存储过程如下:
BEGIN
DECLARE docter_id_ VARCHAR(20);
DECLARE topic_id_ VARCHAR(128);
DECLARE create_time_ DATETIME;
DECLARE url_ VARCHAR(128);
DECLARE _done INT DEFAULT 0;
DECLARE cur_strs CURSOR FOR SELECT docter_id,topic_id,create_time,url from aaa ;
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET _done=1; END;
OPEN cur_strs;
FETCH cur_strs INTO docter_id_, topic_id_,create_time_,url_;
WHILE _done != 1 DO
DROP TABLE IF EXISTS temp;
CREATE TABLE temp(topic_id VARCHAR(128));
SET @b=topic_id_;
set @c=create_time_;
set @d=url_;
SET @a = CONCAT(CONCAT("insert into temp(topic_id) values('",REPLACE(@b,',',"'),('")),"')");
-- SELECT @a;
PREPARE sql1 FROM @a;
EXECUTE sql1;
SELECT docter_id_,t.*,@c,@d FROM temp t;
INSERT INTO aaaa(docter_id,topic_id,create_time,url) SELECT docter_id_,t.*,@c,@d FROM temp t;
COMMIT;
FETCH cur_strs INTO docter_id_, topic_id_,create_time_,url_;
END WHILE;
CLOSE cur_strs;
END;
保存存储过程后运行,结果如下: