如何利用PHP執行.SQL文件

2020-03-22 19:09:45

字體：大中小

來源：轉載

供稿：網友

demo.php:
復制代碼代碼如下:
?php
/**
* 讀取 sql 文件并寫入數據庫
* @version 1.01 demo.php
*/
html' target='_blank'>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 = "^[/`/'/"]{1}";

$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 = "^[/`/'/"]{1}";

if(count($tokens) =$tokensKey)
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_');
?

data.sql:
-- phpMyAdmin SQL Dump
-- version 2.11.3
-- http://www.phpmyadmin.net
--
-- 主機: localhost
-- 生成日期: 2008 年 08 月 20 日 12:09
-- 服務器版本: 5.0.51
-- PHP 版本: 5.2.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- 數據庫: `newysh`
--
-- --------------------------------------------------------
--
-- 表的結構 `allowed`
--
CREATE TABLE `allowed` (
`bhash` blob NOT NULL,
`bname` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`bhash`(20))
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 ROW_FORMAT=DYNAMIC;
--
-- 導出表中的數據 `allowed`
--
-- --------------------------------------------------------
--
-- 表的結構 `allowed_ex`
--
CREATE TABLE `allowed_ex` (
`bhash` blob NOT NULL,

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。

上一篇：php腳本運行時的超時機制詳解

下一篇：php等比例縮放圖片及剪切圖片代碼分享