参考博客:http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
需求:向taskbill表压入几百万数据,其中taskbill_no列的格式 如 RZD000069455
drop procedure task_insert; -- 删除这个存储过程
DELIMITER // -- 定义分隔符//
create PROCEDURE task_insert(count int unsigned)
begin
declare num int unsigned default 1;
declare str varchar(20) default 'RZD1';
while num <= count do
INSERT into t(s) values (CONCAT(str,LPAD(num,8,'0'))); -- 拼接字符串,并对num前补0
set num = num+1;
end while;
end;
//
DELIMITER ; -- 分隔符变为默认的;
call task_insert(1000) -- 受影响的行: 1 时间: 1.672ms
运行效果如图
保存一下写的sql 和上述一致。
-- drop procedure task_insert;
DELIMITER //
create PROCEDURE task_insert(count int unsigned)
begin
declare num int unsigned default 1;
declare str varchar(20) default 'RZD1';
while num <= count do
INSERT into taskbill(taskbill_no,task_type,main_taskbill_no,driver_no,driver_name,driver_phone,car_id,cartype_no,
cartype_name,car_no,car_batch_no,dist_service_id,dist_service_name,batch_id,delivery_time,origin_name) values (CONCAT(str,LPAD(num,8,'0')),10,
taskbill.taskbill_no,'1034201017',concat('新旧宫--司机',num),18212346990,302928374,2015012201,'车型test','京L09876','test1',
5948277494,'晨鲜送',29,1467302400 + 86400*floor(rand()*30),'北京仓库1');
set num = num+1;
end while;
end;
//
DELIMITER ;
call task_insert(1)