mysql批量新增千万级数据脚本（函数+存储过程）

 

1、函数

（1）、基本语法

  创建

delimiter 自定义符号　　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略

    create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns

　　 begin

　　　　函数体　　　　-- 函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用

　　　　返回值

　　 end

　　 自定义符号

delimiter ;

查看函数

show function status [like 'pattern'];　　-- 查看所有自定义函数, 自定义函数只能在本数据库使用。
show create function 函数名;　　-- 查看函数创建语句

删除函数

drop function 函数名;

2、存储过程

  存储过程语法

create procedure 过程名字(参数列表)

begin

　　-- SQL语句...

end

IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

仅需要将数据传入存储过程，并不需要返回计算后的该值。

只能当做传入参数
OUT 输出参数
该值可在存储过程内部被改变，并可返回

不接受外部传入的数据，仅返回计算之后的值。

只能当做转出参数
INOUT 输入输出参数
调用时指定，并且可被改变和返回

需要数据传入存储过程经过调用计算后，再传出返回值

可当做传入转出参数

  查看过程

-- 查看所有过程
show procedure status [like 'pattern'];
-- 查看过程创建语句
show create procedure 过程名字;

  过程调用

-- 过程调用
call 过程名字();

  过程删除

-- 删除过程
drop procedure 过程名字;

 

mysql批量新增千万级数据脚本（函数+存储过程），步骤如下

  创建表

-- 书分类类型表
create table books_classify(
id int(32) not null auto_increment comment '主键',
classify_code int(10) not null comment '分类编号',
primary key (id)
);

-- 书籍表
create table books(
id int(32) not null auto_increment comment '主键',
books_classify_id int(32) not null comment '外键：分类类型表主键',
bookcode varchar(20) not null comment '书编号',
primary key (id)
);

-- 创建books表外键索引
alter table books add index `idx_books_classify_id`(books_classify_id) using btree;

 根据mysql后台配置选择如下配置否是开启（二进制日志开启，此处一定要开启，否则存储过程和函数创建时会报错）

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1; #如果mysql后台开启了二进制日志，则执行该设置，如果没有则忽略该设置

创建函数

-- 创建函数，获取随机字符串函数rundom_str(len int),len为字符串长度
delimiter $$
create function rundom_str(len int) returns varchar(255)
begin
	  declare char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; -- 声明字符串
		declare return_str varchar(255) default '';-- 声明返回的字符串
		declare i int default 0;
		while i<len do
		set return_str = concat(return_str,SUBSTRING(char_str FROM floor(1+rand()*62) FOR 1));-- floor去掉小数，rand()获取0-1之间的随机数不包括0，1
		set i =i + 1 ;
		end while;
    return return_str;
end
$$
delimiter ;



-- 创建函数，获取随机数rundom_int(len int)，len是随机数的位数
delimiter $$
create function rundom_int(len int) returns int(9)
begin
		declare i int default 0;
		declare variate1 int(9) default 9;
		declare variate2 int(9) default 1;
		declare return_int int(9) default 0;
		
		IF len>0 THEN
				IF len=1 THEN
						set return_int = FLOOR(RAND()*variate1+variate2);
				ELSE
						while i<len-1 do
						set variate1=variate1*10;
						set variate2=variate2*10;
						set i = i+1;
						end while;
						set return_int = FLOOR(RAND()*variate1+variate2);
				END IF;
		ELSE
				set return_int = 0 ;
		END IF;		
    
		return return_int;
end
$$
delimiter ;

 

创建存储过程

-- 创建存储过程，新增书分类类型表，insert_books_classify(from_id,add_num,rundom_int_param)，参数依次是从哪个ID开始新增：新增条数：rundom_int函数的参数
delimiter $$
create procedure insert_books_classify(in from_id int(9),in add_num int(9),in rundom_int_param int(9))
begin
declare i int default 0;# begin后，先把所有的变量声明完成，在set或者其他逻辑，declare声明字段要放在最前面
set autocommit =0 ;# 取消自动提交

repeat  # 重复，等同while
	set i= i+1;
	insert into books_classify(id,classify_code)values(from_id+i,rundom_int(rundom_int_param));
	until i=add_num
end repeat;

commit;
end 
$$
delimiter ;

-- 调用存储过程
call insert_books_classify(10,10,5);



-- 创建存储过程，新增书籍表，
delimiter $$
create procedure insert_books(in from_id int(9),in add_num int(9),in rundom_str_param int(9))
begin
declare i int default 0;# begin后，先把所有的变量声明完成，在set或者其他逻辑，declare声明字段要放在最前面
declare pkid int default 0;
set autocommit =0 ;# 取消自动提交
repeat  # 重复，等同while
	set i= i+1;
	set pkid = (select id from books_classify bc join (select round(rand()*(select max(id ) from books_classify)) as idd) as b1 on bc.id>b1.idd limit 1);
	insert into books(id,books_classify_id,bookcode)values(from_id+i,pkid,rundom_str(rundom_str_param));
	until i=add_num
end repeat;
commit;
end 
$$
delimiter ;

-- 调用存储过程
call insert_books(20,20,8);

操作结果