Mysql利用存储过程插入400W条数据

CREATE TABLE emp( <span style="color: #008000">/*</span><span style="color: #008000">EMP雇员表</span><span style="color: #008000">*/</span><span style="color: #000000">
empno MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #008000">/*</span><span style="color: #008000">编号</span><span style="color: #008000">*/</span><span style="color: #000000">
ename VARCHAR(</span>20) NOT <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> "",<span style="color: #008000">/*</span><span style="color: #008000">名字</span><span style="color: #008000">*/</span><span style="color: #000000">
  job VARCHAR(</span>9) NOT <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> "",<span style="color: #008000">/*</span><span style="color: #008000">工作</span><span style="color: #008000">*/</span><span style="color: #000000">
  mgr MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #008000">/*</span><span style="color: #008000">是哪个及编号</span><span style="color: #008000">*/</span><span style="color: #000000">
  hiredate </span><span style="color: #008080">DATE</span> NOT <span style="color: #0000ff">NULL</span>,<span style="color: #008000">/*</span><span style="color: #008000">入职时间</span><span style="color: #008000">*/</span><span style="color: #000000">
  sal DECIMAL(</span>7,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #008000">/*</span><span style="color: #008000">薪水</span><span style="color: #008000">*/</span><span style="color: #000000">
  comm DECIMAL(</span>7,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #008000">/*</span><span style="color: #008000">红利</span><span style="color: #008000">*/</span><span style="color: #000000">
  deptno MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0 <span style="color: #008000">/*</span><span style="color: #008000">部门编号</span><span style="color: #008000">*/</span><span style="color: #000000">
)ENGINE</span>=MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET=utf8

CREATE TABLE salgrade( <span style="color: #008000">/*</span><span style="color: #008000">工资级别表</span><span style="color: #008000">*/</span><span style="color: #000000">
 grade MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #000000">
 losal DECIMAL(</span>17,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #000000">
 hisal DECIMAL(</span>17,2) NOT <span style="color: #0000ff">NULL</span><span style="color: #000000">
)ENGINE</span>=MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET=utf8;

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

#随机产生字符串
#定义一个新的命令结束符
delimiter $$
#删除自动以函数
drop function rand_string $$

创建函数：

<span style="color: #008000">#</span><span style="color: #008000">rand_string(n INT) rand_string 是函数名(n INT)  //该函数传参一个整数</span>
create <span style="color: #0000ff">function</span><span style="color: #000000"> rand_string(n INT)
returns varchar(</span>255<span style="color: #000000">) 
begin
</span><span style="color: #0000ff">declare</span> chars_str varchar(100) <span style="color: #0000ff">default</span> 
 ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘<span style="color: #000000">;
</span><span style="color: #0000ff">declare</span> return_str varchar(255) <span style="color: #0000ff">default</span> ‘‘<span style="color: #000000">;
</span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
</span><span style="color: #0000ff">while</span> i < n <span style="color: #0000ff">do</span><span style="color: #000000">
 set return_str</span>=concat(return_str,substring(chars_str,<span style="color: #008080">floor</span>(1+<span style="color: #008080">rand</span>()*52),1<span style="color: #000000">));
 set i </span>= i +1<span style="color: #000000">;
 </span><span style="color: #008080">end</span> <span style="color: #0000ff">while</span><span style="color: #000000">;
</span><span style="color: #0000ff">return</span><span style="color: #000000"> return_str;
</span><span style="color: #008080">end</span> $$

 再来一个函数

create <span style="color: #0000ff">function</span><span style="color: #000000"> rand_num( )
returns int(</span>5<span style="color: #000000">)
begin
 </span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
 set i </span>= <span style="color: #008080">floor</span>(10+<span style="color: #008080">rand</span>()*500<span style="color: #000000">);
</span><span style="color: #0000ff">return</span><span style="color: #000000"> i;
 </span><span style="color: #008080">end</span> $$

 创建存储过程

create procedure insert_emp(in start int(10),in max_num int(10<span style="color: #000000">))
begin
</span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
</span><span style="color: #008000">#</span><span style="color: #008000">set autocommit = 0 把autocommit设置成0</span>
set autocommit = 0<span style="color: #000000">;
repeat
set i </span>= i +1<span style="color: #000000">;
insert into emp values ((start</span>+i),rand_string(6),‘SALESMAN‘,0001,curdate(),2000,400,<span style="color: #000000">rand_num());
until i </span>=<span style="color: #000000"> max_num
</span><span style="color: #008080">end</span><span style="color: #000000"> repeat;
commit;
</span><span style="color: #008080">end</span> $$

delimiter ;

调用刚刚写好的函数，从100001号开始,产生4000000记录

call insert_emp(100001,4000000);

Mysql利用存储过程插入400W条数据

标签：his   定义   style   arc   命令   drop   proc   数据   调用