How to use stored procedures in MySQL to quickly generate 1 million records

Preface

When testing, in order to test the project's ability to withstand pressure under large amounts of data, we usually need to create some test data. Now this method is absolutely useful.

There may be sql space errors, which you can try to solve yourself. Here, batch insertion is done, 300,000 records are inserted each time, so there is no similar space problem.

First, create a table to insert 1 million data

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 'Order number',
  `seller_order_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT 'Merchant order number',
  `total_amount` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Total value of product in default currency',
  `final_amount` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Total amount of order currency, including payment price, tax, etc.',
  `pay_status` enum('0','1','2','3','4','5') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT 'Payment status 0-Not paid 1-Paid 2-Paid to guarantor 3-Partial payment 4-Partial refund 5-Full refund',
  `ship_status` enum('0','1','2','3','4') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT 'Shipping status',
  `is_delivery` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y' COMMENT 'Does it need to be shipped?',
  `createtime` int(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Order time',
  `last_modified` int(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Last modified time',
  `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 'Payment method',
  `shipping_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT 'Shipping method',
  `shipping` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Shipping method',
  `member_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT 'Member user name',
  `platform_member_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Platform member ID',
  `store_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT 'Collection store ID',
  `confirm_status` enum('0','1','2','3') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT 'Store order status',
  `confirm_time` int(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Order acceptance time',
  `pickself_status` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'self-pickup status',
  `pickself_time` int(10) NULL DEFAULT NULL COMMENT 'Pickup time',
  `pickself_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT 'Pickup store ID',
  `operator_id` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT 'Payee user ID',
  `weixinscan_qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'WeChat payment code',
  `alipay_qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Alipay payment code',
  `unionpay_qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'UnionPay payment code',
  `qrcode` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Verification code',
  `promotion_type` enum('normal','prepare','recharge','cashier') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'normal' COMMENT 'Sales type',
  `status` enum('active','dead','finish') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'active' COMMENT 'Order status',
  `confirm` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N' COMMENT 'Confirmation status',
  `ship_area` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'shipping area',
  `ship_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Consignee',
  `weight` decimal(20, 3) NULL DEFAULT NULL COMMENT 'Total order weight',
  `tostr` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Order text description',
  `itemnum` mediumint(8) UNSIGNED NULL DEFAULT NULL COMMENT 'Order sub-order quantity',
  `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'IP address',
  `ship_addr` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Shipping address',
  `ship_zip` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Consignee's postal code',
  `ship_tel` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Shipping phone number',
  `ship_email` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Consignee email',
  `ship_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Shipping time',
  `ship_mobile` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Consignee's mobile phone number',
  `cost_item` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Total price of order items',
  `is_tax` enum('true','false') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'false' COMMENT 'Do you want to issue an invoice?',
  `tax_type` enum('false','personal','company') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'false' COMMENT 'Invoice type',
  `tax_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Invoice content',
  `cost_tax` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Order tax rate',
  `tax_company` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Invoice header',
  `is_protect` enum('true','false') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'false' COMMENT 'Is there any insurance fee?',
  `cost_protect` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Insurance fee',
  `cost_payment` decimal(20, 3) NULL DEFAULT NULL COMMENT 'Payment fee',
  `currency` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Order payment currency',
  `cur_rate` decimal(10, 4) NULL DEFAULT 1.0000 COMMENT 'Order payment currency exchange rate',
  `score_u` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Order usage points',
  `score_g` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Order points',
  `discount` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Order discount',
  `pmt_goods` decimal(20, 3) NULL DEFAULT NULL COMMENT 'Product promotion discount',
  `pmt_order` decimal(20, 3) NULL DEFAULT NULL COMMENT 'Order promotion',
  `payed` decimal(20, 3) NULL DEFAULT 0.000 COMMENT 'Order payment amount',
  `memo` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Order Notes',
  `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 'Order note icon',
  `mark_text` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Order Notes',
  `cost_freight` decimal(20, 3) NOT NULL DEFAULT 0.000 COMMENT 'Delivery cost',
  `extend` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'false' COMMENT 'Order extension',
  `order_refer` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'local' COMMENT 'Order source',
  `addon` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Order additional information (serialized)',
  `source` enum('pc','wap','weixin','cashier','paycode','eleme','meituan') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'pc' COMMENT 'Platform source',
  `source_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Business district library name',
  `merchant_bn` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Merchant number',
  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 = 'Order table' ROW_FORMAT = Dynamic;
 
SET FOREIGN_KEY_CHECKS = 1;

Creating a function

Generate random string

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 $$

Randomly generate numbers

DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
 
 
#If you want to delete #drop function rand_num;

Creating a stored procedure

And insert data into the table

#Execute the stored procedure to add random data to the sdb_b2c_order table 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 $$;
 
 
#delete#DELIMITER;
# drop PROCEDURE insert_emp;

Calling a stored procedure to insert data

 #Insert 300,000 records in several batches CALL insert_sdb_b2c_orders(700003,300000);

result

Summarize

One million data sets are done. This concludes this article on how to use stored procedures in MySQL to quickly generate one million data sets. For more information on how to use stored procedures in MySQL to generate one million data sets, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL spatial data storage and functions
Comparison of storage engines supported by MySQL database
Mysql uses stored procedures to quickly add millions of data sample code
In MySQL database, datetime, bigint, and timestamp are used to represent time selection. Which one is the most efficient for storing time?
MySQL Series 7 MySQL Storage Engine
MySQL series five views, stored functions, stored procedures, triggers
Detailed explanation of MYSQL stored procedure comments

<<: Html Select option How to make the default selection

>>: How to use vw+rem for mobile layout