PHP实例以sql文件导入导出mysql数据库

记得前面我讲过PHP备份mysql数据库的二种方法。一种是在本地操作的，另外一种是在phpmyadmin中操作的。但是今天使用PHP代码来实现以sql文件导入导出mysql数据库，这在后台尤其重要。

下面是PHP代码：

导入
LOAD DATA INFILE 'c:/name.txt' INTO TABLE zones
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\n';
*/

define(HOST,'127.0.0.1');
define(USER,'user');
define(PWD,'pwd');
define(DBNAME,'dbname');
define(DIR,'C:/xampp/htdocs/backup/');
//1为导入 0为导出
define(OPERATION,1);

mysql_connect(HOST,USER,PWD) or
die("Could not connect: " . mysql_error());
mysql_select_db(DBNAME) or
die("Could not select db: " . mysql_error());
$result = mysql_query("show tables");

if(!is_dir(DIR)){
die('Folder does not exist');
}
if(OPERATION){
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$fileName=DIR.$row[0].".txt";
if(file_exists($fileName)){
$querySql='TRUNCATE TABLE `'.$row[0].'`';
mysql_query($querySql) or
die("Could not truncate table: ". $querySql . mysql_error());

$querySql = "LOAD DATA INFILE '".$fileName."'
INTO TABLE `".$row[0]."`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\n'";
mysql_query($querySql) or
die("Could not insert into table: ". $querySql . mysql_error());
echo 'insert into table '.$row[0].' success.
';
}else{
echo 'Not insert into table '.$row[0].'
';
}
}
echo "The task is finished";
}else{
if (is_writable(DIR)) {
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$fileName=DIR.$row[0].".txt";
if(file_exists($fileName)){
unlink($fileName);
}
$querySql = "SELECT * INTO OUTFILE '".$fileName."'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\n'
FROM `".$row[0]."`";
mysql_query($querySql) or
die("Could not dump table: ". $querySql . mysql_error());
echo 'dump table '.$row[0].' success.
';
}
echo "The task is finished";
}else{
echo 'Folder '.DIR.' can not writeable';
}
}

mysql_free_result($result);
mysql_close();
?>

在网站后台实现点击一个按钮就可以导出整个mysql数据库，并且还是以sql文件导出的。以后恢复的时候只要将这个文件导入到数据库中就行了。呵呵，很多人都在找这样的代码呢！

猜你喜欢