Mysql uses stored procedures to quickly add millions of data sample code

Preface

In order to reflect the difference between adding and not adding indexes, we need to use millions of data. However, if we add millions of data to a table one by one, it is very cumbersome and troublesome. Here we use stored procedures to quickly add data, which takes about 4 hours.
Create a user table

CREATE TABLE `t_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT 'Username',
  `password` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'Password MD5 storage',
  `register_time` timestamp NULL DEFAULT NULL COMMENT 'Registration time',
  `type` int(1) DEFAULT NULL COMMENT 'User type 1,2,3,4 random',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`) USING BTREE
)

Then create a stored procedure to add data in batches.

• Username is a combination of constants and numbers
• The password is an MD5 password
• The registration time is a random number of days forward from the current time.
• type is a random value in the range of 1-4

create procedure salesAdd()
begin 
 declare i int default 11;
   while i <= 4000000 do
         insert into blog.t_sales
         (`username`,`password`,`register_time`,type) values
         (concat("jack",i),MD5(concat("psswe",i)),from_unixtime(unix_timestamp(now()) - floor(rand() * 800000)),floor(1 + rand() * 4)); 
         set i = i + 1; 
   end while; 
end

Then call the stored procedure

call salesAdd()

Improved version

Although using stored procedures to add data is more convenient and faster than adding data one by one, it takes several hours to add millions of data. Later, I found a lot of information on the Internet and discovered that MySQL automatically commits each time it executes a statement. This operation is very time-consuming, so I added and removed the automatic commit. SET AUTOCOMMIT = 0;

create procedure salesAdd()
begin 
 declare i int default 1;
 set autocommit = 0;   
   while i <= 4000000 do
         insert into blog.t_sales
         (`username`,`password`,`register_time`,type) values
         (concat("jack",i),MD5(concat("psswe",i)),from_unixtime(unix_timestamp(now()) - floor(rand() * 800000)),floor(1 + rand() * 4)); 
         set i = i + 1; 
   end while;
 set autocommit = 1;     
end

The execution time is 387 seconds, which is about six minutes, and half of the time is used for md5 and random number calculations.

[SQL]
call salesAdd();
Affected rows: 0
Time: 387.691s

This is the end of this article about how to quickly add millions of data using stored procedures in MySQL. For more information about how to add millions of data in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!