当前位置:Gxlcms > 数据库问题 > Mysql利用存储过程插入400W条数据

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

时间:2021-07-01 10:21:17 帮助过:23人阅读

部门表*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ dname VARCHAR(20) NOT NULL DEFAULT "",/*名称*/ loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/ )ENGINE=MyISAM DEFAULT CHARSET=utf8;
  1. CREATE TABLE emp( <span style="color: #008000">/*</span><span style="color: #008000">EMP雇员表</span><span style="color: #008000">*/</span><span style="color: #000000">
  2. 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">
  3. 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">
  4. 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">
  5. 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">
  6. 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">
  7. 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">
  8. 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">
  9. 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">
  10. )ENGINE</span>=MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET=utf8
  1. CREATE TABLE salgrade( <span style="color: #008000">/*</span><span style="color: #008000">工资级别表</span><span style="color: #008000">*/</span><span style="color: #000000">
  2. grade MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #000000">
  3. losal DECIMAL(</span>17,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #000000">
  4. hisal DECIMAL(</span>17,2) NOT <span style="color: #0000ff">NULL</span><span style="color: #000000">
  5. )ENGINE</span>=MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET=utf8;
  1. INSERT INTO salgrade VALUES (1,700,1200);
  2. INSERT INTO salgrade VALUES (2,1201,1400);
  3. INSERT INTO salgrade VALUES (3,1401,2000);
  4. INSERT INTO salgrade VALUES (4,2001,3000);
  5. INSERT INTO salgrade VALUES (5,3001,9999);
  1. #随机产生字符串
  2. #定义一个新的命令结束符
  3. delimiter $$
  4. #删除自动以函数
  5. drop function rand_string $$

创建函数:

  1. <span style="color: #008000">#</span><span style="color: #008000">rand_string(n INT) rand_string 是函数名(n INT) //该函数传参一个整数</span>
  2. create <span style="color: #0000ff">function</span><span style="color: #000000"> rand_string(n INT)
  3. returns varchar(</span>255<span style="color: #000000">)
  4. begin
  5. </span><span style="color: #0000ff">declare</span> chars_str varchar(100) <span style="color: #0000ff">default</span>
  6. ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘<span style="color: #000000">;
  7. </span><span style="color: #0000ff">declare</span> return_str varchar(255) <span style="color: #0000ff">default</span> ‘‘<span style="color: #000000">;
  8. </span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
  9. </span><span style="color: #0000ff">while</span> i < n <span style="color: #0000ff">do</span><span style="color: #000000">
  10. 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">));
  11. set i </span>= i +1<span style="color: #000000">;
  12. </span><span style="color: #008080">end</span> <span style="color: #0000ff">while</span><span style="color: #000000">;
  13. </span><span style="color: #0000ff">return</span><span style="color: #000000"> return_str;
  14. </span><span style="color: #008080">end</span> $$

 再来一个函数

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

 创建存储过程

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

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

  1. call insert_emp(100001,4000000);

  

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

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

人气教程排行