mysql批量數據腳本

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條隨機生成數據