mysql 迭代更新_在MYSQL中不使用游标进行迭代

我是Stored Procedures的新手.

我的任务是编写一个存储过程,首先验证临时表中的数据,然后将数据插入主表.

为此,我计划迭代临时表的每一行,使用其他存储过程或用户定义的函数验证它,然后将数据插入主表.

我的问题是如何在不使用CURSORS的情况下迭代临时表的行,因为它们非常慢且占用内存.我想使用一些循环结构而不是CURSOR.

当然,如果任何人对上述问题有任何其他算法,那么欢迎提出建议.

PS：我正在使用MYSQL DB

解决方法:

如果不使用Cursor,您可以使用临时表和While..Do语句进行迭代.

假设你有两张桌子

CREATE TABLE `user` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(45) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM;

和

CREATE TABLE `tmp_user` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(45) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM;

创建以下例程,并调整验证过程：

DELIMITER $$

USE `routines_sample`$$

CREATE PROCEDURE `nocursor`()

BEGIN

Declare validId int;

Declare validName varchar(45);

-- drop the temporary table if exists

Drop table if exists `routines_sample`.tmp_validation;

-- create the temporary table, here you could use Engine=Memory

Create table `routines_sample`.tmp_validation (`id` int not null, `name` varchar(45) not null, `valid` bit(1) not null) Engine=MyISAM;

-- insert into the temporary table with a valid flag = 0 (false)

Insert into `routines_sample`.tmp_validation (`id`, `name`, `valid`)

Select tu.id, tu.name, 0

From `routines_sample`.tmp_user tu;

-- while exists data to validate on temporary table do something

While exists(Select `id` From `tmp_validation` Where `valid` = 0) Do

Select `id`, `name` Into @validId, @validName From tmp_validation Where `valid` = 0 Limit 1;

-- do your validation

Select @validId, @validName;

-- don't forget to update your validation table, otherwise you get an endless loop

Update `tmp_validation`

Set `valid` = 1

Where `id` = @validId;

END WHILE;

-- insert only valid rows to your destination table

Insert into `routines_sample`.`user` (`name`)

Select `name` From `tmp_validation`

Where `valid` = 1;

-- drop the temporary table

DROP TABLE tmp_validation;

END$$

DELIMITER ;

标签：mysql,cursor

来源： https://codeday.me/bug/20190521/1147130.html