使用 mysql 游标 批量更新数据

游标由于安全性问题,感觉用到的地方还是比较少,这里由于测试数据需要批量更新用到了,所以就mark一下。

本次使用游标目的是:遍历数据表,同时更新DATETIME类型的字段为某范围内随机时间。

首先是生成范围内随机日期的sql:

SELECT CONCAT(FLOOR(1980 + (RAND() * 35)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0));


然后是生成随机时间的sql:

SELECT CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));

日期和时间拼起来,就可以赋值了,下面是存储过程:

DELIMITER $$

DROP PROCEDURE IF EXISTS `time_cursor`;
CREATE PROCEDURE `time_cursor` ()
BEGIN
	DECLARE tcomdate,tjoindate,tquitdate,tbirthdate,tstartdate,tenddate DATETIME;
	DECLARE tempno VARCHAR(10);
	DECLARE done INT DEFAULT 0;
	DECLARE cur CURSOR FOR SELECT empno FROM t_personsales;
	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
	OPEN cur;
	FETCH cur INTO tempno;
	REPEAT
		IF NOT done THEN
			SELECT CONCAT(FLOOR(1990 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tcomdate;
			SELECT CONCAT(FLOOR(2000 + (RAND() * 15)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tjoindate;
			SELECT CONCAT(FLOOR(2010 + (RAND() * 6)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tquitdate;
			SELECT CONCAT(FLOOR(1960 + (RAND() * 40)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tbirthdate;
			SELECT CONCAT(FLOOR(1999 + (RAND() * 10)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tstartdate;
			SELECT CONCAT(FLOOR(1999 + (RAND() * 15)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tenddate;
			UPDATE t_personsales SET COMDATE = tcomdate,JOINDATE = tjoindate,QUITDATE = tquitdate,BIRTHDATE = tbirthdate,CHK_START_DATE = tstartdate, CHK_END_DATE = tenddate where empno = tempno;
		END IF;
	FETCH cur INTO tempno;
	UNTIL done = 1
	END REPEAT;
	CLOSE cur;
END

然后 call time_cursor(); 大功告成。


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/klaus_zyy/article/details/52384198
个人分类: mysql
上一篇在Ubuntu下安装OpenCV
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭