验证中...
Languages: PHP
Categories: 数据库相关
Latest update 2019-05-21 10:36
Laravel_PHP_数据库备份
Raw Copy
<?php
/**
* author: sell
* createTime: 2019/5/20 17:23
* description:
*/
namespace App\Hiber;
use Illuminate\Support\Facades\DB;
class BackUpHiber
{
private static $tablename;//表名
private static $path_name;//存储路径
private static $db_name;//库名
private static $prefix;//表前缀
public static function back($pn=null){
//获取库名
self::$db_name = config('database.connections.mysql.database');
//获取表前缀,如果设置了
self::$prefix = config('database.connections.mysql.prefix');
//设置存储路径
self::$path_name = ($pn??'uploads/back/').date('YmdHis').rand(100,999).'.sql';
$date = date('Y-m-d H:i:s');
//查询当前库是否约束外键
$fkc = DB::select("SELECT @@FOREIGN_KEY_CHECKS;");
$fkcname = '@@FOREIGN_KEY_CHECKS';
$rema = "/*".PHP_EOL."Date: {$date}".PHP_EOL."*/".PHP_EOL.PHP_EOL."SET FOREIGN_KEY_CHECKS=".$fkc[0]->$fkcname.";".PHP_EOL;
self::putStr($rema);
//查询表名称
$tables = DB::select("show tables");
$table_key = 'Tables_in_'.self::$db_name;
$ct = 'Create Table';
foreach ($tables as $table_name){
self::$tablename = $table_name->$table_key;
$back_table = "-- ----------------------------".PHP_EOL."-- Table structure for ".self::$tablename.PHP_EOL."-- ----------------------------".PHP_EOL;
self::putStr($back_table);
//删除存在的表
self::putStr("DROP TABLE IF EXISTS `".self::$tablename."`;".PHP_EOL);
//查询表结构
$rule = DB::select("show create table `".self::$tablename."`");
self::putStr(($rule[0]->$ct).';'.PHP_EOL.PHP_EOL);
$back_reco = "-- ----------------------------".PHP_EOL."-- Records of ".self::$tablename.PHP_EOL."-- ----------------------------".PHP_EOL;
self::putStr($back_reco);
//查询表主键
$self = self::class;
$keycol = DB::select("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='".self::$tablename."' AND CONSTRAINT_SCHEMA='".self::$db_name."' AND constraint_name='PRIMARY'");
//分批查询记录
DB::table(strtr(self::$tablename,array(self::$prefix=>'')))->orderBy($keycol[0]->column_name,'asc')->chunk(100,function ($query)use ($self){
$query = $query->toArray();
//处理成插入sql语句
foreach ($query as $vq){
$str = "INSERT INTO `".$self::$tablename."` VALUES (";
$array = [];
foreach ($vq as $k=>$v){
if($v===NULL){
$array[] = 'null';
} else{
$tem = str_replace( array("'","\r\n"), array("\'","\\r\\n"), $v );
$tem = str_replace( array("'","\r"), array("\'","\\r"), $tem );
$tem = "'".str_replace( array("'","\n"), array("\'","\\n"), $tem )."'";
$array[] = $tem;
}
}
$str .= implode(', ', $array).');'.PHP_EOL;
$self::putStr($str);
}
});
self::putStr(PHP_EOL);
}
return self::$path_name;
}
private static function putStr($str){
file_put_contents(self::$path_name,$str,FILE_APPEND);
}
}
BackUpHiber.php
Raw Copy
<?php
/**
* author: sell
* createTime: 2019/5/20 17:23
* description:
*/
namespace App\Hiber;
use Illuminate\Support\Facades\DB;
class BackUpHiber extends ExtentHiber
{
private static $tablename;//表名
private static $path_name;//存储路径
private static $db_name;//库名
private static $prefix;//表前缀
public static function back($pn=null){
//获取库名
self::$db_name = config('database.connections.mysql.database');
//获取表前缀,如果设置了
self::$prefix = config('database.connections.mysql.prefix');
//设置存储路径
self::$path_name = ($pn??'uploads/back/').date('YmdHis').rand(100,999).'.sql';
$date = date('Y-m-d H:i:s');
//查询当前库是否约束外键
$fkc = DB::select("SELECT @@FOREIGN_KEY_CHECKS;");
$fkcname = '@@FOREIGN_KEY_CHECKS';
$rema = "/*".PHP_EOL."Date: {$date}".PHP_EOL."*/".PHP_EOL.PHP_EOL."SET FOREIGN_KEY_CHECKS=".$fkc[0]->$fkcname.";".PHP_EOL;
self::putStr($rema);
//查询表名称
$tables = DB::select("show tables");
$table_key = 'Tables_in_'.self::$db_name;
$ct = 'Create Table';
foreach ($tables as $table_name){
self::$tablename = $table_name->$table_key;
$back_table = "-- ----------------------------".PHP_EOL."-- Table structure for ".self::$tablename.PHP_EOL."-- ----------------------------".PHP_EOL;
self::putStr($back_table);
//删除存在的表
self::putStr("DROP TABLE IF EXISTS `".self::$tablename."`;".PHP_EOL);
//查询表结构
$rule = DB::select("show create table `".self::$tablename."`");
self::putStr(($rule[0]->$ct).';'.PHP_EOL.PHP_EOL);
$back_reco = "-- ----------------------------".PHP_EOL."-- Records of ".self::$tablename.PHP_EOL."-- ----------------------------".PHP_EOL;
self::putStr($back_reco);
//查询表主键
$self = self::class;
$keycol = DB::select("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='".self::$tablename."' AND CONSTRAINT_SCHEMA='".self::$db_name."' AND constraint_name='PRIMARY'");
//分批查询记录
DB::table(strtr(self::$tablename,array(self::$prefix=>'')))->orderBy($keycol[0]->column_name,'asc')->chunk(100,function ($query)use ($self){
$query = $query->toArray();
//处理成插入sql语句
foreach ($query as $vq){
$str = "INSERT INTO `".$self::$tablename."` VALUES (";
$array = [];
foreach ($vq as $k=>$v){
if($v===NULL){
$array[] = 'null';
} else{
$tem = str_replace( array("'","\r\n"), array("\'","\\r\\n"), $v );
$tem = str_replace( array("'","\r"), array("\'","\\r"), $tem );
$tem = "'".str_replace( array("'","\n"), array("\'","\\n"), $tem )."'";
$array[] = $tem;
}
}
$str .= implode(', ', $array).');'.PHP_EOL;
$self::putStr($str);
}
});
self::putStr(PHP_EOL);
}
return self::$path_name;
}
private static function putStr($str){
file_put_contents(self::$path_name,$str,FILE_APPEND);
}
}

Comment list( 0 )

You need to Sign in for post a comment

Help Search

Gitee_sixth 5th_float_left_close