MySQL如何利用存储过程快速生成100万条数据详解

吾爱主题阅读：111 2024-04-02 08:05:04 评论：0

前言

在测试的时候为了测试大数据量的情况下项目的抗压能力我们通常要创造一些测试数据那么现在这个方法绝对好用

其中可能会有sql空间的报错可以自己尝试解决，这里做了分批插入，每次插入30万条，所以没有遇到类似的空间问题

首先，创建要插入100万数据的表格

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 83 84 85 86 87 88 89 90 91 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;

创建函数

随机产生字符串

1 2 3 4 5 6 7 8 9 10 11 12 DELIMITER $$ CREATE FUNCTION rand_string(n INT ) RETURNS VARCHAR (255) BEGIN DECLARE chars_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ' ; DECLARE return_str VARCHAR (255) DEFAULT '' ; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str, SUBSTRING (chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END $$

随机产生数字

1 2 3 4 5 6 7 8 9 10 11 12 DELIMITER $$ CREATE FUNCTION rand_num( ) RETURNS INT (5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(100+RAND()*10); RETURN i; END $$ #假如要删除 # drop function rand_num;

创建存储过程

并向表中插入数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 #执行存储过程，往sdb_b2c_order表添加随机数据 DELIMITER $$ CREATE PROCEDURE insert_sdb_b2c_orders( IN START INT (10), IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO sdb_b2c_orders (order_id ,seller_order_id,total_amount,final_amount,source,source_name,merchant_bn ) VALUES (rand_string(10) ,rand_string(10),100,100, 'eleme' , 'square' ,(START+i)); UNTIL i = max_num END REPEAT; COMMIT ; END $$; #删除 # DELIMITER ; # drop PROCEDURE insert_emp;