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 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 ();