Php reads the txt file to form an SQL statement and inserts it into the database (original from Zjmainstay)

* ** $ SplitChar field separator * $ file data file name * $ table database table name * $ conn database connection * $ column name corresponding to fields data * $ insertType insert operation type, including INSERT, REPLACE * functionloadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fieldsarr

/*** $ SplitChar field separator * $ file data file name * $ table database table name * $ conn database connection * $ column name corresponding to fields data * $ insertType insert operation type, including INSERT, REPLACE */function loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields = arr

/**
* $ SplitChar field delimiter
* $ File data file name
* $ Table database table name
* $ Conn database connection
* $ Name of the column corresponding to fields data
* $ InsertType INSERT operation type, including INSERT and REPLACE
*/
Function loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields = array (), $ insertType = 'insert '){}

View Code

 ';} Else {echo' Failed! -- Error: '. array_shift ($ result ).'
';}/* Sqlda ta1.txt1 | 0 | A2 | 1 | B3 | 1 | C4 | 2 | D -- cengjiCREATE TABLE 'cengji' ('id' int (11) not null AUTO_INCREMENT, 'parentid' int (11) not null, 'name' varchar (255) default null, primary key ('id '), unique key 'parentid _ name_unique '('parentid', 'name') using btree) ENGINE = InnoDB AUTO_INCREMENT = 1602 default charset = utf8 * // call Example 2 require 'db. php '; $ splitChar = ''; // space $ file = 'sqldata2.txt'; $ fields = array (' Id', 'make', 'model', 'Year'); $ table = 'cars '; $ result = loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields); if (array_shift ($ result) {echo 'success!
';} Else {echo' Failed! -- Error: '. array_shift ($ result ).'
';}/* Sqldata2.txt11 Aston DB19 200912 Aston DB29 200913 Aston DB39 2009 -- carsCREATE TABLE 'cars' ('id' int (11) not null AUTO_INCREMENT, 'make' varchar (16) not null, 'model' varchar (16) default null, 'Year' varchar (16) default null, primary key ('id ')) ENGINE = InnoDB AUTO_INCREMENT = 14 default charset = utf8 * // call Example 3 require 'db. php '; $ splitChar = ''; // Tab $ file = 'sqldata3.txt'; $ fields = array ('id ',' Make ', 'model', 'Year'); $ table = 'xys'; $ insertType = 'replace'; $ result = loadtxtdata1_database ($ splitChar, $ file, $ table, $ conn, $ fields, $ insertType); if (array_shift ($ result) {echo 'success!
';} Else {echo' Failed! -- Error: '. array_shift ($ result ).'
';}/* Sqldata3.txt11 Aston DB19 200912 Aston DB29 200913 Aston DB39 2009 * // call Example 3 require 'db. php '; $ splitChar = ''; // Tab $ file = 'sqldata3.txt'; $ fields = array ('id', 'value'); $ table = 'notexist '; // No table exists $ result = loadtxtdata=database ($ splitChar, $ file, $ table, $ conn, $ fields); if (array_shift ($ result) {echo 'success!
';} Else {echo' Failed! -- Error: '. array_shift ($ result ).'
';} // Appendix: db. php/* // comment this line to release it all?>
 //*/

-- Data table structure:

-- 100000_insert, 1_00_insert

Create table '2017 _ insert '(
'Id' int (11) not null AUTO_INCREMENT,
'Parentid' int (11) not null,
'Name' varchar (255) default null,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8

Insert 100000 (0.1 million) rows: Insert 100000_line_data use 2.5534288883209 seconds

Insert 1000000 (1 million) rows: Insert into 00_line_data use 19.677318811417 seconds

// Possible error: MySQL server has gone away

// Solution: modify my. ini/my. cnf max_allowed_packet = 20 M