There are two methods for inserting MySQL test data:

Select the priority (generate test data in combination with PHP > use stored procedures).

• Use stored procedures (it is recommended to use this method when the test data is less than 1000)

The specific code is as follows:

To create a table:

1 mysql> create table TB1(
2     -> id int auto_increment not null,
3     -> name varchar(30) not null default 5,
4     -> primary key(id));
5 Query OK, 0 rows affected (0.02 sec)

To create a stored procedure:

 1 mysql> delimiter $
 2 mysql> create procedure testdata()
 3     -> begin
 4     -> declare i int default 1;
 5     -> while(i<=1000) do
 6     -> insert into TB1(name) values(concat("test",i));
 7     -> set i=i+1;
 8     -> end while;
 9     -> end;
10     -> $
11 Query OK, 0 rows affected (0.02 sec)
12 
13 mysql> delimiter ;

Call stored procedure:

1 mysql> call testdata();
2 Query OK, 1 row affected (5.13 sec)

PS: as can be seen from the above figure, the generation of 1000 pieces of data takes 5.13 seconds, which is particularly slow!

• Generate test data with PHP

testdata.php content:

 1 <?php
 2 //  usage method: php testdata.php root 123456
 3 //  The first parameter is database user name and the second parameter is database password
 4 
 5 //  Some settings
 6 set_time_limit(0);
 7 $_ROWS     = 100000;     # Number of rows to insert
 8 $_DATABASE = "DB1";      # Library name
 9 $_TABLE    = "TB1";      # Table name
10 $_KV       = array(
11     "name" => "test",    # Field name => Field prefix
12 );
13 
14 $t1 = microtime(true);
15 //  generate SQL Sentence
16 $sqlString = "INSERT INTO {$_TABLE} (". implode(",", array_keys($_KV))  .") VALUES ";
17 $arr = $valArr = array();
18 while ($_ROWS > 0) {
19     foreach($_KV as $k => $v) {
20         $arr[] = $v.$_ROWS;
21     }
22     $valArr[] = "('". implode("','", $arr) . "')";
23     $_ROWS--;
24     $arr = array();
25 }
26 $sqlString .= implode(',', array_values($valArr)) . ";";
27 
28 //  Import database
29 list($scriptname, $dbuser, $dbpassword) = $argv;
30 $con = mysqli_connect("127.0.0.1", $dbuser, $dbpassword, $_DATABASE) or die("error connecting") ;;
31 mysqli_query($con, $sqlString);
32 mysqli_close($con);
33 $t2 = microtime(true);
34 echo round($t2-$t1, 3) . " sec\r\n";

Execute script:

1 [root@desktop]# php testdata.php root 123456
2 0.48 sec

PS: by comparing the two methods, it only takes 0.48 seconds to import 100000 pieces of test data with PHP script. It can be seen that the execution speed of this method is much faster than using stored procedures.