MySQL通过函数存储过程批量插入

1. 批量插入数据概述

在日常开发过程中有些场景需要批量造数进行测试，本文就是通过MySql数据库函数与存储过程实现批量插入数据，当然也可以通过代码循环插入数据，亦或是通过其他工具实现，这里要实现的是直接操作数据库，通过测试发现直接通过数据库效率更高，更方便。

2. 批量插入数据实现

2.1 创建测试表

首先创建测试表，员工表与部门表。

-- 1.1 创建表
-- 创建部门表
CREATE TABLE `dept` (
	`id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
	`code` VARCHAR ( 32 ) NOT NULL COMMENT '部门编码',
	`name` VARCHAR ( 30 ) NOT NULL COMMENT '部门名称',
	`address` VARCHAR ( 60 ) DEFAULT NULL COMMENT '部门地址',
	`ceo` INT NULL COMMENT '随机数',
	PRIMARY KEY ( `id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='部门表';

-- 创建员工表
CREATE TABLE `emp` (
	`id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
	`code` VARCHAR ( 32 ) NOT NULL COMMENT '员工编码',
	`name` VARCHAR ( 30 ) NOT NULL COMMENT '员工名称',
	`age` INT ( 3 ) NOT NULL COMMENT '员工年龄',
	`dept_id` INT ( 11 ) NOT NULL COMMENT '所属部门',
	PRIMARY KEY ( `id` ) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='员工表';

2.2 设置MySQL参数

在执行创建函数之前， 首先请保证log_bin_trust_function_creators 参数为 1， 即 on 开启状态。 否则会报错。

-- 1.2 设置参数
-- 在执行创建函数之前， 首先请保证 log_bin_trust_function_creators 参数为 1， 即 on 开启状态。
-- 否则会报错：
-- 查询： 
show variables like 'log_bin_trust_function_creators';
-- 设置： 
set global log_bin_trust_function_creators=1;

-- 当然， 如上设置只存在于当前操作， 想要永久生效， 需要写入到配置文件中：
-- 在[mysqld]中加上 log_bin_trust_function_creators=1

2.3 编写函数与存储过程

编写随机字符串函数与随机数函数，以及插入存储过程。

-- 1.3 编写随机函数
-- 1.3.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 $$

-- 如果要删除函数， 则执行： 
drop function rand_string;

-- 1.3.2 随机产生部门编号
#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$

-- 如果要删除函数： 
drop function rand_num;

-- 1.4 创建存储过程
-- 1.4.1 创建往 emp 表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO `emp`(`code`, `name`, `age`, `dept_id`) VALUES ((START+i) ,rand_string(6) ,rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

#删除函数 insert_emp
drop PROCEDURE insert_emp;

-- 1.4.2 创建往 dept 表中插入数据的存储过程
#执行存储过程， 往 dept 表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO `dept`(`code`, `name`, `address`, `ceo`) VALUES (rand_string(8),'部门',rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

#删除函数 insert_dept
drop PROCEDURE insert_dept;

2.4 实现批量插入

调用存储过程批量插入数据。

-- 1.5 调用存储过程
-- 1.5.1 添加数据到部门表
#执行存储过程， 往 dept 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(100);

-- 1.5.2 添加数据到员工表
#执行存储过程， 往 emp 表添加 50 万条数据
DELIMITER ;
CALL insert_emp(1000,5000);

-- 查询验证
select * from dept;
select * from emp;

2.5 实现验证

id		code		name	address		ceo
1		tUradqtX	部门	JmsdlWYezj	243143
2		SSLbYNVm	部门	yITuqxqkeS	485344
3		pEanrUwz	部门	ismjkvxcVs	42754
4		szuymMaR	部门	ggoXZexbPU	48199
5		OFLNHYWL	部门	OOBoRttMEK	373347
6		JUJwncxg	部门	mRyRQBjriP	248685
...