问题:插入2016年1月到11月之间的随机时间戳。
方案:
定义两个变量,并用CONCAT实现连接。
set @dates= CONCAT(FLOOR(2016 + (RAND() * 1)),'-',LPAD(FLOOR(1 + (RAND() * 11)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0));
set @time= CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));
CONCAT(@dates,' ',@time)
实例sql“”
/*门诊设备数据*/
drop PROCEDURE if EXISTS out_patient_device;
create PROCEDURE out_patient_device()
begin
DECLARE num int DEFAULT 0;
DECLARE patientid int DEFAULT 2000;
DECLARE devicesn int DEFAULT 10100;
DECLARE IDS INT DEFAULT 1001;
truncate table patient_device_relation;
while num<10000 do
/*rand()取是是一个0和1之间的随机小数,FLOOR(X)返回不大于X的最大整数,floor(1.8)等于1*/
/*FLOOR(2016 + (RAND() * 1)取2016年,floor是取整函数*/
/*LPAD(FLOOR(8 + (RAND() * 3)),2,0)取9-11月之间的月份,并且lpad在月份之间填充0,如果大于两位,则不填充。如9月份则是09,10月是10*/
/*LPAD(FLOOR(1+(RAND() * 30)),2,0)取1-30号*/
set @dates= CONCAT(FLOOR(2016 + (RAND() * 1)),'-',LPAD(FLOOR(9 + (RAND() * 3)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 29)),2,0));
set @time= CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));
insert into PATIENT_DEVICE_RELATION (ID, PATIENT_ID, DEVICE_SN, DEVICE_TYPE, BIND_TIME, UNWRAP_TIME)
values (IDS, patientid, devicesn, '亲智',CONCAT(@dates,' ',@time), null);
set num=num+1;
set patientid=patientid+1;
SET IDS=IDS+1;
SET devicesn=devicesn+1;
end while;
end;