Controller文件：
<?php
// +----------------------------------------------------------------------
// | snake
// +----------------------------------------------------------------------
// | Copyright (c) 2016~2022 http://baiyf.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: NickBai <1902822973@qq.com>
// +----------------------------------------------------------------------
namespace app\admin\controller;
use ZipArchive;
class Data extends Base
{
// 备份首页列表
public function index()
{
$tables = db()->query('show tables');
$Table = [];
foreach($tables as $key=>$vo){
$Table[] = $vo['Tables_in_' . config('database')['database']];
$sql = "select count(0) as alls from " . $vo['Tables_in_' . config('database')['database']];
$tables[$key]['alls'] = db()->query($sql)['0']['alls'];
$table = $vo['Tables_in_' . config('database')['database']];
$tables[$key]['operate'] = showOperate($this->makeButton($table));
/* filemtime返回上次修改的时间 */
if(file_exists(config('back_path') . $vo['Tables_in_' . config('database')['database']] . ".sql")){
$tables[$key]['ctime'] = date('Y-m-d H:i:s', filemtime(config('back_path') . $vo['Tables_in_' .
config('database')['database']] . ".sql"));
}else{
$tables[$key]['ctime'] = '无';
}
}
$strTable = join(",",$Table); //转换为字符串拼接,
$strTableBr = join("<br>",$Table); //转换为字符串拼接换行符
$tableBtn = showOperate($this->makeButtonAll($strTable)); //生成按钮
$tableAll = [
'Tables_in_' . config('database')['database'] => $strTableBr,
'alls' => count($Table),
'ctime' => date('Y-m-d H:i:s',filemtime(config('back_path') .session('zipFile'))),
'operate' => $tableBtn
];
$this->assign([
'tables' => $tables,
'tableAll' => $tableAll,
]);
return $this->fetch();
}
// 备份数据
public function importData()
{
set_time_limit(0);
$table = input('param.table');
$sqlStr = "/*\r\nDate：" . date('Y/m/d H:i:s',time()) . "\r\nUrl：http://www.llggo.com\r\nTable：" . $table . "\r\nCopyRight：HanYu\r\n*/\r\n";
$sqlStr .= "SET FOREIGN_KEY_CHECKS=0;\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$sqlStr .= "-- Table structure for `$table`\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$sqlStr .= "DROP TABLE IF EXISTS `$table`;\r\n";
$create = db()->query('show create table ' . $table);
$sqlStr .= $create['0']['Create Table'] . ";\r\n";
$sqlStr .= "\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$sqlStr .= "-- Records of `$table`\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$result = db()->query('select * from ' . $table);
foreach($result as $key => $vo){
$keys = array_keys($vo);
$keys = array_map('addslashes', $keys);
$keys = join('`,`', $keys);
$keys = "`" . $keys . "`";
$vals = array_values($vo);
$vals = array_map('addslashes', $vals);
$vals = join("','", $vals);
$vals = "'" . $vals . "'";
$sqlStr .= "insert into `$table`($keys) values($vals);\r\n";
}
$filename = config('back_path') . $table . ".sql";
$fp = fopen($filename, 'w');
fputs($fp, $sqlStr);
fclose($fp);
return json(['code' => 1, 'data' => '', 'msg' => '文件备份在【back】目录下，文件名为：【' . $table . '.sql】']);
}
// 还原数据
public function backData()
{
set_time_limit(0);
$table = input('param.table');
if(!file_exists(config('back_path') . $table . ".sql")){
return json(['code' => -1, 'data' => '', 'msg' => '备份数据不存在!']);
}
$sqls = analysisSql(config('back_path') . $table . ".sql");
foreach($sqls as $key=>$sql){
db()->query($sql);
}
return json(['code' => 1, 'data' => '', 'msg' => '数据库表【' . $table .'】']);
}
// 备份数据
public function importDataAll()
{
set_time_limit(0);
$table = input('param.table');
$table = explode(',',$table);
$sqlStr = "/*\r\nDate：" . date('Y/m/d H:i:s',time()) . "\r\nUrl：http://www.llggo.com\r\nTable：" . input('param.table') . "\r\nCopyRight：HanYu\r\n*/\r\n";
foreach ($table as $key => $value) {
$sqlStr .= "SET FOREIGN_KEY_CHECKS=0;\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$sqlStr .= "-- Table structure for `$value`\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$sqlStr .= "DROP TABLE IF EXISTS `$value`;\r\n";
$create = db()->query('show create table ' . $value);
$sqlStr .= $create['0']['Create Table'] . ";\r\n";
$sqlStr .= "\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$sqlStr .= "-- Records of `$value`\r\n";
$sqlStr .= "-- ----------------------------\r\n";
$result = db()->query('select * from ' . $value);
foreach($result as $key => $vo){
$keys = array_keys($vo);
$keys = array_map('addslashes', $keys);
$keys = join('`,`', $keys);
$keys = "`" . $keys . "`";
$vals = array_values($vo);
$vals = array_map('addslashes', $vals);
$vals = join("','", $vals);
$vals = "'" . $vals . "'";
$sqlStr .= "insert into `$value`($keys) values($vals);\r\n\r\n";
}
}
/* 写入sql文件 */
$filename = config('back_path') . config('database')['database'] . ".sql";
$fp = fopen($filename, 'w'); //打开文件
fputs($fp, $sqlStr); //写入文件
/* 生成压缩包zip */
$zipPath = config('back_path');
$zipFile = $zipPath . iconv("utf-8", "GB2312//IGNORE", date('Ymd_'). config('database')['database']) . '.zip'; //压缩包的名称
session('zipFile',date('Ymd_'). config('database')['database'] . '.zip');
$zip = new ZipArchive();
$zip->open($zipFile, ZipArchive::OVERWRITE || ZipArchive::CREATE); //创建zip文件
$zip->addFile($filename,basename($filename)); //把文件放入zip
$zip->close();//关闭
fclose($fp); //关闭文件句柄
unlink($filename);
return json(['code' => 1, 'data' => '', 'msg' => '文件备份在【back】目录下，文件名为：【' . date('Ymd_'). config('database')['database'] . '.zip】']);
}
// 还原数据
public function backDataAll()
{
set_time_limit(0);
$table = input('param.table');
if(!file_exists(config('back_path') . session('zipFile'))){
return json(['code' => -1, 'data' => '', 'msg' => '备份数据不存在!']);
}
/* 解压备份文件 */
$zip = new ZipArchive();
$zip->open(config('back_path') . session('zipFile'));
$zip->extractTo(config('back_path'));
$zip->close();
$sqls = analysisSql(config('back_path') . config('database')['database'] . ".sql");
foreach($sqls as $key=>$sql){
db()->query($sql);
}
if (is_file(config('back_path') . config('database')['database'] . ".sql")) {
unlink(config('back_path') . config('database')['database'] . ".sql");// 删除解压的sql文件
}
return json(['code' => 1, 'data' => '', 'msg' => '数据库:【' . config('database')['database'] . '】']);
}
/**
* 拼装操作按钮
* @param $table
* @return array
*/
private function makeButton($table)
{
return [
'备份' => [
'auth' => 'data/importdata',
'href' => "javascript:importData('" .$table ."')",
'btnStyle' => 'primary',
'icon' => 'fa fa-tasks'
],
'还原' => [
'auth' => 'data/backdata',
'href' => "javascript:backData('" .$table ."')",
'btnStyle' => 'info',
'icon' => 'fa fa-retweet'
]
];
}
/**
* 拼装全部操作按钮
* @param $table
* @return array
*/
private function makeButtonAll($table)
{
return [
'全部备份' => [
'auth' => 'data/importdataall',
'href' => "javascript:importDataAll('" .$table ."')",
'btnStyle' => 'primary',
'icon' => 'fa fa-tasks'
],
'全部还原' => [
'auth' => 'data/backdataall',
'href' => "javascript:backDataAll('" .$table ."')",
'btnStyle' => 'info',
'icon' => 'fa fa-retweet'
]
];
}
}生成操作按钮
/**
* 生成操作按钮
* @param array $operate 操作按钮数组
*/
function showOperate($operate = [])
{
if(empty($operate)){
return '';
}
$option = '';
foreach($operate as $key=>$vo){
if(authCheck($vo['auth'])){
$option .= ' <a href="' . $vo['href'] . '"><button type="button" class="btn btn-' . $vo['btnStyle'] . ' btn-sm">'.
'<i class="' . $vo['icon'] . '"></i> ' . $key . '</button></a>';
}
}
return $option;
}解析备份sql文件
/**
* 解析备份sql文件
* @param $file
*/
function analysisSql($file)
{
// sql文件包含的sql语句数组
$sqls = array ();
$f = fopen ( $file, "rb" );
// 创建表缓冲变量
$create = '';
while ( ! feof ( $f ) ) {
// 读取每一行sql
$line = fgets ( $f );
// 如果包含空白行，则跳过
if (trim ( $line ) == '') {
continue;
}
// 如果结尾包含';'(即为一个完整的sql语句，这里是插入语句)，并且不包含'ENGINE='(即创建表的最后一句)，
if (! preg_match ( '/;/', $line, $match ) || preg_match ( '/ENGINE=/', $line, $match )) {
// 将本次sql语句与创建表sql连接存起来
$create .= $line;
// 如果包含了创建表的最后一句
if (preg_match ( '/ENGINE=/', $create, $match )) {
// 则将其合并到sql数组
$sqls [] = $create;
// 清空当前，准备下一个表的创建
$create = '';
}
// 跳过本次
continue;
}
$sqls [] = $line;
}
fclose ( $f );
return $sqls;
}