在设计图书书店数据库的customers客户表时,记录一些花了时间的成果。
首先创建客户表
drop table if exists customers;create table customers(customerid BIGINT(20)UNSIGNED NOT NULL AUTO_INCREMENT,customernamevarchar(50) not null COMMENT'用户昵称',cpasswordvarchar(100) not null COMMENT '用户密码',crealnamevarchar(20) DEFAULT '' COMMENT '真实姓名',customersexTINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0男; 1女）',customerbirthdate datenot null COMMENT'用户生日',cphonevarchar(20) DEFAULT'' COMMENT'手机号',cemailvarchar(50) NOT NULL COMMENT'用户邮箱',caddressvarchar(255) NOT NULL COMMENT'用户地址',customerlevel int DEFAULT 1 COMMENT'客户等级',create_time DATETIME DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (customerid)#,constraint PK_TB_customers primary key(CustomerId, CustomerName));
/****************************************************函数**********************************************************/
随机生成密码
DROP FUNCTION if exists f1;delimiter $$CREATE FUNCTION f1() RETURNS varchar(50) DETERMINISTICBEGINDECLARE chars_str varchar(100) DEFAULT '#abcdefghi&jklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!*';DECLARE return_str varchar(255) DEFAULT '';DECLARE i INT DEFAULT 0;DECLARE n INT DEFAULT 10;WHILE i<n DOSET return_str =concat(return_str,substring(chars_str,FLOOR(1+RAND()*62),1));SET i=i+1;END WHILE;RETURN return_str;END $$delimiter ;#SELECT f1();
随机生成邮箱
DROP FUNCTION if exists generate_email;delimiter $$CREATE FUNCTION `generate_email`( ) RETURNS char(100) CHARSET utf8 DETERMINISTICBEGINDECLARE chars_str varCHAR(100) DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';DECLARE emailType VARCHAR(100) DEFAULT '@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@';DECLARE return_str varchar(255) DEFAULT '';DECLARE i INT DEFAULT 0;DECLARE n INT DEFAULT 6;WHILE i<n DOSET return_str =concat(return_str,substring(chars_str,FLOOR(1+RAND()*62),1));SET i=i+1;END WHILE;RETURN concat(return_str,SUBSTRING_INDEX(SUBSTRING_INDEX(emailType,',',FLOOR(1+RAND()*19)),',',-1));END $$delimiter ;#SELECT generate_email();
创建随机生成手机号函数 generatePhone
DROP FUNCTION if exists generatePhone;delimiter $$CREATE FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8 DETERMINISTICBEGINDECLARE head VARCHAR(200) DEFAULT '134,135,136,137,138,139,147,150,151,152,157,158,159,182,183,184,187,188,130,131,132,145,155,156,185,186,199,133,153,180,181,189';DECLARE content CHAR(10) DEFAULT '0123456789'; DECLARE phone CHAR(11) DEFAULT substring(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3); DECLARE i int DEFAULT 1;DECLARE len int DEFAULT LENGTH(content);WHILE i<9 DOSET i=i+1;SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * len), 1));END WHILE;RETURN phone;end$$delimiter ;-- 查询随机手机号 select generatePhone();
随机生成地址函数
DROP FUNCTION if exists generate_address;delimiter $$CREATE FUNCTION `generate_address`( ) RETURNS varchar(250) DETERMINISTICBEGINDECLARE province varCHAR(100) DEFAULT '';DECLARE city varCHAR(100) DEFAULT '';declare county varchar(100) default '';DECLARE street varCHAR(100) DEFAULT '';DECLARE return_str varchar(255) DEFAULT '';DECLARE i INT DEFAULT 0;DECLARE n INT DEFAULT 6;declare area varchar(255) DEFAULT '';declare area1 varchar(255) DEFAULT '';declare area2 varchar(255) DEFAULT '';declare area3 varchar(255) DEFAULT '';declare ances varchar(255);DECLARE address varchar(255) DEFAULT '';declare parent varchar(255) DEFAULT '';declare addresslevel int;set area=(select areaid from kms.city where level=1 order by rand() limit 1);set province=(select areaname from kms.city where areaid=area);set addresslevel=(select level from kms.city where areaid=area);if (addresslevel=1) then#set ances=(select ancestor from kms.city where areaname=address); set ppid=SUBSTRING_INDEX(SUBSTRING_INDEX(ances,'#',FLOOR(1+RAND()*3)),'#',-1);set area1=(select areaid from kms.city where parentnodeid=area order by rand() limit 1);set city=(select areaname from kms.city where areaid=area1);set area2=(select areaid from kms.city where parentnodeid=area1 order by rand() limit 1);set county=(select areaname from kms.city where areaid=area2);set area3=(select areaid from kms.city where parentnodeid=area2 order by rand() limit 1);set street=(select areaname from kms.city where areaid=area3);end if;if length(concat(return_str,province,city,county,street))>0 thenRETURN concat(return_str,province,city,county,street,floor(1+rand()*300),'号');end if;END $$delimiter ;select generate_address();
####################模拟随机生成customers表#############################
DROP FUNCTION if exists fn2;delimiter $$CREATE FUNCTION fn2() RETURNS int DETERMINISTICBEGINDECLARE $num INT DEFAULT 40; ###一下子执行200条,generate_address()函数无返回,多次少数执行DECLARE $i INT DEFAULT 0;DECLARE rand_surname TEXT DEFAULT /* 共有504个姓氏，564个字。*/'赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卡齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄魏家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲台从鄂索咸籍赖卓蔺屠蒙池乔阴郁胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍却璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴翟阎充慕连茹习宦艾鱼容向古易慎戈廖庚终暨居衡步都耿满弘匡国文寇广禄阙东殴殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逯盖后桓公万俟司马上官欧阳夏侯诸葛闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐钟离宇文长孙慕容鲜于闾丘司徒司空亓官司寇仉督子车颛孙端木巫马公西漆雕乐正壤驷公良拓拔夹谷宰父谷粱晋楚闫法汝鄢涂钦段干百里东郭南门呼延归海羊舌微生岳帅缑亢况后有琴梁丘左丘东门西门商牟佘佴伯赏南宫墨哈谯笪年爱阳佟第五言福';DECLARE rand_name TEXT DEFAULT /*名的随机范围401个常用汉字。*/'秀娟英梦华慧巧美娜静淑惠珠翠雅芝玉萍红娥雪玲芬月艳佳雯玥柔昭珍娣格叶桂贞芳燕黛彩菊兰洁春凤云荣素冰霞晨莲梅琳珊妍茜秋青倩锦婉娴莹芙真环爱娅荷菲心妹香莺媛瑞凡嘉琼勤莉璧璐楠琦晶莎婷姣瑾颖露瑶怡婵雁蓓纨仪丹蓉眉君琴蕊林滢寒薇菁岚苑婕馨瑗琰韵融园艺彦咏卿聪澜宁敏花霄蕾紫初阳纯函欣枫沛碧呈荔枝漫家彤妮丽思婧影诗祥毓姿悦爽琬宜姬采羽茗舒希橘萱杉克飘帛文馥竹曦灵筠霭凝晓芸琛欢亚伊帆蔚茹冬曼桃彬优弦琪菡歆璇鹤涵昕钰鑫桐鸿俊栀柏璟正旭依梓凌雨甜恬清加一诚晨信振泽斌骞初然裕潍震运安畅驰骏材天谷延弘烁铭濡贤腾龙宇强涛帝胤鸿辰钊嘉谛华年柔凯家康星海礼允良权锋坤福奇博俊皓祥锐起成韦楷休栋颜荣枫国柏郁尧盛暄逸祯锟槐祜树林升杰禧鹏鑫琛卓哲恒轩邦文梓爵喆彬翰帆澄芃睿子晓杞翱锦桀日浩寅佑伟阳平鹤晖峰吉刚勇毅军保东力兴义辉明永健世广志山仁波宁贵生元全胜学才发武新利清飞富顺昌光达岩中茂进有坚和彪先敬壮会思群豪心承乐绍功松善厚庆磊民友河江超亮政谦亨固之轮朗伯宏言若鸣朋梁维启克伦翔旭士以建致炎德行时泰雄钧冠策楠榕风航';DECLARE surname varchar(2) ; /*姓*/ DECLARE name1 CHAR(1) ; DECLARE name2 VARCHAR(1) ;DECLARE name VARCHAR(14) ; /*名*/DECLARE rand bigint(4);WHILE $i<$num DOselect FLOOR(1 +RAND()*564) into rand;if(rand<=444) then SELECT SUBSTRING(rand_surname,FLOOR(1 +RAND()*444),1) INTO surname; #姓elseif(rand>444 and rand%2=1) then SELECT SUBSTRING(rand_surname,rand,2) INTO surname; end if;IF (FLOOR(RAND()*2)+1=1 and char_length(surname)=1) THENSELECT SUBSTRING(rand_name,FLOOR(RAND()*445 +1),1) INTO name1; SELECT name1 INTO name; #名ELSESELECT SUBSTRING(rand_name,FLOOR(RAND()*445 +1),1) INTO name1;SELECT SUBSTRING(rand_name,FLOOR(RAND()*445 +1),1) INTO name2; #名SELECT CONCAT(name1,name2) INTO name;#名END IF;if(char_length(name)>=1) then INSERT INTO customers(customername,cpassword,customersex,customerbirthdate,cphone,cemail,caddress,customerlevel,create_time)VALUES (CONCAT(surname,name),f1(),FLOOR(RAND()*2),from_unixtime(unix_timestamp('1900-01-01')+floor(rand()*(unix_timestamp('-03-18')-unix_timestamp('1900-01-01')+1)),'%Y-%m-%d'),generatePhone(),generate_email(),generate_address(),floor(1+rand()*100),from_unixtime(unix_timestamp('-01-01')+floor(rand()*(current_date()-unix_timestamp('-01-01')+1)),'%Y-%m-%d %h:%i:%s')); #,SET $i=$i+1;end if;END WHILE;RETURN $i;END $$delimiter ;SELECT fn2(); -- 执行此函数 生成数据select * from customers;
MySQL随机生成customers表姓名 密码 性别 出生日期 电话 邮箱 收货地址 客户等级创建账号时间(function函数)
