MySql---批量插入数据库脚本

1.建表

create database bigData;
use bigData;
 
create table dept(
`id` INT unsigned primary key NOT NULL AUTO_INCREMENT,
`deptno` mediumint unsigned not NULL DEFAULT 0,
`dname` varchar(20) not null default "",
`loc`  VARCHAR(13)  not NULL DEFAULT ""
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
create table emp(
 id int unsigned primary key not null auto_increment,
 empno int unsigned not null default 0,
 ename varchar(20) not null default "",
 job varchar(20) not null default "",
 mgr int unsigned not null default 0,
 hiredate date not null,
 sal decimal(7,2) not null,
 comm decimal(7,2) not null,
 deptno int unsigned not null default 0
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.设置参数



3.创建函数

随机产生字符串

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

4.创建存储过程

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

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),'saleman',00001,curdate(),2000,400,rand_num());
  until i = max_num
  end repeat;
  commit;
end $$

创建往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.调用存储过程

dept

delimiter ;
call insert_dept(100,10);

emp
执行存储过程，往emp表插入50万条数据

delimiter ;
call insert_emp(100001,500000);

---

更新函数

往数据库批量插入数据

#随机产生指定数量的字符串，最大不超过58位
delimiter $$
create function randString(n int) returns varchar(255)
begin
	declare chars varchar(60) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	declare return_str varchar(60) default '';
	declare i int default 0;
	
	while i < n
		do
			set return_str = concat(return_str,substring(chars,floor(1+rand()*52),1));
			set i = i + 1;
	end while;
	return return_str;
end
$$


#随机产生指定数量的数字，最大不超过58位
delimiter $$
create function randNum(n int) returns varchar(255)
begin
	declare chars varchar(60) default '1234567890';
	declare return_str varchar(60) default '';
	declare i int default 0;
	
	while i < n
		do
			set return_str = concat(return_str,substring(chars,floor(1+rand()*10),1));
			set i = i + 1;
	end while;
	return return_str;
end
$$

#随机产生（a,b）之间的数
delimiter $$
create function randBetweenNum(a int,b int) returns int(5)
begin
	declare i int default 0;
	set i = floor(a+rand()*b);
	return i;
end
$$


#随机产生三个字的姓名
delimiter $$
create function randHz() returns varchar(10)
begin
	declare chars1 varchar(60) default '赵钱孙李周吴郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华';
	declare chars2 varchar(60) default '芳华林琳丽中彬斌冰兵为伟炜艳烟期琪燕莎沙深胜升昇南楠豪红';
	declare chars3 varchar(60) default '芳华林琳丽中彬斌冰兵为伟炜艳烟期琪燕莎沙深胜升昇南楠豪红';
	declare return_str varchar(10) default ''; 
	
	set return_str = concat( concat(substring(chars1,floor(1+rand()*28),1),substring(chars2,floor(1+rand()*28),1)) ,substring(chars3,floor(1+rand()*28),1) );	
	return return_str;
end
$$

#数据库所需字段
数据库类型       函数表达
id 				(start+i)
username 		randHz(),    2019-12-12 12:12:12
date   			concat(concat(concat('2019-',randBetweenNum(1,12)),'-'),randBetweenNum(1,28))  
datetime		concat( concat(concat(concat('2019-',randBetweenNum(1,12)),'-'),randBetweenNum(1,28)),  concat(concat(concat(concat(concat(' ',randBetweenNum(1,24)),  ':'),randBetweenNum(1,60)),':'),randBetweenNum(1,60)))
timestamp       concat(concat( concat(concat(concat('2019-',randBetweenNum(1,12)),'-'),randBetweenNum(1,28)),  concat(concat(concat(concat(concat(' ',randBetweenNum(1,24)),  ':'),randBetweenNum(1,60)),':'),randBetweenNum(1,60))),concat(':',randNum(3)) )				
001-009			concat('00',FLOOR(1+RAND()*9))

#往user表插入姓名（测试通过）

delimiter $$
create procedure insert_user(in start int(10),in max_num int(10))
begin
	declare i int default 0;
	set autocommit = 0;
	
	while i < max_num
	do
		insert into user(
			id,			username,	password,		dateDate,																		stringDate
		) values(
			(start+i),	randHz(),	randNum(6),		concat(concat(concat('2019-',randBetweenNum(1,5)),'-'),randBetweenNum(1,28)),	concat(randNum(4),randString(4))
		);
		set i = i + 1;
	
	end while;
	commit;
end
$$

delimiter ;
call insert_user(1,200);