Thinkphp数据库导出导入备份等操作

有个需求要做这样的单子，

但是找了https://github.com/pkg6/tp5-databackup

又不能使用，是有BUG 导出表的时候表数据并没有导出只有导出了表结构

在网上找了个大佬的自己写的类 然后自己结合了一下

放到index模块就把namespace app\admin\controller 里面的admin换成index

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

namespace app\admin\controller;

use think\Controller;
use think\Db;
use Config;

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;
    }

    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');
        }
    }
}

表列表还是需要用刚刚上面用的这个类

composer require tp5er/tp5-databackup

然后列表效果是这样的

图中导出的方法是下面的方法

在其他控制器中引用需要引入BackupSql

导出某张数据库表并且下载

我这里下载了就删除了因为原来并没有返回这个文件信息，所以我这边的读了目录下所有的文件

下载然后删除，因为每次只是点击了一个导出

**
     * @description: 导出数据表
     * @Date: 2023-03-02 12:42:02
     * @Author: MoSheng
     */    public function datatableout()
    {
        $backup = new BackupSql('./backupSql');
        $file = $backup->export(input('name'));
        $arrFiles = array();
        $handle = opendir('./backupSql');
        if ($handle) {
            while (($entry = readdir($handle)) !== FALSE) {
                $arrFiles[] = $entry;
            }
        }
        closedir($handle);

        $type = filetype($arrFiles[2]);
        $filename = $arrFiles[2];
        $filepath = './backupSql/' . $filename;
        header("Content-type: $type");
        header("Content-Disposition: attachment;filename=$filename");
        header("Content-Transfer-Encoding: binary");
        header('Pragma: no-cache');
        header('Expires: 0');
        // 发送文件内容
        set_time_limit(0);

        readfile($filepath);
        unlink($filepath);
        //下载文件

    }

然后是还原 这里写了一个上传的方法，中间没有判断文件类型之类的，正式场景中请慎用

/**
     * @description: 上传数据表
     * @Date: 2023-03-02 12:42:17
     * @Author: MoSheng
     */    public function uploaddatatable()
    {
        $backup = new BackupSql('./uploads');
        // 获取表单上传文件 例如上传了001.jpg
        $file = request()->file('sql');

        // 移动到框架应用根目录/uploads/ 目录下
        $info = $file->move('./uploads', '');
        if ($info) {
            $filepath =  './uploads/' . $info->getFilename();
            $backup->import($info->getFilename());
            $this->success('导入成功');
        } else {
            // 上传失败获取错误信息
            echo $file->getError();
        }
    }