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 过程名字;

 

demo1

           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  #退出循环条件，当循环次数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);

操作结果

 

 

demo2

           查询List集合并且根据集合，对集合逐条遍历进行业务操作

delimiter $$
create procedure insert_coupons(in add_num int(9),in money int(9),in alertType int(1))#add_num每个用户新增条数，insert中字段赋值使用（money，alertType），goodType商品类型，用过循环中判断使用
begin

declare orderId int(32);
declare userId int(32);
declare projectId int(2);
declare s int default 0;# 声明结束标识,迭代退出条件，初始化0，非0时跳出迭代
declare i int default 0;# 用于判定userList集合中每条记录需要新增add_num条对应的数据

##声明游标，查询初需要新增的用户集
declare user_list cursor for SELECT DISTINCT o.id as orderId,o.userid as userId,g.goods_project_id as projectId FROM `dpt_e-commerce`.orders o LEFT JOIN   `dpt_e-commerce`.goods_orders go on o.id=go.ORDERID  LEFT JOIN 
`dpt_e-commerce`.goods g on go.GOODSID=g.id 
where o.PAY_TIME>='2019-11-11 00:00:00' and o.PAY_TIME<'2019-11-19 00:00:00' and go.`STATUS`=0 and g.goods_project_id in(37,40);

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;#设置终止标志，这句话是用在while循环前的，如果游标到了最后就会将之前定义的s设置为1,当fetch游标到了数据库表格最后一行的时候，设置done=1.

set autocommit =0 ;# 取消自动提交

open user_list;# 打开游标，开启迭代
 
	FETCH user_list into orderId,userId,projectId;#将游标中的数据存入到变量中，这样循环中直接引用就可以了
	# 遍历游标
	while s<>1 do  #当s不等于1的时候执行内容操作，跟12行的设置就对应上了
		while i< add_num do
			set i= i+1;
			insert into `dpt_e-commerce`.`goods_order_coupons`(`name`, `from_data_id`, `type`, `limit_project_ids`, `userid`, `status`, `money`, `coupons_type`, `ctime`, `validtime`) values ('在线课程优惠卷', orderId, 1, NULL, userId, 1, money,alertType,now(),now());					
		end while;	
		set i= 0;#为了下一轮外层循环，内层循环设置0重新计算
		FETCH user_list into orderId,userId,projectId;#游标后移，获取下一条记录，跟java中的Iterator是一样的
	end while;
		 
close user_list;#关闭迭代

commit;
end 
$$
delimiter ;