[PHP] Mysql 导入数据库脚本

<?php

/**
 * 导入数据库脚本文件
 */
class DbImport
{
    
    // mysql数据库连接
    private $connInstanec;
    // 是否连接成功
    public $connOk;
    // 解析 - 在单引号里面
    private $parseInquote;
    // 解析 - sql语句语句结束分隔符号
    private $parseDelimiter;
    // 解析 - 分隔符长度
    private $parseDelimiterLength;
    // 解析 - 设置分隔符的语句关键字
    private $parseDeliKey;
    // 解析 - 关键字长度
    private $parseDeliKeyLength;

    private $parseFirst;

    private $fileHandle;

    private $parseVarFirst;

    private $parseVarRemaining;

    private $parseInComment;

    private $parseInCommentBegin;

    public function __construct ($host = null, $user = null, $pwd = null)
    {
        $this->connOk = false;
        $this->parseDeliKey = "DELIMITER";
        $this->parseVarFirst = true;
        $this->parseVarRemaining = '';
        $this->fileHandle = null;
        $this->parseInComment = false;
        $this->parseFirst = true;
        
        $this->parseDeliKeyLength = strlen($this->parseDeliKey);
        $this->connInstanec = @mysql_connect($host, $user, $pwd);
        $this->setParseDelimiter(';');
        
        if (! $this->connInstanec)
        {
            return;
        }
        
        $this->connOk = true;
    }

    public function __destruct ()
    {
        if ($this->connOk)
        {
            mysql_close($this->connInstanec);
        }
    }

    public function openFile ($file)
    {
        $this->fileHandle = fopen($file, 'r');
        
        if (! $this->fileHandle)
        {
            return false;
        }
        
        return true;
    }

    public function getNextSet ($rowCount = 1000)
    {
        if ($this->fileHandle === null)
        {
            return array(
                    "sqlset" => array(),
                    "count" => 0
            );
        }
        
        $tmpSql = "";
        $sqlset = array(
                "sqlset" => array(),
                "count" => 0
        );
        
        while (! feof($this->fileHandle))
        {
            $line = $this->parseVarRemaining . fgets($this->fileHandle);
            $linelen = strlen($line);
            $this->parseVarRemaining = '';
  
            if ($this->parseFirst)
            {
                $this->parseFirst = false;
                
                // 去除UTF8的BOM头:EF BB BF
                if (ord($line[0]) == 0xEF && ord($line[1]) == 0xBB && ord($line[2]) == 0xBF)
                {
                    $line = substr($line, 3);
                    $linelen -= 3;
                }
            } // first
            
            if (strncasecmp(trim($line), '-- ', 3) == 0)
            {
                continue;
            }
       
            if (0 == $linelen || '' == $line)
            {
                continue;
            }
            
            // 解析sql语句
            $parseArr = $this->parseSqlLine($line);
            
            // isBreak表示成功解析一条记录
            while ($parseArr['isBreak'])
            {
                $tmpSql .= $parseArr['break'];
                $tmpSql = trim($tmpSql);
                
                if ($tmpSql != "")
                {
                    $sqlset['sqlset'][] = $tmpSql;
                    $sqlset['count'] ++;
                }
                
                if ($sqlset['count'] >= $rowCount)
                {
                    $this->parseVarRemaining = $parseArr['remaining'];
                    return $sqlset;
                }
                
                $tmpSql = '';
                $parseArr = $this->parseSqlLine($parseArr['remaining']);
            } // while parse
            
            $tmpSql .= $parseArr['remaining'];
        } // while fileHandle
        
        //解析剩余的语句
        if ($this->parseVarRemaining != '')
        {
            $line = $this->parseVarRemaining;
            $parseArr = $this->parseSqlLine($line);
            
            // 解析sql语句 isBreak表示成功解析一条记录
            while ($parseArr['isBreak'])
            {
                $tmpSql .= $parseArr['break'];
                $tmpSql = trim($tmpSql);
                
                if ($tmpSql != "")
                {
                    $sqlset['sqlset'][] = $tmpSql;
                    $sqlset['count'] ++;
                }
                
                if ($sqlset['count'] >= 1000)
                {
                    $this->parseVarRemaining = $parseArr['remaining'];
                    return $sqlset;
                }
                
                $tmpSql = '';
                $parseArr = $this->parseSqlLine($parseArr['remaining']);
            } // while parse
            
            $tmpSql .= $parseArr['remaining'];
            
            if ($tmpSql != "")
            {
                $sqlset['sqlset'][] = $tmpSql;
                $sqlset['count'] ++;
            }
        }
        
        fclose($this->fileHandle);
        $this->fileHandle = null;
        
        return $sqlset;
    }

    public function import ($file)
    {
        
    } // public function import($file)

    private function setParseDelimiter ($delimiter)
    {
        if ($delimiter == null)
        {
            return;
        }
        
        $this->parseDelimiter = $delimiter;
        $this->parseDelimiterLength = strlen($delimiter);
    }

    public function parseSqlLine ($line)
    {
        if ('' == $line)
        {
            return array(
                    "isBreak" => 0,
                    "break" => '',
                    'remaining' => ''
            );
        }
        
        $len = strlen($line);
        
        for ($i = 0; $i < $len; $i ++)
        {
            if (! isset($line[$i]))
            {
                break;
            }
            
            if ("'" == $line[$i] && $line[$i - 1] != "\\")
            {
                $this->parseInquote = $this->parseInquote ? false : true;
                continue;
            }
            
            if ($this->parseInquote)
            {
                continue;
            }
            
            if (strtoupper(substr($line, $i, $this->parseDeliKeyLength)) == $this->parseDeliKey)
            {
                $newdelimiterInfo = $this->getNextCharsetInfo($line, $i + $this->parseDeliKeyLength);
                $this->setParseDelimiter($newdelimiterInfo['delimiter']);
                $line = substr($line, 0, $i) . substr($line, $newdelimiterInfo['endpos']);
                $len = $newdelimiterInfo['endpos'] - $i;
            }
            
            $cutDelimiter = substr($line, $i, $this->parseDelimiterLength);
            
            if ($this->parseDelimiter == $cutDelimiter)
            {
                return array(
                        "isBreak" => 1,
                        "break" => substr($line, 0, $i),
                        "remaining" => substr($line, $i + $this->parseDelimiterLength)
                );
            }
        }
        
        return array(
                "isBreak" => 0,
                "break" => '',
                'remaining' => $line
        );
    }

    /**
     * 获取字符串集
     *
     * @param string $str            
     * @param number $index            
     * @return string
     */
    private function getNextCharsetInfo ($str, $index = 0)
    {
        $spacelist = " \t\n\r";
        $len = strlen($str);
        $begin = 0;
        $space = true;
        
        for ($i = $index; $i < $len; $i ++)
        {
            if ($space && $this->inString($str[$i], $spacelist))
            {
                $begin = $i;
                continue;
            }
            else
            {
                if ($space)
                {
                    $begin = $i;
                    $space = false;
                }
            }
            
            if ($this->inString($str[$i], $spacelist))
            {
                return array(
                        "delimiter" => substr($str, $begin, $i - $begin),
                        "endpos" => $i
                );
            }
        }
        
        return array(
                "delimiter" => substr($str, $begin),
                "endpos" => $len
        );
    }

    private function inString ($char, $str)
    {
        $len = strlen($str);
        
        for ($i = 0; $i < $len; $i ++)
        {
            if ($char == $str[$i])
            {
                return true;
            }
        }
        
        return false;
    }

    private function isSpaceChar ($char)
    {
        if ($char == null)
        {
            return true;
        }
        
        $spacelist = " \t\n\r";
        return $this->inString($char, $space);
    }
}


echo "<pre>";
$db = new DbImport('localhost', 'root', 'password');
$db->openFile('d.sql');

$all = 0;
$success = 0;

set_time_limit(0);
mysql_query("set sql_mode=''");

do
{
    $sqlArray = $db->getNextSet();

    foreach($sqlArray['sqlset'] as $i => $sql)
    {
        $all ++ ;
        
        if(mysql_query($sql))
        {
            $success ++;
        }
        else
        {
            echo "## Fail SQL:$sql<br>## Fail Info:".mysql_error()."<br>## Fail Code:".mysql_errno();
            echo "<br>##############################################<br><br><br><br>";
        }
    }
    
} while($sqlArray['count'] > 0);

echo "All Query:$all<br>";
echo "Success:$success<br>";
echo "Fail:".($all-$success);

转载于:https://my.oschina.net/u/2267381/blog/367430