Fast generation of millions test data on MySQL

1. The simplest method: write a stored procedure to iterate over the data

Create a function to insert:

CREATE PROCEDURE ' insert_test_size ' (in num int)
Begin
declare i int;
Set i=0;
While I<num do
INSERT into Test (A,b,word) VALUES (rand () *10,rand () *100,uuid ());
Set i=i+1;
End while;
End

This method can be added on the millions data, but the time spent is also very large, the implementation of 1W insertion takes time to 9.827s that is, if it is 100W data, it takes more than 15 minutes.

2, the other is to optimize the INSERT statement, directly from the table seconds randomly take a few data inserted into the table data is much faster.

BEGIN
declare i int;
Set i=0;
While I<num do
INSERT into City (cityname,citypinyin) Select Cityname,citypinyin from City WHERE id>= (the Select Floor (RAND () * (select MAX (ID) from city)), ORDER by ID LIMIT 1000;
Set i=i+1;
End while;
END

Inserting 1oW of data takes 14.358s faster than the first method by nearly 85%.

Fast generation of millions test data on MySQL