使用存储过程脚本批量插入数据

使用存储过程脚本批量插入数据

创建数据库表：

1
2
3
4
5
6
CREATE TABLE bigtables (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `number` int(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建函数：

1
2
3
4
5
6
7
8
9
10
11
12
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 $$

创建产生随机的数字的函数

删除函数： DROP FUNCTION rand_num;

1
2
3
4
5
6
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10); 
return i; end $$

创建存储过程

删除存储过程： drop procedure insert_bigtables;

1
2
3
4
5
6
7
8
9
10
11
12
delimiter $$ 
create procedure insert_bigtables(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 bigtables(id,number,name) values((start+i),rand_num(),rand_string(6));
until i=max_num
end repeat;
commit;
end $$

调用存储过程

1
2
call insert_bigtables(100,10); 
/*number大于100，插入10条数据*/

查看存储过程

select name from mysql.proc where db = 'insert_bigtables' and type = 'PROCEDURE';

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'insert_bigtables' ;

SHOW CREATE PROCEDURE insert_bigtables;

SHOW CREATE FUNCTION rand_num;