mysql批量插入数据

建表

create table `dept`(
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `dname` varchar(20) NOT NULL DEFAULT '',
 `loc` varchar(13) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk

 CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `hiredate` date NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comm` decimal(7,2) NOT NULL,
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=gbk 

建函数

 

delimiter $$
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'aeagretmnkbfotiyouuwyuwqwadxczvbnmlpioyrjfhdttdrdsdgABJBFRATIROEGBMNGKKHTLITYOUEYUWWQZSDCRFGTH';
    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 $$







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 ;
drop procedure insert_emp;

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 $$
    
    
#调用执行,往dept表添加10条记录
delimiter ;
call insert_dept(100,10);    
#调用执行,往emp表添加50万条记录
delimiter ;
call insert_emp(100001,500000);