mysql批量插入数据

1、创建部门表和员工表

--建表dept
create table dept(
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=gbk;

--建表emp
create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0 comment '上级编号',
hiredate date not null comment '入职时间',
sal decimal(7,2) not null comment '薪水',
comm decimal(7,2) not null comment '红利',
deptno mediumint unsigned not null default 0 comment '部门编号'
)engine=innodb default charset=gbk;

2、自定义创建随机函数

--创建随机字符串函数，函数有返回值，过程没有返回值
DELIMITER $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) 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 $$

--产生随机部门编号
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $$

--删除drop function rand_num;

3、定义插入procedure

--插入,insert操作不需要返回值，创建procedure
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin 
declare i int default 0;
--set autocommit=0 把autocommit 设置成0 批量commit,否则insert一次就commint一次，对于千万级量数据库吃不消
set autocommit = 0;
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values ((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$



--insert dept
delimiter $$
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 dept(deptno,dname,loc) values ((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$

4、调用procudure批量插入数据

delimiter ;
call insert_dept(100,10);
call insert_emp(100001,500000);