代码如下:
#建库建表
create database bigData;
use bigData;
create table if not exists 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 ''
);
create table if not exists 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,
hiredate date not null ,
sal decimal(7,2) not null ,
comm decimal(7,2) not null ,
deptno mediumint unsigned not null default 0
);
#创建生成随机字符串和部门编号的函数
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,substr(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 $
#创建批量插入数据的存储过程
delimiter $
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 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 $
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 $
#插入部门数据
call insert_dept(100,10);
#插入员工数据(每次插入50万条)
call insert_emp(100001,500000);