使用存储过程脚本批量插入数据
创建数据库表:
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;
创建函数:
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;
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;
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 $$
调用存储过程
call insert_bigtables(100,1000);
/*number大于100,插入1000条数据*/
查看存储过程
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;