使用存储过程批量生成数据

存储过程调用：
call proc1(1000000,’000001’,2,2,23,2,3,2,2);
下面的代码可以查看执行笑话时间
set @t = UNIX_TIMESTAMP();
call proc1(1000000,’000001’,2,2,23,2,3,2,2);
select @t, UNIX_TIMESTAMP() as t;

存储过程创建：

DROP PROCEDURE IF EXISTS proc1;
DELIMITER //
CREATE PROCEDURE proc1(
IN totalnum int(6), #生成库存数量
IN businessid char(32), #商户id
IN goodsid char(32), #商品id
IN credence varchar(50),#商品编号
IN endtime int(10), #有效期
IN price decimal(10), #成本价
IN nowmaxnum int, #目前最大数
IN users varchar(15), #操作人
IN ip varchar(15) #ip地址
)
top:
BEGIN
if totalnum > 100000 THEN #如果生成总数大于10w直接跳出存储过程
leave top;
end if;
DECLARE credenceno varchar(50); #定义商品编号
DECLARE createtime varchar(50) DEFAULT UNIX_TIMESTAMP(); #当前时间
DECLARE storageid char(32); #库存id
DECLARE validatecode char(20); #验证码
DECLARE piececode char(20); #编号
DECLARE sqltext longtext;
DECLARE sqltext1 VARCHAR(1000);
DECLARE sqltext2 longtext;
DECLARE maxwhilenum int default 500; #内部循环的最大值,目前最大值5k多不到1w，太大_CONCAT函数会报错，
DECLARE num int; #内循环变量声明
set sqltext1 =’INSERT INTO xhhk_storage (storageID,businessid,goodsid,credenceno,validatecode,piececode,price,CREATION_DATE,storagestatus,endtime,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSIONs) VALUES ‘;
while (totalnum>0) do
set num = maxwhilenum; #获取本次循环内部循环最大次数
if totalnum < maxwhilenum then
set num = totalnum;
end if;
set sqltext2 = ‘’; #设置语句为空
while (num>0) do

set credenceno = CONCAT(credence,LPAD ((nowmaxnum+num) ,6 ,0));
set storageid = replace(uuid(),’-‘,’’);
set validatecode = rand_string(6); #_left(storageid,6);更快速获得随机值，直接截取uuid的生成数,这种方法生成1w个快4秒，不过虽然不相同但是都很相似
set piececode = rand_string(10); #_left(storageid,10);
set sqltext2 = CONCAT(sqltext2,”,(‘“,storageid,”‘,’”,businessid,”‘,’”,goodsid,”‘,’”,credenceno,”‘,’”,validatecode,”‘,’”,piececode,”‘,’”,price,”‘,’”,createtime,”‘,’”,0,”‘,”,endtime,”,’”,users,”‘,’”,users,”‘,’”,createtime,”‘,’”,ip,”‘,”,1,”)”);
set num = num-1;
end while;
set sqltext = CONCAT(sqltext1,substring(sqltext2,2)); #生成总执行的_sql语句
set @sqltext = sqltext; #这一步很重要，下一话_PREPARE的语句只接受@的变量所以要转换一下
PREPARE SQLStr1 FROM @sqltext; #准备语句
EXECUTE SQLStr1; #执行语句
DEALLOCATE PREPARE SQLStr1; #注销变量

set totalnum = totalnum - maxwhilenum; #总数减去本次循环数
set nowmaxnum = nowmaxnum + maxwhilenum; #下一个编号起始好加上循环值
end while;

END //
DELIMITER ;

随机字符串函数创建：
（转：http://hi.baidu.com/microji/item/15a1933a208ea04a023edc03）
DROP FUNCTION IF EXISTS rand_string;
DELIMITER
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str varchar(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’;
DECLARE return_str varchar(255) DEFAULT ‘’;
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END
DELIMITER $$