mysql 利用sql语句生成多天测试数据

mysql 利用sql语句生成多天测试数据

1.通过存储过程实现添加测试数据：现有以下字段表
2.存储过程代码

#mysql 创建多条测试数据
DROP PROCEDURE IF EXISTS createTextDate;
DELIMITER $$
CREATE PROCEDURE createTextDate()
BEGIN
DECLARE x INT Default 0;
DECLARE id char(64);
DECLARE phone char(32);
DECLARE username char(32);
DECLARE password1 char(128);
DECLARE sex char(4);
DECLARE birthday char(64);
WHILE x < 10 DO
SET x = x+1;
set id = REPLACE(UUID(),"-","");
SET phone = concat('1',SUBSTRING_INDEX(SUBSTRING_INDEX('3,5,7,8',',',ceiling(rand()*10)%4+1),',',-1), CEILING(RAND()*900000000+100000000) );
set username = concat(substring('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚',floor(1+190*rand()),1),substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',floor(1+400*rand()),1),substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',floor(1+400*rand()),1));
set password1 = md5(ceiling(rand()*1000000));
set sex = SUBSTRING_INDEX(SUBSTRING_INDEX('男,女',',',ceiling(rand()*10)%2+1),',',-1);
set birthday = concat('2015-', 1+ceiling(rand()*100)%12,'-',1+ceiling(rand()*100)%28);
insert into user(id,phone,username,password,sex,birthday,update_time)
values(id,phone,username,password1,sex,birthday,NOW())
ON DUPLICATE KEY UPDATE
update_time=now();
END WHILE;
END $$

call createTextDate();

3.结果

4.分析

1. id通过uuid生成
2. md5()生成md5加密的密码
3. 通过concat()方法字符串拼接
4. SUBSTRING_INDEX(SUBSTRING_INDEX(‘3,5,7,8’,’,’,ceiling(rand()*10)%4+1),’,’,-1)将随机从3，5，7，8中选择一个数，使用类似方法对多个数进行随机选择
5. 1+ceiling(rand()*100)%28)：ceiling()方法取整数，rand()生成0到1随机小数