mysql批量数据脚本

• 1 建表

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;

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,/*上级编号*/

  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=gbk;

• 2 设置参数

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

• 3 创建函数

 a.随机产生字符串

 delimiter $$

 create function rand_string(n int) returns varchar(255)

 begin

   declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm';

   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 $$

 b.随机产生部门编号

 delimiter $$

 create function rand_num() returns int(5)

 begin

   declare i int default 0;

   set i=floor(100+rand()*10);

 return i;

 end $$

• 4 创建存储过程

a.创建往emp表中插入数据的存储过程

delimiter $$

create procedure insert_emp(in start int(10),in max_num int(10))

begin

 declare i int default 0;

 /*把autocommit设置成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 $$

b.创建往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 $$

• 5 调用存储过程

call insert_dept(100,10);//从deptno为100起插入10条随机生成数据

call insert_emp(100001,5000000);//从empno为100001起插入5000000条随机生成数据