How to rapidly insert millions of mysql test data records

Recently I want to create a testing environment with a large amount of data, so I found out how to insert million data records. I used 20 fields. For comparison, we first use the mysql stored procedure: mysql> delimiter $ mysql> set autocommit = 0 $ mysql> create procedure test () begin www.2cto.com declare I decimal (10) default 0; dd: loop insert into 'mililion' ('categ _ id', 'categ _ fid', 'sortpath', 'address', 'P _ identifier ', 'Pro _ specification ', 'name', 'add _ date', 'picture _ url', 'thumb _ url', 'is _ display_front', 'create _ html_time ', 'hit', 'buy _ Sum', 'athor', 'templete _ style ', 'Is _ hot ', 'is _ new', 'is _ best') VALUES (268, 2, '0,262,268,', 0, '123', '123 ', '000000', '2017-01-09 09:55:43 ', 'upload/product/20111205153432_53211.jpg', 'upload/product/thumb_20111205153432_53211.jpg ', 1, 0, 0, 0, 0, 'admin', '0', 0, 0, 0); commit; set I = I + 1; if I = 1000000 then leave dd; end if; end loop dd; end; $ mysql> delimiter; mysql> call test; www.2cto.com result mysql> call test; Query OK, 0 Rows affected (58 min 30.83 sec) is time consuming.
So I found another method to generate data with PHP code and then import the data: <? Php $ t = mktime (); set_time_limit (1000); $ myFile = "e:/insert. SQL "; $ fhandler = fopen ($ myFile, 'wb'); if ($ fhandler) {$ SQL =" 268 \ t2 \ t' 0,262,268, '\ t0 \ t' 000000' \ t' 000000' \ t' 000000' \ t' 000000' \ t' 2342-01-09 09:55:43' \ t' upload/product/ 20111205153432_53211.jpg '\ t' upload/product/thumb_20111205153432_53211.jpg' \ tNULL \ t38 \ t' pieces '\ t' \ t123 \ t123 \ t0 "; $ I = 0; while ($ I <1000000) // 1,000,000 {$ I ++; fwrite ($ fhandler, $ SQL. "\ r \ n ");} Www.2cto.com echo" written successfully, time consumed: ", mktime ()-$ t;} and then import load data local INFILE 'e:/insert. SQL 'into TABLE tenmillion ('categ _ id', 'categ _ fid', 'sortpath', 'address', 'P _ identifier ', 'Pro _ specification ', 'name', 'description', 'add _ date', 'picture _ url', 'thumb _ url', 'shop _ url', 'shop _ thumb_url ', 'brand _ id', 'unit ', 'square _ meters_unit', 'market _ price', 'true _ price', 'square _ meters_price '); note that fields are no longer separated by commas (,). \ T split. Records are separated by \ r \ n. As a result, I insert 10 data records, and an average of data records can only be inserted in 1 minute. In the second method, many intermediate steps are omitted in mysql, resulting in a much faster insertion speed than in the first method. Author technology panda