php读取txt文件组成SQL并插入数据库的代码（原创自Zjmainstay）

php读取txt文件组成SQL并插入数据库的代码（原创自Zjmainstay）_PHP教程

WBOY

发布： 2016-07-21 15:17:15

原创

845 人浏览过

/**
* $splitChar 字段分隔符
* $file 数据文件文件名
* $table 数据库表名
* $conn 数据库连接
* $fields 数据对应的列名
* $insertType 插入操作类型，包括INSERT,REPLACE
*/

复制代码代码如下:

 
/** 
* $splitChar 字段分隔符 
* $file 数据文件文件名 
* $table 数据库表名 
* $conn 数据库连接 
* $fields 数据对应的列名 
* $insertType 插入操作类型，包括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('"; //数据头 
$end = "')"; 
$sqldata = trim(file_get_contents($file)); 
if(preg_replace('/\s*/i','',$splitChar) == '') { 
$splitChar = '/(\w+)(\s+)/i'; 
$replace = "$1','"; 
$specialFunc = 'preg_replace'; 
}else { 
$splitChar = $splitChar; 
$replace = "','"; 
$specialFunc = 'str_replace'; 
} 
//处理数据体，二者顺序不可换，否则空格或Tab分隔符时出错 
$sqldata = preg_replace('/(\s*)(\n+)(\s*)/i','\'),(\'',$sqldata); //替换换行 
$sqldata = $specialFunc($splitChar,$replace,$sqldata); //替换分隔符 
$query = $head.$sqldata.$end; //数据拼接 
if(mysql_query($query,$conn)) return array(true); 
else { 
return array(false,mysql_error($conn),mysql_errno($conn)); 
} 
} 
//调用示例1 
require 'db.php'; 
$splitChar = '|'; //竖线 
$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(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 
*/ 
//调用示例2 
require 'db.php'; 
$splitChar = ' '; //空格 
$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(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 
*/ 
//调用示例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 
*/ 
//调用示例3 
require 'db.php'; 
$splitChar = ' '; //Tab 
$file = 'sqldata3.txt'; 
$fields = array('id','value'); 
$table = 'notExist'; //不存在表 
$result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields); 
if (array_shift($result)){ 
echo 'Success!
'; 
}else { 
echo 'Failed!--Error:'.array_shift($result).'
'; 
} 
//附：db.php 
/* //注释这一行可全部释放 
?> 
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; 
} 
?> 
//*/ 

数据表结构

复制代码代码如下:

 
-- 数据表结构： 
-- 100000_insert,1000000_insert 
CREATE TABLE `100000_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 
100000 （10万）行插入：Insert 100000_line_data use 2.5534288883209 seconds 
1000000（100万）行插入：Insert 1000000_line_data use 19.677318811417 seconds 
//可能报错：MySQL server has gone away 
//解决：修改my.ini/my.cnf max_allowed_packet=20M 

作者：Zjmainstay