mysql 批量更新（插入）表字段 清洗数据 存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `u_goods_support`()
BEGIN
declare i int;
set i = 0;
while i <= 100000 DO
UPDATE u_goods_support ugs
INNER JOIN goods_new_old gno ON ugs.prod_id = gno.old_code
INNER JOIN goods_classification gc ON gc.code = gno.classify
SET ugs.prod_id = gno.code, ugs.product_type = CASE WHEN gc.classification_ascription = '类' THEN 1 ELSE 2 END
WHERE ugs.id>=i AND ugs.id < i+1000;
commit;
set i = i+1000;
end while;
end

// 循环插入数据
DROP PROCEDURE
IF
	EXISTS test_insert;

DELIMITER;;
CREATE PROCEDURE test_insert ( ) BEGIN
DECLARE
		i INT DEFAULT 1;
	WHILE
			i < 1000000 DO
			INSERT INTO `shop` (`openid`) VALUES ('on6uJ5aWyG2rhkl4itbIYFNWo1BU');
			SET i = i + 1;
			END WHILE;
			COMMIT;
		END;;
CALL test_insert ();