简单订单表模拟批量写入

CREATE TABLE `t_customer` (
  `customer_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '会员id',
  `customer_name` varchar(30) NOT NULL COMMENT '会员名称',
  `customer_phone` char(11) NOT NULL COMMENT '会员电话',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员详情表';
CREATE TABLE `t_order` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `code` varchar(200) NOT NULL COMMENT '流水号',
  `type` tinyint unsigned NOT NULL COMMENT '订单类型:1实体销售,2网络销售',
  `shop_id` int unsigned DEFAULT NULL COMMENT '零售店id',
  `customer_id` int unsigned DEFAULT NULL COMMENT '会员id',
  `amount` decimal(10,2) unsigned NOT NULL COMMENT '总金额',
  `payment_type` tinyint unsigned NOT NULL COMMENT '支付方式:1借记卡,信用卡,3微信,4支付宝,5现金',
  `status` tinyint unsigned NOT NULL COMMENT '状态:1未付款,2已付款,3已发货,4已签收',
  `postage` decimal(10,2) unsigned DEFAULT NULL COMMENT '邮费',
  `weight` int unsigned DEFAULT NULL COMMENT '重量(克)',
  `voucher_id` int unsigned DEFAULT NULL COMMENT '购物券id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=90000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';
CREATE TABLE `t_shopping` (
  `shop_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '零售店id',
  `shop_name` varchar(30) NOT NULL COMMENT '商店名称',
  `shop_addr` varchar(50) NOT NULL COMMENT '商店地址',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商店详情表';

DELIMITER $$
CREATE PROCEDURE `insert_t_customer`(in putcount int)
BEGIN
DECLARE firstname VARCHAR(1500);
DECLARE  lastname VARCHAR(3500);
DECLARE  firstlastname VARCHAR(20);
DECLARE  phone char(11);
declare storename VARCHAR(20);
declare storename2 VARCHAR(20);

DECLARE param int default 0;
set firstname='赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄曲家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴鬱胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍郤璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾毋沙乜养鞠须丰钟离宇文长孙慕容鲜于闾丘司徒司空丌官司寇仉督子车颛孙端木巫马公西漆雕乐正壤驷公良拓跋夹谷宰父谷梁晋楚闫法汝鄢涂钦段干百里东郭南门呼延归海羊舌微生岳帅缑亢况郈有琴梁丘左丘东门西门商牟佘佴伯赏南宫墨哈谯笪年爱阳佟';
set  lastname='天地玄黄宇宙洪荒日月盈昃辰宿列张寒来暑往秋收冬藏闰余成岁律吕调阳云腾致雨露结为霜金生丽水玉出昆冈剑号巨阙珠称夜光果珍李柰菜重芥姜海咸河淡鳞潜羽翔龙师火帝鸟官人皇始制文字乃服衣裳推位让国有虞陶唐吊民伐罪周发殷汤坐朝问道垂拱平章爱育黎首臣伏戎羌遐迩一体率宾归王鸣凤在竹白驹食场化被草木赖及万方盖此身发四大五常恭惟鞠养岂敢毁伤女慕贞洁男效才良知过必改得能莫忘罔谈彼短靡恃己长信使可覆器欲难量墨悲丝染诗赞羔羊景行维贤克念作圣德建名立形端表正空谷传声虚堂习听祸因恶积福缘善庆尺璧非宝寸阴是竞资父事君曰严与敬孝当竭力忠则尽命临深履薄夙兴温凊似兰斯馨如松之盛川流不息渊澄取映容止若思言辞安定笃初诚美慎终宜令荣业所基籍甚无竟学优登仕摄职从政存以甘棠去而益咏乐殊贵贱礼别尊卑上和下睦夫唱妇随外受傅训入奉母仪诸姑伯叔犹子比儿孔怀兄弟同气连枝交友投分切磨箴规仁慈隐恻造次弗离节义廉退颠沛匪亏性静情逸心动神疲守真志满逐物意移坚持雅操好爵自縻都邑华夏东西二京背邙面洛浮渭据泾宫殿盘郁楼观飞惊图写禽兽画彩仙灵丙舍旁启甲帐对楹肆筵设席鼓瑟吹笙升阶纳陛弁转疑星右通广内左达承明既集坟典亦聚群英杜稿钟隶漆书壁经府罗将相路侠槐卿户封八县家给千兵高冠陪辇驱毂振缨世禄侈富车驾肥轻策功茂实勒碑刻铭磻溪伊尹佐时阿衡奄宅曲阜微旦孰营桓公匡合济弱扶倾绮回汉惠说感武丁俊义密勿多士实宁晋楚更霸赵魏困横假途灭虢践土会盟何遵约法韩弊烦刑起翦颇牧用军最精宣威沙漠驰誉丹青九州禹迹百郡秦并岳宗泰岱禅主云亭雁门紫塞鸡田赤城昆池碣石钜野洞庭旷远绵邈岩岫杳冥治本于农务兹稼穑俶载南亩我艺黍稷税熟贡新劝赏黜陟孟轲敦素史鱼秉直庶几中庸劳谦谨敕聆音察理鉴貌辨色贻厥嘉猷勉其祗植省躬讥诫宠增抗极殆辱近耻林皋幸即两疏见机解组谁逼索居闲处沉默寂寥求古寻论散虑逍遥欣奏累遣戚谢欢招渠荷的历园莽抽条枇杷晚翠梧桐蚤凋陈根委翳落叶飘摇游鹍独运凌摩绛霄耽读玩市寓目囊箱易輶攸畏属耳垣墙具膳餐饭适口充肠饱饫烹宰饥厌糟糠亲戚故旧老少异粮妾御绩纺侍巾帷房纨扇圆洁银烛炜煌昼眠夕寐蓝笋象床弦歌酒宴接杯举觞矫手顿足悦豫且康嫡后嗣续祭祀烝尝稽颡再拜悚惧恐惶笺牒简要顾答审详骸垢想浴执热愿凉驴骡犊特骇跃超骧诛斩贼盗捕获叛亡布射僚丸嵇琴阮啸恬笔伦纸钧巧任钓释纷利俗并皆佳妙毛施淑姿工颦妍笑年矢每催曦晖朗曜璇玑悬斡晦魄环照指薪修祜永绥吉劭矩步引领俯仰廊庙束带矜庄徘徊瞻眺孤陋寡闻愚蒙等诮谓语助者焉哉乎也';

while param<putcount do
set storename=SUBSTR(firstname, FLOOR(RAND() * LENGTH(firstname))/3, 1) ;
set storename2 = CONCAT( storename, SUBSTR(lastname, FLOOR(RAND() * LENGTH(lastname))/3, 1) ) ;
set firstlastname = CONCAT( storename2, SUBSTR(lastname, FLOOR(RAND() * LENGTH(lastname))/3, 1) ) ;
set phone=CONCAT('13',CEILING(RAND()*900000000+100000000));
INSERT INTO zt_db.t_customer(`customer_id`,`customer_name`,`customer_phone`,`create_time`)VALUES(null,firstlastname,phone,null);
set param=param+1;
set storename='';
set storename2='';
set firstlastname='';
set phone='';
end while;
END$$
DELIMITER ;

DELIMITER $$
CREATE  PROCEDURE `insert_t_order`(in putcount int ,shop_id int,customer_id int)
BEGIN
DECLARE code2 VARCHAR(30);
DECLARE param int default 0;

while param<putcount do
set code2= CONCAT('TH',DATE_FORMAT(now(), '%Y%m%d%H%i%s'),lpad(round(round(rand(),4)*1000),4,'0'));

INSERT INTO `zt_db`.`t_order`
(`id`,`code`,`type`,`shop_id`,`customer_id`,`amount`,`payment_type`,`status`,`postage`,`weight`,`voucher_id`,`create_time`)
VALUES
(null,code2,FLOOR(RAND() *2+1),FLOOR(RAND() *shop_id+1),FLOOR(RAND() *customer_id+1),FLOOR(RAND() *100+100),FLOOR(RAND() *5+1),
FLOOR(RAND() *4+1),FLOOR(RAND() *10+10),FLOOR(RAND() *10+1)*100,0,null);

set param=param+1;
end while;
END$$
DELIMITER ;

DELIMITER $$
CREATE  PROCEDURE `insert_t_shopping`(in putcount int)
begin
DECLARE addrname VARCHAR(48);
DECLARE  yanaddr VARCHAR(20);
DECLARE  newaddrname VARCHAR(20);
DECLARE  sumaddrname VARCHAR(20);
declare storename VARCHAR(20);
DECLARE param int default 0;
set addrname='西广南北长新语雅递四方天海特和湾';
set yanaddr='深圳';
while param<putcount do
set storename=CONCAT( '天虹', SUBSTR(addrname, FLOOR(RAND() * LENGTH(addrname))/3, 1) ) ;
set newaddrname = CONCAT( yanaddr, SUBSTR(addrname, FLOOR(RAND() * LENGTH(addrname))/3, 1) ) ;
set sumaddrname = CONCAT( newaddrname, SUBSTR(addrname, FLOOR(RAND() * LENGTH(addrname))/3, 1) ) ;
insert into  zt_db.t_shopping values(null, storename,sumaddrname,null);
set param=param+1;
set newaddrname='';
set sumaddrname='';
end while;
end$$
DELIMITER ;

批量写入
call insert_t_shopping(100); 写入100个随机商店
call insert_t_customer(10000);写入10000个随机客户信息
call insert_t_order(10000000,100,10000); 写入10000000 个订单信息,100个随机商店,10000个随机客户