How to insert test data in MySQL and test data in MySQL

How to insert test data in MySQL and test data in MySQL

To insert MySQL test data, we will introduce two methods:

Select priority(Use PHP to generate test data>Use stored procedures).

• Use stored procedures (this method is recommended if the test data is smaller than 1000 entries)

The Code is as follows:

　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)

　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:It can be seen that the generation of 1000 pieces of data consumes 5.13 seconds, which is particularly slow!

 

 

• Combined with PHPTest Data

　Testdata. php content:

1 <? Php 2 // usage: php testdata. php root 123456 3 // The first parameter is the database username, and the second parameter is the Database Password 4 5 // some configuration items 6 set_time_limit (0); 7 $ _ ROWS = 100000; # The number of rows to be inserted is 8 $ _ DATABASE = "DB1"; # DATABASE 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 statement 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 scripts:

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

PS:By comparing the two methods, it takes only 100000 seconds to import 0.48 pieces of test data using a PHP script. It can be seen that the method is executed much faster than the stored procedure.