导航：首页 > 数据库 >

MySQL游标多循环一次的问题怎么解决

发表于：2025-11-14 作者：千家信息网编辑

千家信息网最后更新 2025年11月14日，这篇文章主要讲解了"MySQL游标多循环一次的问题怎么解决"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"MySQL游标多循环一次的问题怎么解决"吧！在

千家信息网最后更新 2025年11月14日MySQL游标多循环一次的问题怎么解决

这篇文章主要讲解了"MySQL游标多循环一次的问题怎么解决"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"MySQL游标多循环一次的问题怎么解决"吧！

在MySQL中使用游标的时候，发现游标多循环一次
想在表中插入一条语句，但是实际上却插入了两条
语句如下：
mysql> create table test(id int(5));
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter $$
mysql> create procedure proc_drop_table()
-> BEGIN
-> /* Declare Variables */
-> DECLARE done_1 INT DEFAULT FALSE;
-> DECLARE v_history_table_name varchar(64);
-> declare the_query VARCHAR(500);
->
-> /* Declare Conditions */
-> DECLARE not_found CONDITION FOR 1741;
->
-> /* Declare Cursors */
-> DECLARE cur1 CURSOR FOR select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME like '%test%';
->
-> /* Declare Exception Handlers, usually with set actions */
-> /* usually with set actions, the following handler has two forms,
/*> one with begin .. end statements, and the other without */
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_1 = TRUE;
->
-> OPEN cur1;
-> read_loop_1: LOOP
-> FETCH cur1 INTO v_history_table_name;
-> /*IF done_1 IS FALSE THEN*/
-> set @v_history_table_name=v_history_table_name;
-> select concat('insert into ', @v_history_table_name, ' values (10)') into the_query;
-> SET @stmt=the_query;
-> PREPARE STMT FROM @stmt;
-> EXECUTE STMT;
-> DEALLOCATE PREPARE STMT;
-> /*END IF;*/
-> IF done_1 THEN
-> LEAVE read_loop_1;
-> END IF;
-> END LOOP read_loop_1;
-> CLOSE cur1;
-> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select * from test;
Empty set (0.00 sec)

mysql> call proc_drop_table();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+------+
| id |
+------+
| 10 |
| 10 |
+------+
2 rows in set (0.00 sec)

解决方法：
在实际执行的语句两边增加IF判断

mysql> truncate table test;
Query OK, 0 rows affected (0.07 sec)

mysql> drop procedure proc_drop_table;
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter $$
mysql> create procedure proc_drop_table()
-> BEGIN
-> /* Declare Variables */
-> DECLARE done_1 INT DEFAULT FALSE;
-> DECLARE v_history_table_name varchar(64);
-> declare the_query VARCHAR(500);
->
-> /* Declare Conditions */
-> DECLARE not_found CONDITION FOR 1741;
->
-> /* Declare Cursors */
-> DECLARE cur1 CURSOR FOR select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) from information_schema.tables where TABLE_SCHEMA='test' and TABLE_NAME like '%test%';
->
-> /* Declare Exception Handlers, usually with set actions */
-> /* usually with set actions, the following handler has two forms,
/*> one with begin .. end statements, and the other without */
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_1 = TRUE;
->
-> OPEN cur1;
-> read_loop_1: LOOP
-> FETCH cur1 INTO v_history_table_name;
-> IF done_1 IS FALSE THEN
-> set @v_history_table_name=v_history_table_name;
-> select concat('insert into ', @v_history_table_name, ' values (10)') into the_query;
-> SET @stmt=the_query;
-> PREPARE STMT FROM @stmt;
-> EXECUTE STMT;
-> DEALLOCATE PREPARE STMT;
-> END IF;
-> IF done_1 THEN
-> LEAVE read_loop_1;
-> END IF;
-> END LOOP read_loop_1;
-> CLOSE cur1;
-> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc_drop_table();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00 sec)