PHP读取SQL文件并导入数据库(示例)

php中文网

发布： 2016-07-25 08:55:00

原创

1678人浏览过

/**
* 读取 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,$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($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;
}
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=0,& $tablename = array())
{
$regxleftwall = "^[\`\'\"]发达";
if(count($tokens) return false;
if('' == trim($tokens[$tokenskey]))
{
return self::findtablename($sqlflagtree,$tokens,$tokenskey+1,$tablename);
}
else
{
foreach($sqlflagtree as $flag => $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);
}
}
}
}
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('localhost','w01f','123456','test');
$dbM->createFromFile('data.sql',null,'fff_');
?>

复制代码