/**
* $splitChar Field Delimiter
* $file Data file name
* $table database table name
* $conn Database connection
* Column name corresponding to the $fields data
* $insertType insert operation type, including Insert,replace
*/
Copy CodeThe code is as follows:
/**
* $splitChar Field Delimiter
* $file Data file name
* $table database table name
* $conn Database connection
* Column name corresponding to the $fields data
* $insertType insert operation type, including Insert,replace
*/
function Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields =array (), $insertType = ' INSERT ') {
if (empty ($fields)) $head = "{$insertType} into ' {$table} ' VALUES (');
else $head = "{$insertType} into ' {$table} ' ('". Implode (', ', ', $fields). " ') VALUES (' "; Data header
$end = "')";
$sqldata = Trim (file_get_contents ($file));
if (preg_replace ('/\s*/i ', ', $splitChar) = = ") {
$splitChar = '/(\w+) (\s+)/I ';
$replace = "$ ', '";
$specialFunc = ' preg_replace ';
}else {
$splitChar = $splitChar;
$replace = "', '";
$specialFunc = ' str_replace ';
}
Handles the data body, which is not swapped in order, otherwise a space or tab delimiter error occurs
$sqldata = Preg_replace ('/(\s*) (\n+) (\s*)/i ', ' \ '), (\ ' ", $sqldata); Replace line break
$sqldata = $specialFunc ($splitChar, $replace, $sqldata); Replace delimiter
$query = $head. $sqldata. $end; Data stitching
if (mysql_query ($query, $conn)) return array (true);
else {
Return Array (False,mysql_error ($conn), Mysql_errno ($conn));
}
}
Invoking Example 1
Require ' db.php ';
$splitChar = ' | '; Vertical
$file = ' sqldata1.txt ';
$fields = array (' id ', ' parentid ', ' name ');
$table = ' Cengji ';
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields);
if (Array_shift ($result)) {
Echo ' success!
';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). '
';
}
/*sqlda Ta1.txt
|0| A
|1| B
|1| C
|2| D
--Cengji
CREATE TABLE ' Cengji ' (
' id ' int (one) not NULL auto_increment,
' ParentID ' int (one) is 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
*/
invoking Example 2
Require ' db.php ';
$splitChar = "; Space
$file = ' sqldata2.txt ';
$fields = array (' id ', ' make ', ' model ', ' year ');
$table = ' cars ';
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields);
if (Array_shift ($result)) {
Echo ' success!
';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). '
';
}
/* Sqldata2.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
--Cars
CREATE TABLE ' Cars ' (
' id ' int (one) not NULL auto_increment,
' Make ' varchar (+) is not NULL,
' Model ' varchar (+) DEFAULT NULL,
' Year ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=14 DEFAULT Charset=utf8
*/
invoking Example 3
Require ' db.php ';
$splitChar = "; Tab
$file = ' sqldata3.txt ';
$fields = array (' id ', ' make ', ' model ', ' year ');
$table = ' cars ';
$insertType = ' REPLACE ';
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields, $insertType);
if (Array_shift ($result)) {
Echo ' success!
';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). '
';
}
/* Sqldata3.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
*/
invoking Example 3
Require ' db.php ';
$splitChar = "; Tab
$file = ' sqldata3.txt ';
$fields = array (' ID ', ' value ');
$table = ' notexist '; Table does not exist
$result = Loadtxtdataintodatabase ($splitChar, $file, $table, $conn, $fields);
if (Array_shift ($result)) {
Echo ' success!
';
}else {
Echo ' Failed!--Error: '. Array_shift ($result). '
';
}
Attached: db.php
/*//Note this line can be fully released
?>
static $connect = null;
static $table = ' Jilian ';
if (!isset ($connect)) {
$connect = mysql_connect ("localhost", "root", "");
if (! $connect) {
$connect = mysql_connect ("localhost", "Zjmainstay", "" ");
}
if (! $connect) {
Die (' Can not connect to database. Fatal error handle by/test/db.php ');
}
mysql_select_db ("Test", $connect);
mysql_query ("SET NAMES UTF8", $connect);
$conn = & $connect;
$db = & $connect;
}
?>
//*/
Data table structure
Copy CodeThe code is as follows:
--Data table structure:
--100000_insert,1000000_insert
CREATE TABLE ' 100000_insert ' (
' id ' int (one) not NULL auto_increment,
' ParentID ' int (one) is not NULL,
' Name ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=1 DEFAULT Charset=utf8
100000 (100,000) row insert: Insert 100000_line_data use 2.5534288883209 seconds
1000000 (1 million) row insert: Insert 1000000_line_data use 19.677318811417 seconds
Possible error: MySQL server has gone away
FIX: Modify MY.INI/MY.CNF max_allowed_packet=20m
Author: Zjmainstay
http://www.bkjia.com/PHPjc/325790.html www.bkjia.com true http://www.bkjia.com/PHPjc/325790.html techarticle /** * $splitChar Field delimiter * $file data file filename * $table database table name * $conn Database connection * $fields data corresponding column name * $insertType insert operation type, including ...
