drop procedure TestVoucher;
-- 定义存储过程
delimiter $$
create procedure TestVoucher()
begin
DECLARE max int;
DECLARE rc int;
declare tempVoucherId VARCHAR(60); -- 随机数
declare TEMP_VOUCHER_NO VARCHAR(60); -- 随机数
declare TEMP_GOODS_ID varCHAR(60);
declare TEMP_GOODS_NAME varCHAR(60);
declare time TIMESTAMP ;
set max =100;
set rc =1;
loopl: while rc<max do
SELECT CONCAT('99S000620160607',round(round(rand(),18)*1000000000000000)) into tempVoucherId;
SELECT CONCAT('99',round(round(rand(),10)*1000000000000000)) into TEMP_VOUCHER_NO;
select CONCAT('2016-06-16 ',LPAD(FLOOR(7 + (RAND() * 16)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) into time ;
SELECT CONCAT(FLOOR(1 + (RAND() * 6))) into TEMP_GOODS_ID;
SELECT CONCAT('商品名称',TEMP_GOODS_ID) into TEMP_GOODS_NAME;
insert into S0007_S_COMMON_VOUCHER(VOUCHER_ID,VOUCHER_NO,GOODS_ID,GOODS_NAME,CREATE_TIME) values(tempVoucherId,TEMP_VOUCHER_NO,TEMP_GOODS_ID,TEMP_GOODS_NAME,time);
set rc=rc+1;
end while loopl;
end$$
delimiter ;
call TestVoucher;
SELECT FLOOR(7 + (RAND() * 6)); 取7到12的随机整数,包括7到12
SELECT FLOOR(12+(rand()*13)); 取12到24的随机整数,包括12到24
LPAD(FLOOR(0 + (RAND() * 23)),2,0) 取0-24之间的随机数,不够2位的前补0
格式 : 2008-12-11 17:43:11
举例:
给用户赋一个随机入职日期,年份为1990-2014
select CONCAT(FLOOR(1990 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0))
结果:1999-10-07
select CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0))
结果:15:39:18
拼好之后,再写update语句就可以了