MySQL如何利用存储过程快速生成100万条数据详解
时间:2022-05-30 13:45:45|栏目:MySQL|点击: 次
前言
在测试的时候为了测试大数据量的情况下项目的抗压能力我们通常要创造一些测试数据那么现在这个方法绝对好用
其中可能会有sql空间的报错可以自己尝试解决,这里做了分批插入,每次插入30万条,所以没有遇到类似的空间问题
首先,创建要插入100万数据的表格
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for sdb_b2c_orders -- ---------------------------- DROP TABLE IF EXISTS `sdb_b2c_orders`; CREATE TABLE `sdb_b2c_orders` ( `order_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '订单号', `seller_order_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '商户订单号', `total_amount` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '商品默认货币总值', `final_amount` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '订单货币总值, 包含支付价格,税等', `pay_status` enum('0','1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '付款状态 0-未支付 1-已支付 2-已付款至担保方 3-部分付款 4-部分退款 5-全额退款', `ship_status` enum('0','1','2','3','4') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '发货状态', `is_delivery` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y' COMMENT '是否需要发货', `createtime` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '下单时间', `last_modified` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '最后更新时间', `ectools_payments` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `payment` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付方式', `shipping_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT '配送方式', `shipping` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '配送方式', `member_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT '会员用户名', `platform_member_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '平台会员ID', `store_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT '收款门店ID', `confirm_status` enum('0','1','2','3') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '门店接单状态', `confirm_time` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '接单时间', `pickself_status` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自提状态', `pickself_time` int(10) NULL DEFAULT NULL COMMENT '自提时间', `pickself_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT '自提门店ID', `operator_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT '收款用户ID', `weixinscan_qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '微信付款码', `alipay_qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付宝付款码', `unionpay_qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '银联付款码', `qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '核销码', `promotion_type` enum('normal','prepare','recharge','cashier') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'normal' COMMENT '销售类型', `status` enum('active','dead','finish') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'active' COMMENT '订单状态', `confirm` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N' COMMENT '确认状态', `ship_area` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货地区', `ship_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货人', `weight` decimal(20, 3) NULL DEFAULT NULL COMMENT '订单总重量', `tostr` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '订单文字描述', `itemnum` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT '订单子订单数量', `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'IP地址', `ship_addr` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '收货地址', `ship_zip` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货人邮编', `ship_tel` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货电话', `ship_email` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货人email', `ship_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '配送时间', `ship_mobile` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收货人手机', `cost_item` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '订单商品总价格', `is_tax` enum('true','false') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'false' COMMENT '是否要开发票', `tax_type` enum('false','personal','company') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'false' COMMENT '发票类型', `tax_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票内容', `cost_tax` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '订单税率', `tax_company` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票抬头', `is_protect` enum('true','false') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'false' COMMENT '是否还有保价费', `cost_protect` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '保价费', `cost_payment` decimal(20, 3) NULL DEFAULT NULL COMMENT '支付费用', `currency` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单支付货币', `cur_rate` decimal(10, 4) NULL DEFAULT 1.0000 COMMENT '订单支付货币汇率', `score_u` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '订单使用积分', `score_g` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '订单获得积分', `discount` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '订单减免', `pmt_goods` decimal(20, 3) NULL DEFAULT NULL COMMENT '商品促销优惠', `pmt_order` decimal(20, 3) NULL DEFAULT NULL COMMENT '订单促销优惠', `payed` decimal(20, 3) NULL DEFAULT 0.000 COMMENT '订单支付金额', `memo` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '订单附言', `disabled` enum('true','false') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'false', `displayonsite` enum('true','false') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'true', `mark_type` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'b1' COMMENT '订单备注图标', `mark_text` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '订单备注', `cost_freight` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT '配送费用', `extend` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'false' COMMENT '订单扩展', `order_refer` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'local' COMMENT '订单来源', `addon` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '订单附属信息(序列化)', `source` enum('pc','wap','weixin','cashier','paycode','eleme','meituan') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'pc' COMMENT '平台来源', `source_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商圈库名', `merchant_bn` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商户号', UNIQUE INDEX `idx_bn`(`order_id`, `merchant_bn`) USING BTREE, INDEX `ind_ship_status`(`ship_status`) USING BTREE, INDEX `ind_pay_status`(`pay_status`) USING BTREE, INDEX `ind_promotion_type`(`promotion_type`) USING BTREE, INDEX `ind_status`(`status`) USING BTREE, INDEX `ind_disabled`(`disabled`) USING BTREE, INDEX `ind_last_modified`(`last_modified`) USING BTREE, INDEX `ind_createtime`(`createtime`) USING BTREE, INDEX `idx_order_refer`(`order_refer`) USING BTREE, INDEX `idx_bn_aa`(`merchant_bn`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
上一篇:MySQL 外键(FOREIGN KEY)用法案例详解
栏 目:MySQL
本文标题:MySQL如何利用存储过程快速生成100万条数据详解
本文地址:https://idc91.com/shujuku/3409.html
您可能感兴趣的文章
- 05-31MySQL中的 inner join 和 left join的区别解析(小结果集驱动大结果集)
- 05-31MySQL索引失效十种场景与优化方案
- 05-31MYSQL 高级文本查询之regexp_like和REGEXP详解
- 05-31MySQL获取binlog的开始时间和结束时间(最新方法)
- 05-31MySQL索引查询的具体使用
- 05-31基于MySQL和Redis扣减库存的实践
- 05-31关于MySQL的存储过程与存储函数
- 05-31MySQL实战文章(非常全的基础入门类教程)
- 05-31MySQL Flink Watermark实现事件时间处理的关键技术
- 05-31MySQL Flink实时流处理的核心技术之窗口机制
阅读排行
推荐教程
- 05-30Navicat for MySQL 11注册码激活码汇总
- 05-27Mysql误删数据快速恢复
- 05-31VS2022连接数据库MySQL并进行基本的表的操作指南
- 05-30解决seata不能使用mysql8版本的问题方法
- 05-30MYSQL字符集设置的方法详解(终端的字符集)
- 05-30解决MySQL启动报错:ERROR 2003 (HY000): Can't con
- 05-30关于Mysql-connector-java驱动版本问题总结
- 11-22mac下安装mysql忘记密码的修改方法
- 05-30MySQL中的隐藏列的具体查看
- 11-22mysql exists与not exists实例详解