/**随机姓名**/
drop function if exists generateusername;
delimiter //
create function generateusername() returns varchar(255) charset utf8
deterministic
begin
declare xing varchar(2056) default '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁锺徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄麴家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫';
declare ming varchar(2056) default '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';
declare i_xing int default length(xing) / 3;
declare i_ming int default length(ming) / 3;
declare return_str varchar(2056) default '';
set return_str = concat(return_str, substring(xing, floor(1 + rand() * i_xing), 1));
set return_str = concat(return_str, substring(ming, floor(1 + rand() * i_ming), 1));
if rand() > 0.400 then
set return_str = concat(return_str, substring(ming, floor(1 + rand() * i_ming), 1));
end if;
return return_str;
end//
delimiter ;
/**随机电话**/
drop function if exists generatephone;
delimiter //
create function generatephone() returns char(11) charset utf8
deterministic
begin
declare head varchar(100) default '000,156,136,176';
declare content char(10) default '0123456789';
declare phone char(11) default substring(head, 1+(floor(1 + (rand() * 3))*4), 3);
#set phone = concat(phone, substring('156,136,123,456,789', 1+(floor(1 + (rand() * 4))*4), 3));
declare i int default 1;
declare len int default length(content);
while i<9 do
set i=i+1;
set phone = concat(phone, substring(content, floor(1 + rand() * len), 1));
end while;
return phone;
end//
delimiter ;
drop function if exists randomdatetime;
delimiter //
create function `randomdatetime`() returns datetime
begin
set @sd=date_format('1995-01-01 00:00:00','%y-%m-%d %h:%i:%s');
set @ed=date_format('2003-12-31 23:59:59','%y-%m-%d %h:%i:%s');
return date_add(@sd,interval floor(1+rand()*((abs(unix_timestamp(@ed)-unix_timestamp(@sd)))-1)) second);
end//
delimiter ;
drop procedure if exists proc1;
delimiter //
set autocommit = 0 //
create procedure proc1(num int)
begin
declare v_cnt decimal (10) default 0 ;
dd:loop
insert into arbitrary_data(name,sex,age,cellphone,time) values (generateusername(),round(1+rand()),round(15+rand()*10), generatephone(),`randomdatetime`());
commit;
set v_cnt = v_cnt+1 ;
if v_cnt = num then leave dd; /** num为条数**/
end if;
end loop dd ;
end;//
delimiter ;
CREATE TABLE `arbitrary_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
sex ENUM('男','女') NOT NULL DEFAULT '男',
`age` int(11) DEFAULT NULL,
`cellphone` varchar(13) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;