MySQL5.8版本编写存储过程批量造数-CSDN博客

本文链接：https://blog.csdn.net/weixin_43225147/article/details/134716128

– -------存储过程-while

– 删除存储过程

drop procedure  IF EXISTS proc16_while

– 创建存储过程

delimiter $$
create procedure proc16_while()
begin
    declare i int default 1;
    label:while i<= 300 do
        INSERT INTO `product_spu`(`id`, `name`, `keyword`, `introduction`, `description`, `bar_code`, `category_id`, `brand_id`, `pic_url`, `slider_pic_urls`, `video_url`, `unit`, `sort`, `status`, `spec_type`, `price`, `market_price`, `cost_price`, `stock`, `delivery_template_id`, `recommend_hot`, `recommend_benefit`, `recommend_best`, `recommend_new`, `recommend_good`, `give_integral`, `give_coupon_template_ids`, `sub_commission_type`, `activity_orders`, `sales_count`, `virtual_sales_count`, `browse_count`, `create_time`, `update_time`, `creator`, `updater`, `deleted`, `tenant_id`) 
				VALUES (2000001+i, concat('多商品测试-',i), '多商品测试', '商品简介', '<p>商品详情</p><p><img src=\"https://hyt-test-1315113155.cos.ap-guangzhou.myqcloud.com/hyt-test-1315113155/2ce3bcf5e4f309167ff280fa8cec667a4d1e2df2bdbcbb33764012876fa0ecdf.png\" alt=\"image\" data-href=\"https://hyt-test-1315113155.cos.ap-guangzhou.myqcloud.com/hyt-test-1315113155/2ce3bcf5e4f309167ff280fa8cec667a4d1e2df2bdbcbb33764012876fa0ecdf.png\" style=\"\"/></p>', '', 27, 7, 'https://hyt-test-1315113155.cos.ap-guangzhou.myqcloud.com/hyt-test-1315113155/2ce3bcf5e4f309167ff280fa8cec667a4d1e2df2bdbcbb33764012876fa0ecdf.png', '[\"https://hyt-test-1315113155.cos.ap-guangzhou.myqcloud.com/hyt-test-1315113155/56b9e496a68da02043767b54f394053c94707d680212f1bbaad2ef18d524fc48.jpg\"]', NULL, 1, 666, 1, b'0', 1100, 2200, 900, 99, 6, b'1', b'1', b'1', b'1', b'1', 1, '', b'0', '[]', 0, 0, 0, '2023-11-30 10:18:40', '2023-11-30 10:18:40', '1', '1', b'0', 0);
				INSERT INTO `product_sku`(`id`, `spu_id`, `properties`, `price`, `market_price`, `cost_price`, `bar_code`, `pic_url`, `stock`, `weight`, `volume`, `first_brokerage_price`, `second_brokerage_price`, `sales_count`, `create_time`, `update_time`, `creator`, `updater`, `deleted`, `tenant_id`) 
				VALUES (1000001+i, 2000001+i, '[{\"propertyId\":0,\"propertyName\":\"默认\",\"valueId\":0,\"valueName\":\"默认\"}]', 1100, 2200, 900, '111', 'https://hyt-test-1315113155.cos.ap-guangzhou.myqcloud.com/hyt-test-1315113155/2ce3bcf5e4f309167ff280fa8cec667a4d1e2df2bdbcbb33764012876fa0ecdf.png', 99, 0.02, 0.01, 0, 0, 0, '2023-11-30 10:18:40', '2023-11-30 10:18:40', '1', 1, b'0', 0);

        set i=i+1;
    end while label;
end $$
delimiter ;

– 调用存储过程

call proc16_while();

– 查询表结果

SELECT * from product_spu;
SELECT * from product_sku;

– 清除批量数据

delete from product_spu where id >2000000;
delete from product_sku where id >1000000;