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

ゝ一世哀愁。 2023-06-07 03:12 60阅读 0赞

## ##

### 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);

### 操作结果 ###

![20191012144558631.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz_size_16_color_FFFFFF_t_70][]

# 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 ;

[20191012144558631.png]: /images/20230601/50b38004ceef48ff8ac92aa042f1414e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz_size_16_color_FFFFFF_t_70]: /images/20230601/e943e2e91341471284359b2f7e7ff83b.png