基于ThinkPHP5的数据库表导入导出类

具体的实现思路和方式参考了：https://github.com/tp5er/tp5-databackup

原本是打算直接拿这个来用的，但因为我的业务需求比较简单，原本的类用起来稍有些臃肿，于是我根据自己的需求重写了这个类。

目前的功能有：备份数据库、还原备份的数据库、获取现有备份文件列表、删除指定备份文件。

 

// +----------------------------------------------------------------------
// | 基于ThinkPHP5的数据库表导入导出类
// +----------------------------------------------------------------------
// | 当前版本：1.0.0 
// +----------------------------------------------------------------------
// | 作者：何效名
// +----------------------------------------------------------------------

namespace app\index\common;
use think\Controller;
use think\Db;

class BackupSql extends Controller
{
    private $dbConfig = array();
    private $path = '';
    private $table = '';
    private $file = null;
    
    public function __construct($path)
    {
        parent::__construct();
        
        $this -> dbConfig = config('database');
        if(!$this -> checkPath($path))
        {
            echo '创建目录结构失败';
            die();
        }
        else
        {
            $this -> path = rtrim($path, '/');
        }
    }
    
    public function export($table)
    {
        $db = Db::connect();
        $table = $this -> dbConfig['prefix'] . strtolower($table);
        $this -> table = trim($table, '/');
        
        $sql = "-- -----------------------------\n";
        $sql .= "-- Xmsb & ThinkPHP —— MySql Transfer \n";
        $sql .= "-- \n";
        $sql .= "-- Host     : " . $this -> dbConfig['hostname'] . "\n";
        $sql .= "-- Database : " . $this -> dbConfig['database'] . "\n";
        $sql .= "-- \n";
        $sql .= "-- Date : " . date("Y-m-d H:i:s") . "\n";
        $sql .= "-- -----------------------------\n\n";
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
        if(false === $this -> writeSql($sql)) 
        {
            return false;
        }
        
        $result = $db -> query("SHOW CREATE TABLE `{$table}`");
        $sql = "\n";
        $sql .= "-- -----------------------------\n";
        $sql .= "-- Table structure for `{$table}`\n";
        $sql .= "-- -----------------------------\n";
        $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
        $sql .= trim($result[0]['Create Table']) . ";\n\n";
        if(false === $this -> writeSql($sql)) 
        {
            return false;
        }
        
        $result = $db -> query("SELECT COUNT(*) AS count FROM `{$table}`");
        $count = $result['0']['count'];
        
        if($count) 
        {
            $sql = "-- -----------------------------\n";
            $sql .= "-- Records of `{$table}`\n";
            $sql .= "-- -----------------------------\n";
            $this -> writeSql($sql);
            
            $result = $db -> query("SELECT * FROM `{$table}`");
            foreach ($result as $row) 
            {
                $row = array_map('addslashes', $row);
                $sql = "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\\r', '\\n'), implode("', '", $row)) . "');\n";
                if (false === $this -> writeSql($sql)) 
                {
                    return false;
                }
            }
        }
        
        fclose($this -> file);
        $this -> file = null;
        
        return '1000';
    }
    
    public function import($file)
    {
        if(substr(strrchr($file, '.'), 1) != 'sql')
        {
            return '文件类型不符合要求';
        }
        if(!file_exists($fullPath = $this -> path . '/' . $file))
        {
            return '指定的sql文件不存在';
        }
        
        $db = Db::connect();
        $sql = '';
        $gz = fopen($fullPath, 'r');
        
        while(1 == 1)
        {
            $sql .= fgets($gz);
            if(preg_match('/.*;$/', trim($sql))) 
            {
                if(false === $db -> execute($sql)) 
                {
                    return false;
                }
                
                $sql = '';
            }
            
            if(feof($gz)) break;
        }
        
        return '1000';
    }
    
    public function fileList()
    {
        $flag = \FilesystemIterator::KEY_AS_FILENAME;
        $glob = new \FilesystemIterator($this -> path, $flag);
        
        $list = array();
        foreach($glob as $name => $file)
        {
            $fileSplit = explode('-', $name);
            if(substr(strrchr($name, '.'), 1) == 'sql' && strlen(end($fileSplit)) == '18') $list[] = $name;
        }
        
        return $list;
    }
    
    public function fileDelete($file)
    {
        $fullPath = $this -> path . '/' . $file;
        
        if(file_exists($fullPath))
        {
            unlink($fullPath);
            return '1000';
        }
        else
        {
            return '文件不存在';
        }
    }
    
    protected function checkPath($path)
    {
        if(is_dir($path) || mkdir($path, 0755, true)) 
        {
            return true;
        } 
        else 
        {
            return false;
        }
    }
    
    protected function writeSql($sql)
    {
        $this -> openFile();
        
        return fwrite($this -> file, $sql);
    }
    
    protected function openFile()
    {
        if($this -> file === null)
        {
            $fullPath = $this -> path . '/' . $this -> table . '-' . date('YmdHis') . '.sql';
            $this -> file = fopen($fullPath, 'a');
        }
    }
}

 

 

使用方式：

// 实例化 参数1为存放备份sql的路径
$backup = new BackupSql('./backupSql');

// 执行备份 参数1为数据表的表名，不加表前缀（表前缀需在thinkphp5的database.php中进行配置）
$backup -> export('table_name');

// 获取备份文件列表 文件路径为实例化时传入的参数
$backup -> fileList();

// 还原数据 参数1为备份后的文件名
$backup -> import('file_name');

// 删除备份文件 参数1为备份后的文件名
$backup -> fileDelete('file_name');