php讀取txt檔案組成SQL並插入資料庫的方法-php教程-PHP中文網

php讀取txt檔案組成SQL並插入資料庫的方法

WBOY

發布： 2016-07-25 09:04:33

原創

996 人瀏覽過

/**
* $splitChar 欄位分隔符號
* $file 資料檔檔名
* $table 資料庫表名
* $conn 資料庫連線
* $fields 資料對應的列名
* $insertType 插入操作類型，包括INSERT,REPLACE
* 蒐集整理：bbs.it-home.org
*/
function loadTxtDataIntoDatabase($splitChar,$file,$,,$file,$ $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,
`id`),
UNIQUE KEY `parentid_name_unique` (`parentid`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1602 DEFAULT CHARSET=utf8
/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
Aston DB39 2009
-- cars
Aston DB39 2009
-- cars
ATE`37> id` int(11) NOT NULL AUTO_INCREMENT,
`make` varchar(16) NOT NULL,
`model` varchar(16) DEFAULT NULL,
`year` varchar(16)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
*/
';
$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 ` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utv81000000（100萬）行插入：Insert 1000000_line_data 00019.673178173172 月/>sec3100/0067310 away
//解決：修改my. ini/my.cnf max_allowed_packet=20M
複製代碼

作者：Zjmainstay