MySql数据游标遍历使用以及踩坑记录

MySql游标遍历使用以及踩坑记录

拿到一个需求，需要把已有表里的一些数据迁移到新的表里做业务上的适配，也就是新功能不影响到老的用户，本来想着用Mybatis写点逻辑，但是一想，这个操作只需要执行一次，用mybatis的话总不能每次程序一跑就迁移一次吧，所以想着Mysql会不会有类似脚本的东西能够只写sql语句的情况下就把数据迁移好，查阅百度，我发现了Mysql中有游标的用法，通过对一个结果集（select 语句）创建游标，在每次对游标fetch操作后游标会自动指向结果集的下一条数据，以此对结果集进行循环并进行相应操作。

下面是代码环节

创建两张测试表testtable1和testtable2，下文简称t1，t2

CREATE TABLE `testtable1` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `start_time` datetime DEFAULT NULL,
  `expired_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `testtable2` (
  `seller_id` bigint(10) NOT NULL,
  `validity_from` datetime DEFAULT NULL,
  `validity_to` datetime DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

t1呢把他看做你需要循环获取数据的数据表，t2看作需要写入数据的新表

下面是t1的模拟数据

遍历t1首先需要循环，mysql中有三种循环的用法，我这边选择loop

先写一个代码块的模板

drop procedure if exists serviceinit;
CREATE PROCEDURE serviceinit()
BEGIN
	***具体代码***
END;
CALL serviceinit();

然后定义需要在游标循环过程中接收数据的变量

drop procedure if exists serviceinit;
CREATE PROCEDURE serviceinit()
BEGIN
	DECLARE done int default false; 
	DECLARE sellerId bigint(10)	default 0;
	DECLARE startTime datetime;
	DECLARE expiredTime datetime;
END;
CALL serviceinit();

参数解释：

done：用来表示游标的循环是否结束，因为游标不知道自己是否结束，所以需要定义一个标志

sellerId：用来接收t1表中的id，改成什么都行

startTime&expiredTime：分别用来接收t1表中的start_time，expired_time

定义好用来接收游标中数据的变量后就需要定义一个游标了

drop procedure if exists serviceinit;
CREATE PROCEDURE serviceinit()
BEGIN
	DECLARE done int default false; 
	DECLARE sellerId bigint(10)	default 0;
	DECLARE startTime datetime;
	DECLARE expiredTime datetime;
	
	DECLARE timeCur CURSOR for select id,start_time,expired_time from testtable1;
	declare continue HANDLER for not found set done = true;
END;
CALL serviceinit();

DECLARE timeCur CURSOR for select id,start_time,expired_time from testtable1;//这条语句就是为后面select的结果集生成一个游标

declare continue HANDLER for not found set done = true;//这条语句是mysql捕获异常的语句，在这边也就是当游标已经循环到底了那就需要退出循环了

设置好变量和游标后就开始写具体的逻辑

先上完整代码

drop procedure if exists serviceinit;
CREATE PROCEDURE serviceinit()
BEGIN
	DECLARE done int default false; 
	DECLARE sellerId bigint(10)	default 0;
	DECLARE startTime datetime;
	DECLARE expiredTime datetime;
	
	DECLARE timeCur CURSOR for select id,start_time,expired_time from testtable1;
	declare continue HANDLER for not found set done = true;
	
	OPEN timeCur;
	read_loop: LOOP
	fetch timeCur into sellerId,startTime,expiredTime;
	
	IF done THEN
		LEAVE read_loop; 
	ELSE
		insert INTO testtable2(seller_id,validity_from,validity_to,created_time)VALUE(sellerId,startTime,expiredTime,now());
	END IF; 
	END LOOP read_loop;
	CLOSE timeCur;
END;
CALL serviceinit();

挨个解释

首先是 打开这个游标，在打开的时候就已经指向第一条数据了，当关闭游标时游标就不可用了

OPEN timeCur;
	***
CLOSE timeCur;

然后是loop循环

OPEN timeCur;
	read_loop: LOOP
	***
	END LOOP read_loop;
CLOSE timeCur;

这边的read_loop就是指向当前所在的loop，可以通过LEAVE read_loop退出当前loop，这个read_loop只是个名字，可以改成任何字符，read_loop:LOOP;和END LOOP read_loop;表示这这个循环的边界，循环是在这个边界内执行的。

最后是具体的逻辑

OPEN timeCur;
	read_loop: LOOP
	fetch timeCur into sellerId,startTime,expiredTime;
	
	IF done THEN
		LEAVE read_loop; 
	ELSE
		insert INTO testtable2(seller_id,validity_from,validity_to,created_time)VALUE(sellerId,startTime,expiredTime,now());
	END IF; 
	END LOOP read_loop;
CLOSE timeCur;

通过fetch将timeCur这个游标中的数据依次放入sellerId,startTime,expiredTime;这几个字段中，这里有个坑，接受字段命名上不能和创建游标时的select后面的字段一样

下图情况就会产生fetch后目标字段seller_id,start_time,expired_time都为null的问题

错误示范

DECLARE timeCur CURSOR for select id,start_time,expired_time from testtable1;
fetch timeCur into seller_id,start_time,expired_time;

然后是循环部分，我看到有些教程的顺序是这样写的

OPEN timeCur;
	read_loop: LOOP
	fetch timeCur into sellerId,startTime,expiredTime;
	insert INTO testtable2(seller_id,validity_from,validity_to,created_time)VALUE(sellerId,startTime,expiredTime,now());
	IF done THEN
		LEAVE read_loop; 		
	END IF; 
	END LOOP read_loop;
CLOSE timeCur;

这样在我这个场景下会有问题，在fetch不到数据的时候先触发了 declare continue HANDLER for not found set done = true;

但是此时代码还没运行到 IF done THEN LEAVE read_loop; 这里所以会多执行一次insert的逻辑，相关的值是最后一次fetch的值。

到此这个逻辑就结束了最后通过call调用一下定义的方法就可以了。

看下效果