最近工作中，遇到了需要对数据库进行备份和还原的功能，然后上网查找下参考思路，自行手动封装了一个类，使用的是PHP的‘PDO’类来连接数据库。
代码如下：
<?php
/**
* 数据库备份还原类
* Class Date_backup_restore
*/
class Date_backup_restore
{
private $config = array(
//连接数据库服务器的地址
"db_host" => "localhost",
//连接数据库服务器的用户
"db_user" => "root",
//连接数据库服务器的密码
"db_pass" => "123456",
//连接数据库服务器的类型
"db_type" => "mysql",
//连接数据库服务器的端口
"db_port" => 3306,
//数据库编码默认采用utf8
"db_charset" => "utf8",
//连接数据库的名称
"db_name" => "",
//数据库连接类型（是否是长连接）
"db_link_type" => false,
//连接dsn,数据源名称或叫做 DSN，包含了请求连接到数据库的信息
"dsn" => "",
);
//数据库连接对象
private $conn;
//数据源名称或叫做 DSN，包含了请求连接到数据库的信息。
private $dsn;
public function __construct($C = array(), $fn = null)
{
error_reporting(5);//1+4
date_default_timezone_set("Asia/Shanghai");
//$args = func_get_args();
//call_user_func_array(array($this, 'initialize'), $args);
//数据库连接配置
if (is_array($C)) {
foreach ($C as $key => $item) {
$this->config[$key] = $item;
}
}
//拼接 dsn
if ($this->config['dsn']) {
$this->dsn = $this->config['dsn'];
} elseif (strtolower($this->config['db_type']) === 'mysql') {
$this->dsn = "{$this->config['db_type']}:host={$this->config['db_host']};dbname={$this->config['db_name']};port={$this->config['db_port']}";
} else {
die('数据库连接信息不存在！');
}
//连接数据库
try {
$this->conn = new PDO($this->dsn, $this->config['db_user'], $this->config['db_pass'], array(PDO::ATTR_PERSISTENT => $this->config['db_link_type']));
} catch (Exception $exception) {
isset($fn) and is_callable($fn) and ($fn($exception));
}
if (in_array(strtolower($this->config['db_type']), array('mysql'))) {
//设置数据库编码
$this->conn->query('SET NAMES ' . $this->config['db_charset']);
}
return $this;
}
/**
* 获取数据库信息
* @return string
*/
public function get_mysql_info()
{
$line = '-- ----------------------------';
$desc = $line . "\n";
$desc .= "-- 主机：{$this->config['db_host']}:{$this->config['db_port']}\n";
$desc .= "-- 生成日期：" . date("Y-m-d H:i") . "\n";
$desc .= "-- MySQL版本：" . self::fetch("select version()", '', PDO::FETCH_NUM)[0] . "\n";
$desc .= "-- 数据库：{$this->config['db_name']}\n";
$desc .= "$line\n\n";
$desc .= "SET FOREIGN_KEY_CHECKS=0;\n\n";
return $desc;
}
/**
* 获取创建表的SQL语句
* @param string $tableName
* @param array $all_table_structure 所有表结构,二维数组
* @return array
*/
public function get_table_structure($tableName = '', $all_table_structure = '')
{
$args = func_get_args();
$line = '-- ----------------------------';
$sql = '';
//是否是视图表
$is_view = 0;
if(is_array($all_table_structure) && count($all_table_structure)>0){
$args[2] && ($all_table_structure = self::lv2_reset($all_table_structure,'table_name'));
$data = $all_table_structure[$tableName];
$data[1] = $data['sql'];
$data['is_view'] == 1 && ($data['View'] = $data['table_name']);
}else{
//获取创建表的sql语句
$re = $this->conn->query("SHOW CREATE TABLE `$tableName`");
//$re = $this->conn->query("SHOW CREATE TABLE `$tableName`",PDO::FETCH_ASSOC);
if (self::isError()) {
die("错误！可能数据表[$tableName]不存在");
}
$data = $re->fetch();
}
if ($data['View']) {
$sql .= "{$line}\n";
$sql .= "-- 视图表的结构 {$tableName}\n";
$sql .= "{$line}\n";
// 如果存在则删除视图表
$sql .= "DROP VIEW IF EXISTS `$tableName`; \n";
$sql .= $data[1];
$sql .= ";\n\n";
$is_view = 1;
} else {
$sql .= "{$line}\n";
$sql .= "-- 表的结构 {$tableName}\n";
$sql .= "{$line}\n";
// 如果存在则删除表
$sql .= "DROP TABLE IF EXISTS `$tableName`; \n";
$sql .= $data[1];
$sql .= ";\n\n";
$sql .= "{$line}\n";
$sql .= "-- 转存表中的数据 $tableName \n";
$sql .= "{$line}\n";
$is_view = 0;
}
return ['sql' => $sql, 'is_view' => $is_view];
}
/**
* 获取当前数据库所有表的创建sql语句
* @return mixed|null|array
* @throws Exception
*/
public function all_table_structure()
{
$temp = [];
//获取当前数据库里面所有的表名称
$table_list = self::query("SHOW TABLES", '', PDO::FETCH_NUM);
foreach ($table_list as $index => $v) {
$tableName = $v[0];
//获取创建表的sql语句
$re = $this->conn->query("SHOW CREATE TABLE `$tableName`");
$data = $re->fetch();
$is_view = $data['View'] ? 1 : 0;
$temp[] = ['table_name' => $tableName, 'sql' => $data[1], 'is_view' => $is_view];
}
return self::sortArrByManyField($temp, 'is_view', SORT_ASC);
}
/**
* 插入一条记录
* @param string $tableName
* @param int $field_num 表里面字段的数量
* @param array $record 记录数据（一维数组）
*/
public function insert_record($tableName = '', &$field_num = 0, $record = [])
{
if (intval($field_num) < 1) {
$re = $this->conn->query("SHOW COLUMNS FROM `$tableName`", PDO::FETCH_ASSOC);
if (self::isError()) {
die("错误！可能数据表[$tableName]不存在");
}
$field_num = count($re->fetchAll());
}
for ($i = 0, $v = []; $i < $field_num; $i++) {
$v[] = $this->quote($record[$i]);
}
$insert = "INSERT INTO `$tableName` VALUES (" . join(',', $v) . ");\n";
return $insert;
}
/**
* 导出
* @param string $tableName
* @param string|array $all_table_structure
* @return string
*/
public function _export($tableName = '', $all_table_structure = '')
{
//导出单张表
if (self::is_string($tableName)) {
$ts = self::get_table_structure($tableName, $all_table_structure);
$sql = $ts['sql'];
if ($ts['is_view'] === 0) {
//插入表的数据
$data_list = self::query("SELECT * FROM `$tableName`", '', PDO::FETCH_NUM);
foreach ($data_list as $key => $v) {
$sql .= self::insert_record($tableName, $field_num, $v);
}
}
return $sql . "\n\n";
} else {
//获取数据库里面的表名称
$table_list = self::lv2_reset(self::all_table_structure(),'table_name');
$sql = '';
foreach ($table_list as $k => $v) {
$sql .= self::_export($v['table_name'], $table_list);
}
return $sql . "\n";
}
}
/**
* 导出sql文件
* @param string $tableName
* @param string $fileName
* @param string $dir_path
* @param boolean|int $is_downlod
*/
public function export($tableName = '', $fileName = '', $dir_path = '/', $is_downlod = false)
{
self::is_string($tableName) && !self::is_string($fileName) && ($fileName = $tableName . '.sql');
!self::is_string($tableName) && !self::is_string($fileName) && ($fileName = $this->config['db_name'] . '.sql');
if (strpos($dir_path, '/') === 0) {
$dir_path = rtrim(getcwd() . '\\/');
if ($_SERVER['DOCUMENT_ROOT']) {
$dir_path = rtrim($_SERVER['DOCUMENT_ROOT'], "\t\n\r\0\x0B\\/");
}
}
$sql = self::get_mysql_info();
$sql .= self::_export($tableName);
//$sql .= "\n\nSET FOREIGN_KEY_CHECKS=1;";
if($is_downlod){
self::download($sql,preg_replace('/\.sql$/i','',$fileName));
}else{
self::file_write("$dir_path/$fileName", $sql);
}
}
/**
* 导入sql文件，返回sql语句数组，一维数组
* @param string $file_path
* @return array|bool
*/
public function _import($file_path = '')
{
if (self::is_string($file_path) && is_file($file_path)) {
//sql语句数组
$sql_list = array();
//缓存创建表的sql语句
$create = '';
$condition = array();
$f = fopen($file_path, 'r');
if ($f) {
while (!feof($f)) {
$str = trim(fgets($f));
//包含注释（如‘-- ’）或空白行跳过
if ($str == '' || preg_match("/^-- /", $str)) {
continue;
}
//表结构部分
if(!preg_match('/;$/',$str) || ($condition[0]=preg_match('/ENGINE=/',$str))){
$create .= $str;
//如果包含了创建表的最后一句
if($condition[0]){
//并入到sql数组
$sql_list[] = $create;
//清空当前缓存
$create = '';
}
continue;
}
$sql_list[] = $str;
}
}
fclose($f);
return $sql_list;
}
return false;
}
/**
* sql导入辅助
* @param string|string[] $file_path
* @return bool
*/
public function _import_($file_path = '')
{
if (self::is_string($file_path)) {
$sql_list = self::_import($file_path);
$msg = ['length' => count($sql_list), 'data' => [], 'code' => 0];
if (is_array($sql_list) && count($sql_list) > 0) {
foreach ($sql_list as $index => $sql) {
$re = self::exec($sql);
if (self::isError()) {
self::dump(self::getError([]));
$msg['code'] = 0;
$msg['data'][] = ["sql" => mb_substr($sql, 0, 100), "msg" => "失败", "code" => 0];
break;
}else{
$msg['code'] = 1;
$msg['data'][] = ["sql" => mb_substr($sql, 0, 100), "msg" => "成功", "code" => 1];
}
}
}
return $msg;
} else {
$msg_list = [];
if (is_array($file_path) && count($file_path) > 0) {
foreach ($file_path as $index => $v) {
self::is_string($v) && ($msg_list[] = self::_import_($v));
}
}
return $msg_list;
}
}
/**
* sql导入
* @param string|string[] $file_path
* @throws Exception
*/
public function import($file_path)
{
/*$table_list = self::all_table_structure();
foreach ($table_list as $index => $item) {
if ($item['is_view'] === 0) {
self::lock($item['table_name']);
}
}*/
$re = self::_import_($file_path);
if($re['code'] === 1){
self::dump('当前sql文件导入成功');
}else{
self::dump('当前sql文件导入失败');
}
}
/**
* 文件写入方式
* @param string $path
* @param string $content
* @param string $mode [r,r+]（读取文件） [w,w+]（覆盖写入文件） [a,a+]（向文件末尾写入）
*/
public function file_write($path = '', $content = '', $mode = 'w+')
{
//打开文件
//$f = fopen($path, $mode) or die("Unable to open file!");
$f = fopen($path, $mode);
if(!$f){die("无法打开文件！");}
//写入内容
$fw = fwrite($f, $content);
if(!$fw){die("写入文件失败，请查看文件是否可写");}
//关闭文件
$fc = fclose($f);
if(!$fc){die("关闭文件失败！");}
}
public function exec($sql)
{
$re = $this->conn->exec($sql);
if ($this->conn->errorCode() != '00000') {
return false;
}
return $re; //影响的行数
}
/**
* @param string $sql
* @param callable|string $error_fn
* @return array|bool
*/
public function query($sql, $error_fn = '', $mode = PDO::FETCH_ASSOC)
{
$object = $this->conn->query($sql, $mode);
if ($this->conn->errorCode() != '00000') {
isset($error_fn) and is_callable($error_fn) and $error_fn($object);
return false;
}
if (is_object($object)) {
return $object->fetchAll();
} else {
return false;
}
}
/**
* @param string $sql
* @param string|callable $error_fn
* @param int $mode
* @return bool|mixed
*/
public function fetch($sql = '', $error_fn = '', $mode = PDO::FETCH_ASSOC)
{
$re = $this->conn->query($sql, $mode);
if ($this->isError()) {
is_callable($error_fn) && $error_fn($re);
return false;
}
return $re->fetch();
}
public function close(){
$this->conn = null;
}
/**
* 锁定数据表
* @param $tableName
* @param string $op
* @return bool
*/
private function lock($tableName,$op = 'WRITE'){
return !!self::exec("lock tables `$tableName` $op");
}
/**
* 解锁数据表
* @return bool
*/
private function unlock(){
return !!self::exec('unlock tables');
}
public function quote($string = '', $type = PDO::PARAM_STR)
{
return $this->conn->quote($string, $type);
}
/**
* sql语句操作数据库是否出错
* @return boolean (true=出错，false=未出错)
*/
public function isError()
{
return $this->conn->errorCode() != '00000';
}
/**
* 获取错误信息
* @param $type integer (0=获取错误码,1=数据库错误码,2=错误描述)
* @return string|int|array
*/
public function getError($type = 0)
{
$err = $this->conn->errorInfo();
return in_array($type, [0, 1, 2]) ? $err[$type] : $err;
}
/**
* 是否是字符串
* @param string|int|double $str
* @return bool
*/
public function is_string($str = '')
{
return !in_array($str, array('', null, false), true) && (is_string($str) || is_numeric($str));
}
/**
* 判断是否存在
* @param $var
* @param array $opt 这里的值代表了不存在的意思
* @return bool
*/
public function is_exist($var, $opt = array("", null, false))
{
isset($var) || ($var = false);
return !in_array($var, $opt, true);
}
public function is_number($arg)
{
return is_int($arg) || is_float($arg);
}
/**
* 获取变量值
* @return bool|*
*/
public function get_vars()
{
$args = func_get_args();
$var = false;
foreach ($args as $arg) {
if (!in_array($arg, array("", null, false), true)) {
$var = $arg;
break;
}
}
return $var;
}
/**
* 浏览器友好的变量输出
* @param mixed $var 变量
* @param boolean $echo 是否输出 默认为True 如果为false 则返回输出字符串
* @param string $label 标签 默认为空
* @param boolean $strict 是否严谨 默认为true
* @return void|string
*/
public function dump($var, $echo = true, $label = null, $strict = true)
{
$label = ($label === null) ? '' : rtrim($label) . ' ';
if (!$strict) {
if (ini_get('html_errors')) {
$output = print_r($var, true);
$output = '<pre>' . $label . htmlspecialchars($output, ENT_QUOTES) . '</pre>';
} else {
$output = $label . print_r($var, true);
}
} else {
ob_start();
var_dump($var);
$output = ob_get_clean();
if (!extension_loaded('xdebug')) {
$output = preg_replace('/\]\=\>\n(\s+)/m', '] => ', $output);
$output = '<pre>' . $label . htmlspecialchars($output, ENT_QUOTES) . '</pre>';
}
}
if ($echo) {
echo($output);
return null;
} else {
return $output;
}
}
/**
* 强制下载
* @param string $content
* @param string $filename
* @param string $file_extension
*/
public function download($content = '', $filename = '', $file_extension = 'sql')
{
$conf = [
'pdf' => 'application/pdf',
'txt' => 'application/txt',
'exe' => 'application/octet-stream',
'zip' => 'application/zip',
'all' => 'application/force-download',
'sql' => 'application/force-download',
];
$ctype = $conf[$file_extension];
$len = strlen($content);
//Begin writing headers
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public");
header("Content-Description: File Transfer");
//Use the switch-generated Content-Type
header("Content-Type: $ctype");
$filename = "$filename.$file_extension";
//Force the download
$header = "Content-Disposition: attachment; filename=" . $filename . ";";
header($header);
header("Content-Transfer-Encoding: binary");
header("Content-Length: " . $len);
ob_clean();
flush();
//@readfile($file);
echo $content;
exit;
}
/**
* 数组排序 使用例如： self::sortArrByManyField($lv2_array,'id',SORT_DESC,'field2',SORT_ASC)
* @return mixed|null
* @throws Exception
*/
public function sortArrByManyField()
{
$args = func_get_args();
if (empty($args)) {
return null;
}
$arr = array_shift($args);
if (!is_array($arr)) {
throw new Exception("第一个参数不为数组");
}
foreach ($args as $key => $field) {
if (is_string($field)) {
$temp = array();
foreach ($arr as $index => $val) {
$temp[$index] = $val[$field];
}
$args[$key] = $temp;
}
}
$args[] = &$arr;//引用值
call_user_func_array('array_multisort', $args);
return array_pop($args);
}
private function lv2_search($lv2_array = '', $field = '', $options = '==', $value = '', $get_field = '')
{
if (is_array($lv2_array) && count($lv2_array) > 0) {
if (is_callable($field)) {
foreach ($lv2_array as $k => $v) {
if (in_array($field(), [1, true, '1'], true)) {
$new_lv2_array[] = $v;
self::is_string($get_field) && ($lv1_array_field[] = $v[$get_field]);
}
}
return $get_field ? $lv1_array_field : $new_lv2_array;
} else {
foreach ($lv2_array as $k => $v) {
if (self::is_string($field) && in_array($options, ['=', '==']) && self::is_exist($value) && $v[$field] == $value) {
$new_lv2_array[] = $v;
self::is_string($get_field) && ($lv1_array_field[] = $v[$get_field]);
} elseif (self::is_string($field) && strtolower($options) == 'regexp' && self::is_string($value) && preg_match("/$value/i", $v[$field])) {
$new_lv2_array[] = $v;
self::is_string($get_field) && ($lv1_array_field[] = $v[$get_field]);
} else {
if (!self::is_exist($value) && self::is_string($field) && self::is_exist($options) && $v[$field] == $options) {
$new_lv2_array[] = $v;
self::is_string($get_field) && ($lv1_array_field[] = $v[$get_field]);
}
}
}
return $get_field ? $lv1_array_field : $new_lv2_array;
}
}
return false;
}
private function lv2_reset($lv2_array = '', $field = 'id')
{
$new_array = array();
if(is_array($lv2_array) && count($lv2_array)>0){
foreach ($lv2_array as $k => $v){
$new_array[$v[$field]] = $v;
}
}
return $new_array;
}
function __destruct()
{
// TODO: Implement __destruct() method.
self::unlock();
self::close();
}
}
/*连接MySQL数据库*/
/*$dbr = new Date_backup_restore([
//连接数据库服务器的地址
"db_host" => "localhost",
//连接数据库服务器的用户
"db_user" => "root",
//连接数据库服务器的密码
"db_pass" => "123456",
//连接数据库的名称
"db_name" => "dbname",
]);*/
/*导出数据为sql文件，浏览器下载*/
//$dbr->dump($dbr->export('','','/',1));
/*导出数据为sql文件保存*/
//$dbr->dump($dbr->export('','','/'));
//$dbr->dump($dbr->all_table_structure());
//$dbr->get_mysql_info();
/*导入sql文件到数据库*/
//$dbr->import(getcwd().'/hnspcy.com.sql');
我的gitee代码链接: https://gitee.com/fang_wen_feng/my_php_plugCode/blob/phpcode/phpCode/Data_backup_restore.php