Shulou(Shulou.com)05/31 Report--

How to implement cyclic batch insertion in mysql? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Data structure

When paging, the standard column is divided into three scenarios: primary key column, index column and normal column. Therefore, the test table needs to include these three scenarios. The syntax for creating the table is as follows:

Drop table if exists `test`.`t _ model`; Create table `test`.`t _ model` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `uid` bigint COMMENT 'business primary key', `modelid`varchar (50) COMMENT 'character primary key', `modelname` varchar (50) COMMENT 'name', `model`varchar (50) COMMENT 'description', primary key (`id`), UNIQUE index `uid_ unique` (`uid`), key `modelid_ index` (`modelid`) USING BTREE) ENGINE=InnoDB charset=utf8 collate=utf8_bin

For convenience, insert operations use stored procedures to insert ordered data through while loops, without verifying the performance of other operations or loops.

Execution process

1. Use the simplest way to directly cycle through a single bar and insert 1W bar. The syntax is as follows:

Drop procedure if exists my_procedure; delimiter / / create procedure my_procedure () begin DECLARE n int DEFAULT 1; WHILE n < 10001 DO insert into t_model (uid,modelid,modelname, `room`) value (ncompanyConcat ('id20170831',n), CONCAT (' name',n), 'desc'); set n = n + 1; END WHILE;end// delimiter

Insert 1W pieces of data, the execution time is about 6m7s, at this speed, to insert 1000W data, it is estimated to run for a few days.

2. So, can the idea of adding a transaction commit speed up the performance? Commit every 1000 items, and the syntax is as follows:

Delimiter / / create procedure u_head_and_low_pro () begin DECLARE n int DEFAULT 17541; WHILE n < 10001 DO insert into t_model (uid,modelid,modelname, `room`) value (n Cocat ('id20170831',n), CONCAT (' name',n), 'desc'); set n = n + 1; if n% 1000 = 0 then commit; end if; END WHILE;end//delimiter

The execution time is 6 min and 16 sec, which is not much different from that without commit. It seems that the performance of batch insertion in this way is very low.

3. Use the stored procedure to generate a batch insert statement to execute a batch insert 1W bar. The syntax is as follows:

Drop procedure IF EXISTS upright headers and permanent lowfloor protagonists $create procedure u_head_and_low_pro () begin DECLARE n int DEFAULT 1; set @ exesql = 'insert into t_model (uid,modelid,modelname, `room`) values'; set @ exedata =''; WHILE n < 10001 DO set @ exedata = concat (@ exedata, "(", n, "'id20170831", n, "','", "name", n, "','", "desc'", ")") If n% 1000 = 0 then set @ exesql = concat (@ exesql,@exedata, ";"); prepare stmt from @ exesql; execute stmt; DEALLOCATE prepare stmt; commit; set @ exesql = 'insert into t_model (uid,modelid,modelname, `room`) values'; set @ exedata = ""; else set @ exedata = concat (@ exedata,','); end if; set n = n + 1; END WHILE;end;$$ delimiter This is the answer to the question about how to achieve circular batch insertion in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.