今天測試一個功能，需要往數據庫中插入一些原始數據，PM給了一個txt文件，如何快速的將這個txt文件的內容拆分為所要的數組，然后再插入到數據庫中？
serial_number.txt的示例內容：
serial_number.txt：
DM00001A11 0116,SN00002A11 0116,AB00003A11 0116,PV00004A11 0116,OC00005A11 0116,IX00006A11 0116,
創建數據表：
create table serial_number(id int primary key auto_increment not null,serial_number varchar(50) not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;
php代碼如下：
$conn = mysql_connect('127.0.0.1','root','') or die("Invalid query: " . mysql_error());mysql_select_db('test', $conn) or die("Invalid query: " . mysql_error());$content = file_get_contents("serial_number.txt");$contents= explode(",",$content);//explode()函數以","為標識符進行拆分foreach ($contents as $k => $v)//遍歷循環{ $id = $k; $serial_number = $v; mysql_query("insert into serial_number (`id`,`serial_number`) VALUES('$id','$serial_number')");} 備注：方法有很多種，我這里是在拆分txt文件為數組后，然后遍歷循環得到的數組，每循環一次，往數據庫中插入一次。
再給大家分享一個支持大文件導入的
<?php/** * $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)); }}//調用示例1require '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!<br/>';}else { echo 'Failed!--Error:'.array_shift($result).'<br/>';}/*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*///調用示例2require '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!<br/>';}else { echo 'Failed!--Error:'.array_shift($result).'<br/>';}/* 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*///調用示例3require '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!<br/>';}else { echo 'Failed!--Error:'.array_shift($result).'<br/>';}/* sqldata3.txt11 Aston DB19 200912 Aston DB29 200913 Aston DB39 2009 *///調用示例3require '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!<br/>';}else { echo 'Failed!--Error:'.array_shift($result).'<br/>';}//附：db.php/* //注釋這一行可全部釋放?><?phpstatic $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
