如何利用PHP執行.SQL文件

本篇文章是對使用PHP執行SQL文件的實現代碼進行了詳細的分析介紹需要的朋友參考下  

　　demophp:

復制代碼 代碼如下:
<?php
/**
* 讀取 sql 文件並寫入數據庫
* @version demophp
*/
class DBManager
{
    var $dbHost = ;
    var $dbUser = ;
    var $dbPassword = ;
    var $dbSchema = ;

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

    function createFromFile($sqlPath$delimiter = (;/n)|((;/r/n))|(;/r)$prefix = $commenter = array(#))
    {
        //判斷文件是否存在
        if(!file_exists($sqlPath))
            return false;

        $handle = fopen($sqlPathrb);  

        $sqlStr = fread($handlefilesize($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 = "^[/`//"]{}[/_azAZ]+[/_azAZ]*[/`//"]{}$";//處理表名的正則表達式
            $regxLeftWall = "^[/`//"]{}";

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

                    );

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

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

                if(empty($tableName[leftWall]))
                {
                    $newTableName = $prefix$tableName[name];
                }
                else{
                    $newTableName = $tableName[leftWall]$prefixsubstr($tableName[name]);
                }

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

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

            $statement = $newStmt;
        }

        //用於測試      
        //var_dump($segment);
        //writeArrayToFile(datatxt$segment);
        //

        self::saveByQuery($segment);

        return true;
    }

    private function 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);
    }

    private function findTableName($sqlFlagTree$tokens$tokensKey=& $tableName = array())
    {
        $regxLeftWall = "^[/`//"]{}";

        if(count($tokens)<=$tokensKey)
            return false;      

        if( == trim($tokens[$tokensKey]))
        {
            return self::findTableName($sqlFlagTree$tokens$tokensKey+$tableName);
        }
        else
        {
            foreach($sqlFlagTree as $flag => $v)
            {  
                if(eregi($flag$tokens[$tokensKey]))
                {
                    if(==$v)
                    {
                        $tableName[name] = $tokens[$tokensKey];

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

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

        return false;
    }
}
function writeArrayToFile($fileName$dataArray$delimiter="/r/n")
{
    $handle=fopen($fileName "wb");

    $text = ;

    foreach($dataArray as $data)
    {
        $text = $text$data$delimiter;
    }
    fwrite($handle$text);
}
//測試
$dbM = new DBManager(localhostwftest);
$dbM>createFromFile(datasqlnullfff_);
?>

　　
datasql:
phpMyAdmin SQL Dump
version


主機: localhost
生成日期: 年 月 日 :
服務器版本:
PHP 版本:
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

數據庫: `newysh`



表的結構 `allowed`

CREATE TABLE `allowed` (
`bhash` blob NOT NULL
`bname` varchar() character set utf NOT NULL
PRIMARY KEY (`bhash`())
) ENGINE=MyISAM DEFAULT CHARSET=gb ROW_FORMAT=DYNAMIC;

導出表中的數據 `allowed`



表的結構 `allowed_ex`

CREATE TABLE `allowed_ex` (
`bhash` blob NOT NULL
`badded` datetime NOT NULL
`bsize` bigint() unsigned NOT NULL
`bfiles` int() unsigned NOT NULL
PRIMARY KEY (`bhash`())
) ENGINE=MyISAM DEFAULT CHARSET=gb ROW_FORMAT=DYNAMIC;

導出表中的數據 `allowed_ex`



表的結構 `category`

CREATE TABLE `category` (
`cid` int() unsigned NOT NULL auto_increment COMMENT 種子分類id
`name` varchar() NOT NULL COMMENT 分類名稱支持html格式
`sequence` int() unsigned NOT NULL COMMENT 顯示排序需要小的排在前面
PRIMARY KEY (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf AUTO_INCREMENT= ;

導出表中的數據 `category`

INSERT INTO `category` (`cid` `name` `sequence`) VALUES
( 音樂 )
( 學習資料 )
( 電影 );

注:對於phpmyadmin 生成的sql文件均適用

From:http://tw.wingwit.com/Article/program/PHP/201311/21281.html