PHP讀取SQL檔案並導入資料庫(範例)

/**
* 讀取 sql 檔案並寫入資料庫
* @version 1.01 demo.php
* @author xingshaocheng
* @edit: bbs.it-home.org
*/
class DBManager
{
 var $dbHost = '';
 var $dbUser = '';
 var $dbPassword = '';
 var $dbSchema = '';

 function __construct($host,$user,$host,$user,$ password,$schema)
 {
 $this->dbHost = $host;
 $this->dbUser = $user;
 $this->dbPassword = $password;
 $this ->dbSchema = $schema;
 }

 function createFromFile($sqlPath,$delimiter = '(;n)|((;rn))|(;r)',$prefix = '' ,$commenter = array('#','--'))
 {
 //判斷檔案是否存在
 if(!file_exists($sqlPath))
 return false;

 $handle = fopen($sqlPath,'rb');

 $sqlStr = fread($handle,filesize($sqlPath));

 //透過sql語法的語句分割符進行分割
 $segment = explode(";",trim($sqlStr));

 //var_dump($segment);

 //去掉註解和多餘的空白行
 foreach($segment as & $statement)
 {
 $sentence = explode("n",$statement);

 $newStatement = array();

 foreach($sentence as $subSentence)
 {
 if('' != trim($subSentence))
 {
 //判斷是會否是註解
 $isComment = false;
 foreach($commenter as $comer)
 {
 if(eregi("^(".$comer.")",trim($subSentence)))
 {
 $isComment = true;
 break;
 }
 }
 //如果不是註釋，則認為是sql語句
 if(!$isComment)
 $newStatement[] = $subSentence;
 }
 }

 $statement = $newStatement;
 }
 //對表名加上前綴
 if('' != $prefix)
 {


 //只有表名在第一行出現時才有效例如CREATE TABLE talbeName

 $regxTable = "^[`'"]{0,1} [_a-zA-Z] [_a-zA-Z0-9]*[`'"]{0,1}$";//處理表名的正規表示式
 $regxLeftWall = "^[`' "]發達";

 $sqlFlagTree = array(
 "CREATE" => array(
 "TABLE" => array(
 "$regxTable" => 0
 )
 ),
 "INSERT" => array(
 "INTO" => array(
 "$regxTable" => 0
 )
 )

 ) ;

 foreach($segment as & $statement)
 {
 $tokens = split(" ",$statement[0]);

 $tableName = array() ;
 $this->findTableName($sqlFlagTree,$tokens,0,$tableName);

 if(empty($tableName['leftWall']))
 {
 $newTableName = $prefix.$tableName['name'];
 }
 else{
 $newTableName = $tableName['leftWall'].$prefix.substr($tableName['name'],1) ;
 }

 $statement[0] = str_replace($tableName['name'],$newTableName,$statement[0]);
 }

 }
 //組合sql語句
 foreach($segment as & $statement)
 {
 $newStmt = '';
 foreach($statement as $sentence)
 {
 $ newStmt = $newStmt.trim($sentence)."n";
 }

 $statement = $newStmt;
 }

 //用於測試---- --------------------
 //var_dump($segment);
 //writeArrayToFile('data.txt',$segment);
 //-------------------------------

 self::saveByQuery($segment);

 return true;
 }

 烘烤函數saveByQuery($sqlArray)
 {
 $conn = mysql_connect($this->dbHost,$this->dbUser,$this->dbPassword);


 mysql_select_db($this->dbSchema);

 foreach($sqlArray as $sql)
 {
 mysql_query($sql);
 }
 mysql_close($ conn);
 }

 通風函數findTableName($sqlFlagTree,$tokens,$tokensKey=0,& $tableName = array())
 {
 $regxLeftWall = "^[`` '"]展開";

 if(count($tokens) return false;

 if('' == trim($tokens[$tokensKey) ])))
 {
 return self::findTableName($sqlFlagTree,$tokens,$tokensKey 1,$tableName);
 }
 else
 {
 foreach($$sqlFlagTreeas $fsql > $v)
 {
 if(eregi($flag,$tokens[$tokensKey]))
 {
 if(0==$v)
 {
 $tableName [ 'name'] = $tokens[$tokensKey];

 if(eregi($regxLeftWall,$tableName['name']))
 {
 $tableName[' leftWall'] = $ tableName['name']{0};
 }

 return true;
 }
 else{
 return self::findTableName($v,$ tokens,$tokensKey 1 ,& $tableName);
 }
 }
 }
 }

 回傳false;
 }
}
函數writeArrayToFile($fileName,$dataArray,$delimiter="rn")
{
 $handle=fopen($fileName, "wb");

 $text = '';

 foreach($dataArray as $data)
 {
 $text = $text.$data.$delimiter;
 }
 fwrite($handle,$ text);
 }
//測試
$dbM = new DBManager('localhost','w01f','123456','test');
$dbM->createFromFile(' data .sql',null,'fff_');
?>