利用游标先遍历表数据，再根据循环插入数据

最新推荐文章于 2021-04-07 23:04:52 发布

weixin_34392843

最新推荐文章于 2021-04-07 23:04:52 发布

阅读量137

点赞数

CC 4.0 BY-SA版权

文章标签： python

原文链接：https://my.oschina.net/u/2381372/blog/785782

本文提供了一个使用MySQL存储过程进行批量数据插入的例子。通过定义存储过程`batchCall`，实现循环插入100条记录到`edu_performance_data`表中，并从`cap_edut`表获取相关联的ID和IP地址。

2019独角兽企业重金招聘Python工程师标准>>>

DELIMITER $$
CREATE PROCEDURE `batchCall`()
    BEGIN
	DECLARE id VARCHAR(32);
	DECLARE create_time VARCHAR(255);
	DECLARE done INT DEFAULT 0;
	DECLARE done2 INT ;
	DECLARE edu_id VARCHAR(255) DEFAULT '';
	DECLARE edu_ip VARCHAR(255);
	DECLARE rs CURSOR FOR (SELECT t.id AS edu_id,t.edu_ip AS edu_ip FROM cap_edu t);
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN rs;
	myloop:LOOP
		FETCH FROM rs INTO edu_id,edu_ip;
		IF done = 1 THEN
			LEAVE myloop;
		END IF;
		SET done2 = 0;
		my2loop:LOOP
			IF done2 >= 100 THEN
				LEAVE my2loop;
			END IF;
			SET id = REPLACE(UUID(),'-','');
			SELECT RandomDateTime(DATE_FORMAT('2016-8-1 00:00:00','%Y-%m-%d %H:%i:%s'),DATE_FORMAT('2016-10-31 23:59:59','%Y-%m-%d %H:%i:%s')) INTO create_time;
			INSERT INTO edu_performance_data VALUES (id, done2, NULL, NULL, NULL, NULL, NULL, NULL, create_time, edu_id, edu_ip, 1, NULL, NULL, NULL, NULL, NULL, NULL);
			SET done2 = done2+1;
		END LOOP my2loop;
	END LOOP myloop;
	CLOSE rs;
    END$$
DELIMITER ;

DELIMITER $$
CREATE
    FUNCTION `RandomDateTime`(sd DATETIME,ed DATETIME)
    RETURNS DATETIME
    BEGIN
    RETURN DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)))-1)) SECOND);
    END$$
DELIMITER ;

转载于:https://my.oschina.net/u/2381372/blog/785782