MYSQL存储过程：批量更新数据

地区等级的信息储存在jsjh_district表。

要更新jsjh_goods_district表的district_level地区信息

DELIMITER $$ DROP PROCEDURE IF EXISTS update_district_level $$CREATE PROCEDURE update_district_level()BEGINDECLARE row_id INT;#定义变量IDDECLARE row_district_id INT;#定义变量地区IDDECLARE row_level INT;#定义变量地区等级DECLARE done INT;-- 定义游标DECLARE rs_cursor CURSOR FORSELECT main.id,main.district_id,d.`level` FROM jsjh_goods_district main LEFT JOIN jsjh_district d ON d.id=main.district_id;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;OPEN rs_cursor; cursor_loop:LOOPFETCH rs_cursor INTO row_id,row_district_id,row_level; -- 取数据IF done=1 THENleave cursor_loop;END IF;-- 更新表UPDATE jsjh_goods_district SET district_level=row_level WHERE id=row_id;END LOOP cursor_loop;CLOSE rs_cursor;END$$DELIMITER ;

 执行存储过程

CALL update_district_level();

/* Affected rows: 0  已找到记录: 0  警告: 0  持续时间 1 query: 1.014 sec. */

执行时间1秒多