| 文章内容来自于：尚硅谷MySQL技术高级篇 |
MySQL批量插入数据最简单的就是循环遍历，调用多次INSERT语句不就可以插入多条记录了吗！但是这种方法会增加服务器的负荷，因为，执行每一次SQL，服务器都要同样对SQL进行分析、优化等操作。MySQL提供了另一种解决方案，就是使用一条INSERT语句来插入多条记录。这并不是标准的SQL语法，因此只能在MySQL中使用。
文章目录
-
- 方式一、load data infile命令
- 方式二、通过插入存储过程方式插入
-
- 1、创建tb_dept_bigdata（部门表）。
- 2、创建tb_emp_bigdata（员工表）。
- 3、开启log_bin_trust_function_creators参数。
-
- 3.1 创建函数，保证每条数据都不同
-
- 3.1.1 创建随机生成字符串的函数。
- 3.1.2 创建随机生成编号的函数。
- 3.2 创建存储过程用于批量插入数据
-
- 3.2.1 创建往tb_dept_bigdata表中插入数据的存储过程。
- 3.2.2 创建往tb_emp_bigdata表中插入数据的存储过程。
- 3.2.3 具体执行过程批量插入数据
- 4、删除函数与存储过程
-
- 4.1 删除函数
- 4.2 删除存储过程
- 5、总结
方式一、load data infile命令
首先修改my.ini（linux是/etc/my.cnf）下secure-file-priv为你存放txt的地址：
secure-file-priv="D:/mysql_import_data/"
然后就可以使用命令导入了
load data infile "D:\mysql_import_data\\文件名.txt" into table 表 fields terminated by '|' lines terminated by '\n' ;
这里要注意 \\文件 这里，一定是双斜杠，否则导入会出错，如果出现中文乱码，先检查数据库本身编码问题，其次检查txt文件编码，都为utf8即可。
方式二、通过插入存储过程方式插入
使用脚本进行大数据量的批量插入，对特定情况下测试数据集的建立非常有用。
创建数据表
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.....因此我们需开启函数创建的信任功能。
通过下面命令查看是否开启：
show variables like '%log_bin_trust_function_creators%';
可通过set global log_bin_trust_function_creators=1的形式开启该功能，也可通过在my.ini(linux中是my.cnf）中永久配置的方式开启该功能，在[mysqld]下配置log_bin_trust_function_creators=1。
3.1 创建函数，保证每条数据都不同
3.1.1 创建随机生成字符串的函数。
delimiter $$
drop function if exists rand_string; //如果存在函数rand_string，则删除
create function rand_string(n int) returns varchar(255) //创建函数rand_string，带一个int参数，返回varchar对应到java就是string了
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));//floor向下取整，rand()函数产生[0,1)之间随机数，*52也就是产生[0,52)之间的随机数。如果有参数3指定需要截取的位数，则是从左往右开始截取也就是从首到尾，而不是从尾到首开始。
set i=i+1;
end while;
return return_str;
end $$
3.1.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 $$
3.2 创建存储过程用于批量插入数据
3.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 $$
3.2.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.2.3 具体执行过程批量插入数据
- 首先执行随机生成字符串的函数。
- 然后执行随机生成编号的函数。
- 使用命令查看函数是否创建成功。
查看函数是否创建成功，这里我这边linux下查看的比较混乱，就直接使用阳哥的图了。
show function status;
查看存储过程是否创建成功
show procedure status;
执行存储过程，插入数据
a.首先执行insert_dept存储过程。
delimiter ; //注意中间的空格，这个就是将mysql语句结束改回分号;因为上面创建存储过程时候改成了$$
call insert_dept(100,100); //调用存储过程插入100条数据
select count(*) from tb_dept_bigdata; //查看记录条数
说明：deptno的范围[100,110)，因为deptno的值使用了rand_num()函数。
b.然后执行insert_emp存储过程。
delimiter ;
call insert_emp(100,300);
select count(*) from tb_emp_bigdata;
说明：tb_emp_bigdata表中deptno编号的范围[100,110)，使用rand_num()函数。
注：对于部门表的deptno和员工表中deptno的数据都使用了rand_num()函数进行赋值，确保两边的值能对应。
4、删除函数与存储过程
4.1 删除函数
drop function rand_num;
drop function rand_string;
4.2 删除存储过程
drop procedure insert_dept;
drop procedure insert_emp;
5、总结
- 注意mysql中函数和存储过程的写法。
- 注意存储过程的调用，
call procedurename。 - 注意开启对函数的信任，
log_bin_trust_function_creators参数。
本文地址：https://blog.csdn.net/dl962454/article/details/110945255