mysql 大量数据插入（可用于数据添加字段不能添加的情况，因为会copy临时表。这样可以手动分批处理）

delimiter $$


drop procedure if exists P_insert_sale_order_detail_3 $$


create procedure P_insert_sale_order_detail_3(out p_num int,out p_num2 int)
BEGIN






select  @p_num:=min(order_detail_id),@p_num2:=max(order_detail_id)  from sale_order_detail ;
set p_num=@p_num;
set p_num2=@p_num2;


select  @p_num3:=max(order_detail_id)  from sale_order_detail_3 ;


if @p_num3>p_num
then 
set p_num=@p_num3;
end if ;




WHILE (p_num<=p_num2)
do


INSERT INTO sale_order_detail_3
(
 order_detail_id
,erp_order_detail_id
,order_id
,erp_order_id
,shop_id
,member_id
,create_time
,place_order_time
,pay_time
,sale_time
,outer_sku_id
,product_id
,erp_sku_id
,sku_id
,color_id
,size_id
,num
,update_timestamp
,network_price
,real_price
,cost_price
,network_amount
,real_amount
,discount_amount
,discount_rate
,avg_order_discount
,retained_amount
,post_fee
,is_presale
,shop_name
,member_name
,product_code
,color_name
,size_name
,sku_name
,outer_sku_name
,outer_order_id
,if_original
)
SELECT 


 b.order_detail_id
,b.erp_order_detail_id
,b.order_id
,b.erp_order_id
,b.shop_id
,b.member_id
,b.create_time
,b.place_order_time
,b.pay_time
,b.sale_time
,b.outer_sku_id
,b.product_id
,b.erp_sku_id
,b.sku_id
,b.color_id
,b.size_id
,b.num
,b.update_timestamp
,b.network_price
,b.real_price
,b.cost_price
,b.network_amount
,b.real_amount
,b.discount_amount
,b.discount_rate
,b.avg_order_discount
,b.retained_amount
,b.post_fee
,b.is_presale
,b.shop_name
,b.member_name
,b.product_code
,b.color_name
,b.size_name
,b.sku_name
,b.outer_sku_name
,b.outer_order_id
,b.if_original
FROM 
(
SELECT order_detail_id FROM sale_order_detail WHERE order_detail_id>=p_num+1
ORDER BY order_detail_id ASC 
LIMIT 10000


) a 
JOIN sale_order_detail b 
ON a.order_detail_id=b.order_detail_id
;
SET p_num=p_num+10000;


end while ;


end ;


$$


/*
call P_insert_sale_order_detail_3(@p_num,@p_num2 );
select @p_num,@p_num2;






select count(1),max(order_detail_id),count(distinct order_detail_id) from sale_order_detail_3;


*/