批量插入脚本的使用示范

有时候,我们在造数据的时候, 需要使用sql 进行批量插入数据. 如果直接使用jmeter 发送接口请求来造数据, 会更加慢. 那么mysql的批量插入脚本怎么写呢?

需求， 我们给某两个相互有联系的表进行批量数据插入

思路就是先建表,然后创建两个方法用来生成随机字符串和指定范围的随机数.

建表

create table  country (
`id` Int PRIMARY key  auto_increment ,
cname  varchar(250),
size float 

);

create table  prince (
`id` Int PRIMARY key  auto_increment ,
countryId int ,
pname varchar(100),

CONSTRAINT fk_prince_country FOREIGN key (countryId) REFERENCES country(`id`)  

);

设置全局变量 log_bin_trust_function_creators = 1 避免批量插入脚本报错(如果开启过慢查询日志等二进制日志的话是必须这样做的)

set global log_bin_trust_function_creators = 1;
show VARIABLES like ‘log_bin_trust_function_creators’;

新建方法,生成随机字符串和固定范围的随机数

向下取整
select  FLOOR(1 + rand()*52 );
-- 
delimiter $
create FUNCTION get_string_random  ( n int  )  returns varchar(255) 
begin 
declare  char_string varchar(100) DEFAULT 'abcdefhijklimnopqrstuvwsyzABCDEFHIJKLIMNOPQRSTUVWSYZ';
declare return_str varchar(255) DEFAULT '' ;
declare i int DEFAULT 0 ;
while i < n Do 
set return_str = CONCAT( return_str, SUBSTRING( char_string, FLOOR(1 + rand()*52), 1 ));
set i= i + 1; 
end while;
return  return_str;

end $

-- 随机号从100 号开始
delimiter $
create FUNCTION rand_num () returns int (5)
begin 
declare i int DEFAULT 0 ;
set i = floor(100 +  RAND() * 10);
return i ;
end $

select  get_string_random(50);
select rand_num();

创建存储过程来插入两个表的数据

这里有用到了REPEAT 做循环,以及关掉了自动提交, 最后做整体的提交(类似事务,但不需要开启事务)

--  两个坑点， commit 提交是在循环repeat 后面， 不然就是一次插入提交一次， 数量大的时候，会造成性能影响的
-- 第二个坑点 UNTIL i = max_num  跳出循环条件后不能写 ； 等符号
delimiter $
create PROCEDURE insert_prince(in start_index int(10), in max_num int(10)) 
begin 
declare i int DEFAULT 0;
set autocommit = 0;
REPEAT
	set i = i+1 ;
	insert into prince(id, countryId, pname) values ( (start_index + i) ,rand_num(), get_string_random(6) );
UNTIL i = max_num 
END REPEAT;
commit ;
end $

-- 
delimiter $
create PROCEDURE insert_country(in  start_index int(10), in size_num int (10))
begin 
declare i int DEFAULT 0 ;
set autocommit = 0 ;
REPEAT
	set i = i+ 1;
	insert into country(id, cname , size) values ( (start_index+i ), get_string_random(10), null );
UNTIL i =  size_num
END REPEAT;
commit;
end $

调用的时候, 要注意外键的影响

call  insert_country(49, 200);
-- -- delete from country ;
-- select * from country; 
--  这里一定要提前把国家的  country 表的id 弄到超过200， 不然会外键报错
call insert_prince(0,5);

select * from prince;