MySQL循环插入千万级数据
程序员文章站
2022-07-02 20:22:04
1、创建测试表create table `mysql_genarate` ( `id` int(11) not null auto_increment, `uuid` varchar(50) defa...
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循环插入的资料请关注其它相关文章!