MySQL如何利⽤存储过程快速⽣成100万条数据详解
前⾔
在测试的时候为了测试⼤数据量的情况下项⽬的抗压能⼒我们通常要创造⼀些测试数据那么现在这个⽅法绝对好⽤
其中可能会有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;
创建函数
随机产⽣字符串
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 $$
随机产⽣数字
DELIMITER $$
CREATE FUNCTION rand_num( )
RETURNS INT(5)mysql的存储过程怎么理解
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
#假如要删除
#drop function rand_num;
创建存储过程
并向表中插⼊数据
#执⾏存储过程，往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;
调⽤存储过程进⾏插⼊数据
#插⼊30万条数据分⼏次插⼊
CALL insert_sdb_b2c_orders(700003,300000);
结果
总结
百万数据搞定，到此这篇关于MySQL如何利⽤存储过程快速⽣成100万条数据的⽂章就介绍到这了，更多相关MySQL⽤存储过程⽣成100万数据内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家
以后多多⽀持！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论