MySQL循环插入千万级数据

1、创建测试表

create table `mysql_genarate` (
 `id` int(11) not null auto_increment,
 `uuid` varchar(50) default null,
 primary key (`id`)
) engine=innodb auto_increment=5999001 default charset=utf8;

2、创建一个循环插入的存储过程

create definer=`root`@`localhost` procedure `test_two1`( )
  begin 
  declare i int default 0;
  while i < 3000 do
    insert into mysql_genarate ( uuid ) values( uuid( ) );
    set i = i + 1;
  end while;
end

调用测试call test_two1(), 测试10000条数据耗时几分钟，如果是千万级数据，这个速度将无法忍受。

3、优化存储过程

使用批量插入的sql语句

create definer=`root`@`localhost` procedure `insertpro`( in sum int )
  begin
  declare count int default 0;
  declare i int default 0;
  set @exesql = concat( "insert into mysql_genarate(uuid) values" );
  set @exedata = "";
  set count = 0;
  set i = 0;
  while count < sum do
    set @exedata = concat( @exedata, ",(uuid())" );
    set count = count + 1;
    set i = i + 1;
    if i % 1000 = 0 then
      set @exedata = substring( @exedata, 2 );
      set @exesql = concat( "insert into mysql_genarate(uuid) values ", @exedata );
      prepare stmt from @exesql;
      execute stmt;
      deallocate prepare stmt;
      set @exedata = "";
    end if;
  end while;
  if length( @exedata ) > 0 then
    set @exedata = substring( @exedata, 2 );
    set @exesql = concat( "insert into mysql_genarate(uuid) values ", @exedata );
    prepare stmt from @exesql;
    execute stmt;
    deallocate prepare stmt;
  end if;
end

调用 call insertpro(10000) ，耗时零点几秒，这个速度可以接受。

以上就是mysql循环插入千万级数据的详细内容，更多关于mysql循环插入的资料请关注其它相关文章！