PHP implementation MySQL Millions data insertion, time consuming about 10s

Title, recently done a project, demand is like this, write a functional module, to achieve batch import, save time for customer service (well, demand is demand). Fortunately, the inserted data is a number of consecutive numbers, so you can use

foreach loops out the data and then stitching it into the MySQL INSERT statement to BULK Insert the large segment. Principle is such a principle, of course, there is a hole in the process, what to open the extension limit, for the moment only to see the logical side of it.

Frame words, not laravel, with the CI, the example code is as follows:

    /** * @desc Bulk Import millions data warehousing (temporarily only rhyme, method has been written general, data minimum 5,000, maximum 100W) * @date 2017-10-26 20:45:45 * @param [in T $start _no start number, int $end _no cutoff number, string $express _type type] * @author [email protected] * @return [Type] *         /Public Function Import_million_express_no () {//Basic data Set header (' Content-type:text/html;charset=utf-8 ');        Ini_set (' Memory_limit ', ' 128M '); Set the table name in the corresponding database $express _to_form = [' test1 ' = ' from1 ',//platform 1 corresponding table name ' test2 ' = ' from2 '        ,//table name corresponding to platform 2];        Get parameter $start _no = Trim ($this->input->post (' start_no '));        $end _no = Trim ($this->input->post (' end_no '));        $express _type = Trim ($this->input->post (' Express_type ')); Determine if the parameter exists if (! $start _no | |! $end _no) {echo ' <script>alert ("entry failed, starting and closing number cannot be null for 0"); History.back (); &L            T;/script> ';        Return }//The starting number cannot be greater than or equal to the cut-off ticket, and the input quantity is at least 5,000 if ($start _no &Gt;= $end _no) {echo ' <script>alert ("the starting number cannot be greater than or equal to the cutoff number!");            History.back ();</script> ';        Return } else {if ($end _no-$start _no < x) {echo ' <script>alert ("cannot be less than 5,000 per entry number!");                History.back ();</script> ';            Return } if ($end _no-$start _no > 1000000) {echo ' <script>alert ("No more than 1 million per entry number!");                History.back ();</script> ';            Return        }}//Determine if the data type exists $table _name = $express _to_form[$express _type]; if (! $table _name) {echo ' <script>alert ("The Courier type is wrong and cannot be printed!");            History.back ();</script> ';        Return            }else{//Determine the initial number, whether the cut-off number has been entered $sql 1 = "Select id from {$table _name} where express_no = {$start _no}";            $res 1 = $this->db->query ($sql 1)->row (); if ($res 1) {echo ' <script>alert ("start number already exists!"); History.back (); </script> ';            Return            } $sql 2 = "Select id from {$table _name} where express_no = {$end _no}";            $res 2 = $this->db->query ($sql 2)->row (); if ($res 2) {echo ' <script>alert ("the cutoff number already exists!");                History.back ();</script> ';            Return  }}/*** above a series of judgment on the nonsense can not see, directly see how the following logical processing of data ***///The starting number and the cutoff number of the interval division $length = $end _no-$start _no +        1;        $times = Floor ($length/5000);        $temp _data = [];          for ($i =0; $i < $times; $i + +) {$temp _data[$i] [' start_no '] = $start _no;   Starting number $temp _data[$i] [' end_no '] = $start _no + 4999;  End number $start _no + = 5000; The starting number of the next cycle}//Check the array for the last set of data to determine if you need to add if ($end _no > $temp _data[$times -1][' End_no ') {$te            mp_data[$times [' start_no '] = $temp _data[$times -1][' end_no '] + 1;        $temp _data[$times] [' end_no '] = $end _no; }//To import the database SQL statement splicing $add _Time = time ();        $add _user = $this->session->userdata[' user_name ');        $tmp _val = "(' {$add _time} ', ' $add _user ', 0, '%s ', 0),"; for ($j =0; $j <count ($temp _data); $j + +) {//Loop stitch SQL INSERT statement $sql = "INSERT INTO {$table _name} (Field1,fiel            D2,FIELD3,FIELD4,FIELD5) values ";             for ($i = $temp _data[$j] [' start_no ']; $i <= $temp _data[$j] [' end_no ']; $i + +) {$sql. = sprintf ($tmp _val, $i); } $sql = Trim ($sql, ', ').            ‘;‘;            $bool = $this->db->query ($sql);                Execute insert error, write into log Exception table FROM3 if (! $bool) {//log $log _info = Array ();                $log _info[' field1 '] = time (); $log _info[' field2 ' = ' type: '. $express _type. ' Execution error, number '. $temp _data[$j] [' start_no ']. ' -'. $temp _data[$j] [' end_no ']. '                Failed to execute ';                $log _info[' field3 ') = $this->session->userdata[' user_name '];                $this->db->insert (' from3 ', $log _info);    Error log Flags            $err _log_info = TRUE; }}//Data return if ($err _log_info) {echo ' <script>alert ("Part number execution failed, please contact the administrator to resolve!");        History.back ();</script> '; }else{Echo ' <script>alert ("Data execution success!!!        "); History.back ();</script> ';    } return; }

Above is a package of a complete class, parameter validation what, you can not look at, directly look at the SQL statement stitching, in fact, finally found it, did not do anything. After testing, the basic time spent hovering around 10s, nn, temporarily first such, have a good idea, welcome to communicate. I'm going to have to work overtime, 233.

PHP implementation MySQL Millions data insertion, time consuming about 10s