1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
| delimiter //
DROP PROCEDURE IF EXISTS user_proc_batch_insert;
CREATE PROCEDURE user_proc_batch_insert(in max_num int)
BEGIN
DECLARE i int; -- 声明变量【循环index】
-- 声明变量(主键：雪花ID)
DECLARE user_id varchar(36);
-- 声明变量(推荐人用户ID)
DECLARE parent_user_id varchar(36);
-- 声明变量(昵称)
DECLARE nickname varchar(255);
-- 声明变量(手机号码)
DECLARE mobile varchar(11);
-- 声明变量(性别：1-男、2-女、3-其他)
DECLARE gender tinyint;
-- 声明变量(用户微信号)
DECLARE wechat_id varchar(100);
-- 声明变量(微信OpenID)
DECLARE wechat_openid varchar(50);
-- 声明变量(微信UnionID)
DECLARE wechat_unionid varchar(50);
-- 声明变量(用户头像)
DECLARE avatar varchar(255);
-- 声明变量(注册时间)
DECLARE register_time datetime;
-- 声明变量(注册IP地址)
DECLARE register_ip varchar(20);
-- 声明变量(生日)
DECLARE birthday varchar(20);
-- 声明变量(登录时间)
DECLARE login_time datetime;
-- set autocommit = 0; -- 禁止自动提交sql
SET i=1;
WHILE i < max_num DO
-- 循环index+1
SET i=i+1;
-- 赋值(主键：雪花ID)
SET user_id = REPLACE(UUID(),'-','');
-- SET parent_user_id = NULL;
-- 赋值(推荐人用户ID)[五分之一的几率为空]
IF round(rand()*(5-1)+1)=2 THEN
SET parent_user_id = NULL;
ELSE
SET parent_user_id = (SELECT t1.id FROM `shop_user` AS t1 JOIN (SELECT concat(conv(ROUND(RAND() * 281474976710655),10,16),'ffffffffffffffffffff') AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1);
UPDATE shop_user SET follow_num = follow_num + 1 WHERE id = parent_user_id;
END IF;
-- 赋值(昵称)
SET nickname = concat(
substring('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚',
floor(1+190*rand()),1),
substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',
floor(1+400*rand()),
1),
substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',
floor(1+400*rand())
,1)
);
-- 赋值(手机号码)
SET mobile = concat('1',substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1), right(left(trim(cast(rand() AS char(50))), 11), 9));
-- 赋值(性别：1-男、2-女、3-其他)
SET gender = round(rand()*(3-1)+1);
-- 赋值(用户微信号)[6-20位数字或字母]
SET wechat_id = SUBSTRING(REPLACE(UUID(),'-',''),1,round(rand()*(20-6)+6));
-- 赋值(微信OpenID)[28位数字或字母]
SET wechat_openid = SUBSTRING(REPLACE(UUID(),'-',''),1,28);
-- 赋值(微信OpenID)[28位数字或字母]
SET wechat_unionid = SUBSTRING(REPLACE(UUID(),'-',''),1,28);
-- 赋值(用户头像)
SET avatar = "goods/00e2df50f4824b6995f432d041777a72.jpg";
-- 赋值(注册时间)(2020-3-1向后60天)
SET register_time = DATE_ADD('2020-3-1 00:00:00', INTERVAL FLOOR(1 + (RAND() * 60*60*24*30*2)) SECOND);
-- 赋值(注册IP地址)
SET register_ip = concat(round(rand()*(254-0)+0),'.',round(rand()*(254-0)+0),'.',round(rand()*(254-0)+0),'.',round(rand()*(254-0)+0));
-- 赋值(生日)(1960-1-1向后80年)
SET birthday = DATE_ADD('1960-1-1 00:00:00', INTERVAL FLOOR(1 + (RAND() * 60*60*24*30*12*80)) SECOND);
-- 赋值(登录时间)(2020-5-2向后10天)
SET login_time = DATE_ADD('2020-5-2 00:00:00', INTERVAL FLOOR(1 + (RAND() * 60*60*24*10)) SECOND);
INSERT INTO shop_user(id,parent_user_id,nickname,mobile,gender,wechat_id,wechat_openid,wechat_unionid,avatar,register_time,register_ip,birthday,login_time)VALUES (user_id,parent_user_id,nickname,mobile,gender,wechat_id,wechat_openid,wechat_unionid,avatar,register_time,register_ip,birthday,login_time);
END WHILE;
-- commit; -- 待循环完毕后再进行提交，即整体提交整体的 sql，这样可以提高效率，否则循环里会一次一次的去提交sql,数据多了会很慢。
END//
delimiter ;
|
