用mysql随机生成出生日期。
因为出生日期在表中存的是date,我实在搞不懂怎么能产生并存进去
create function rand_birth(n int)
returns char(8)
begin
declare year char(5);
declare month char(2);
declare day char(2);
declare return_birth char(8);
declare i int default '0'; while i < n do
set year = floor(1997+rand()*32);
set month = round(rand(1,12));
if month = 2 then
set day = round(rand(1,28));
else if month%2 = 0 and month != 2 then
set day = round(rand(1,30));
else
set day = round(rand(1,31));
end if;
set return_birth = concat(year,month,day);
set i = i + 1;
end while;
return return_birth;
end
$$
~~~~这是我写的function~~~~~~大神帮我看哈
因为出生日期在表中存的是date,我实在搞不懂怎么能产生并存进去
create function rand_birth(n int)
returns char(8)
begin
declare year char(5);
declare month char(2);
declare day char(2);
declare return_birth char(8);
declare i int default '0'; while i < n do
set year = floor(1997+rand()*32);
set month = round(rand(1,12));
if month = 2 then
set day = round(rand(1,28));
else if month%2 = 0 and month != 2 then
set day = round(rand(1,30));
else
set day = round(rand(1,31));
end if;
set return_birth = concat(year,month,day);
set i = i + 1;
end while;
return return_birth;
end
$$
~~~~这是我写的function~~~~~~大神帮我看哈
原因:1、declare i int default '0';错误declare i int default 0;不是字符0
2、declare return_birth char(8);return_birth是拼接的所以即使是8最好写为拼接的所有长度为9
3、rand(1,12)没有这种写法;rand()*11+1因为rand()是在0-1之间
3、else if 在mysql中没有是ELSEIF 连写的
4、return return_birth;只能返回字符串为8长度的字符,所以不能的得到很多条生日日期
我将你的函数修改了下
是改为过程
DELIMITER $$
begin
declare year1 char(4);
declare month1 char(2);
declare day1 char(2);
declare return_birth char(10);
declare i int default 0; while i < n do
set year1 = floor(1997+RAND()*32);
set month1 = round(RAND()*12);
if month1 = 2 then
set day1 = round(RAND()*27+1);
ELSEIF month1%2 = 0 and month1 != 2 then
set day1 = round(RAND()*29+1);
else
set day1 = round(RAND()*30+1);
end if;
set return_birth = concat(year1,"-",month1,"-",day1);
insert into random_table(birth) values (str_to_date(return_birth,'%Y-%m-%d'));
set i = i + 1;
END while;
end
$$
DELIMITER ;
这是建表语句
CREATE TABLE `random_table` (
`bewin_id` TINYINT(3) NOT NULL AUTO_INCREMENT COMMENT '自增Id',
`birth` DATE NULL DEFAULT NULL COMMENT '生日时间',
PRIMARY KEY (`bewin_id`)
)
COMMENT='随机生日表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
这个str_to_date(return_birth,'%Y-%m-%d')函数是将字符串类型转换为date执行过程CALL `rand_birth`('5')
结果