欢迎您的访问
专注于Java技术系列文章的Java技术分享网站
关注我们

—— 加入社群 ——
「十大城市」工作内推
「微信/QQ」技术讨论
「面试真题」随时领取
公众号名称:搜云库技术团队  公众号ID:souyunku
关注公众号后发送 口令 获取关闭弹窗地址
公众号每天中午 12:20 为你推送一篇技术文章

MySQL高级知识(十)——批量插入数据脚本

号外:限时领取:2020,全网最新、最全的技术知识体系

前言:使用脚本进行大数据量的批量插入,对特定情况下测试数据集的建立非常有用。


0.准备

#1、创建tb_dept_bigdata(部门表)。

create table tb_dept_bigdata(
id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default '',
loc varchar(13) not null default ''
)engine=innodb default charset=utf8;

#2、创建tb_emp_bigdata(员工表)。

create table tb_emp_bigdata(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
empname varchar(20) not null default '',
job varchar(9) not null default '',
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0 
)engine=innodb default charset=utf8;

#3、开启log_bin_trust_function_creators参数。

由于在创建函数时,可能会报:This function has none of DETERMINISTIC…..因此我们需开启函数创建的信任功能。

77_1.png

可通过set global log_bin_trust_function_creators=1的形式开启该功能,也可通过在my.cnf中永久配置的方式开启该功能,在[mysqld]下配置log_bin_trust_function_creators=1。

1.创建函数,保证每条数据都不同

#1、创建随机生成字符串的函数。

delimiter $$
drop function if exists rand_string;
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$

#2、创建随机生成编号的函数。

delimiter $$
drop function if exists rand_num;
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*100);
return i;
end $$

2.创建存储过程用于批量插入数据

#1、创建往tb_dept_bigdata表中插入数据的存储过程。

delimiter $$
drop procedure if exists insert_dept;
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into tb_dept_bigdata (deptno,dname,loc) values(rand_num(),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$

#2、创建往tb_emp_bigdata表中插入数据的存储过程。

delimiter $$
drop procedure if exists insert_emp;
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into tb_emp_bigdata (empno,empname,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'developer',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

3.具体执行过程批量插入数据

#1、首先执行随机生成字符串的函数。

#2、然后执行随机生成编号的函数。

#3、查看函数是否创建成功。

77_2.png

#4、执行插入数据的存储过程,并查看其创建情况。

77_3.png

#5、执行存储过程,插入数据。

a.首先执行insert_dept存储过程。

77_4.png

说明:deptno的范围[100,110),因为deptno的值使用了rand_num()函数。

b.然后执行insert_emp存储过程。

77_5.png

说明:tb_emp_bigdata表中deptno编号的范围[100,110),使用rand_num()函数。

注:对于部门表的deptno和员工表中deptno的数据都使用了rand_num()函数进行赋值,确保两边的值能对应。

4.删除函数与存储过程

#1、删除函数

drop function rand_num;
drop function rand_string;

#2、删除存储过程

drop procedure insert_dept;
drop procedure insert_emp;

总结

①注意mysql中函数和存储过程的写法。

②注意存储过程的调用,call procedurename。

③注意开启对函数的信任,log_bin_trust_function_creators参数。

https://www.cnblogs.com/developer_chan/category/1020652.html

文章永久链接:https://tech.souyunku.com/?p=13742

赞(99) 打赏

版权归原创作者所有,任何形式转载请联系作者;搜云库技术团队 » MySQL高级知识(十)——批量插入数据脚本
本站:免责声明!

评论 抢沙发

一个专注于Java技术系列文章的技术分享网站

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏