如何利用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文件均适用