一、标准的数据库导出内容讲解
先看一个标准的数据库导出的sql内容:
/*
Navicat Premium Data Transfer
Source Server : 192.168.1.122
Source Server Type : MySQL
Source Server Version : 50621
Source Host : 192.168.1.122:3306
Source Schema : db1
Target Server Type : MySQL
Target Server Version : 50621
File Encoding : 65001
Date: 20/07/2022 17:44:22
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name`(`name`) USING BTREE,
INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES (1, '王明', 30);
INSERT INTO `table1` VALUES (2, '张芳', 28);
SET FOREIGN_KEY_CHECKS = 1;
标注导出的数据库信息:
/*
Navicat Premium Data Transfer
Source Server : 192.168.1.122
Source Server Type : MySQL
Source Server Version : 50621
Source Host : 192.168.1.122:3306
Source Schema : db1
Target Server Type : MySQL
Target Server Version : 50621
File Encoding : 65001
Date: 20/07/2022 17:44:22
*/
设置UTF-8编码格式:
SET NAMES utf8mb4;
取消外键约束 (参数为0) 和设置外键约束 (参数为1) :
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;
表结构:
-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `name`(`name`) USING BTREE,
INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
表数据:
-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES (1, '王明', 30);
INSERT INTO `table1` VALUES (2, '张芳', 28);
二、PHP导出数据库的常用操作
查询某个库是否存在,有两种写法:
SELECT * FROM `information_schema`.SCHEMATA WHERE SCHEMA_NAME="db1";
SHOW DATABASES LIKE "db1";
查询某个表是否存在,有两种写法:
SELECT * FROM `information_schema`.TABLES WHERE TABLE_NAME="table1";
SHOW TABLES LIKE "table1";
查询某个库的某个表的某个字段是否存在:
SELECT * FROM `information_schema`.COLUMNS WHERE TABLE_SCHEMA="db1" AND TABLE_NAME="table1" AND COLUMN_NAME="name";
获取某个库下的全部表名称:
SHOW TABLES;
获取表结构:
SHOW CREATE TABLE `table1`;
获取表数据,包括表字段和表数据:
SHOW COLUMNS FROM `table1`;
SELECT * FROM `table1`;
完整的扩展类代码:
<?php
header("Content-Type: text/html;charset=utf-8");
/**
* 备份数据库的扩展类
*/
class BackService{
private $config=[];
private $handler;
private $table = array(); //需要备份的表
private $begin; //开始时间
private $error; //错误信息
private $execRowNum = 200; //每次数据执行的SQL行数
public function __construct($config) {
$config['path'] = './file/'; //默认路径
$config['sqlbakname'] = 'bak.sql'; //备份文件名称
$this->config = $config;
$this->connect();
}
//首次进行pdo连接
private function connect() {
try{
$this->handler = new \PDO("{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']}",
$this->config['username'],
$this->config['password'],
array(
\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']}",
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
));
}catch (PDOException $e) {
exit( "Error! ".$e->getMessage()."<br/>" );
}
}
/**
* 查询
* @param string $sql
* @return mixed
*/
private function query($sql = '')
{
$stmt = $this->handler->query($sql);
$stmt->setFetchMode(\PDO::FETCH_NUM);
$list = $stmt->fetchAll();
return $list;
}
/**
* 获取全部表
* @param string $dbName
* @return array
*/
private function get_dbname($dbName = '*') {
$sql = "SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='".$this->config['database']."' AND TABLE_TYPE='BASE TABLE'";
$list = $this->query($sql);
$tables = array();
foreach($list as $value){
$tables[] = $value[0];
}
return $tables;
}
/**
* 获取表定义语句
* @param string $dbName
* @return mixed
*/
private function get_dbhead($table = '') {
$sql = "SHOW CREATE TABLE `{$table}`";
$ddl = $this->query($sql)[0][1].";";
return $ddl;
}
/**
* 获取表数据
* @param string $table
* @return mixed
*/
private function get_dbdata($table = '')
{
$sql = "SHOW COLUMNS FROM `{$table}`";
$list = $this->query($sql);
//字段
$columns = '';
foreach($list as $value){
$columns .= "`".$value[0]."`,";
}
$columns = substr($columns, 0, -1);
//需要返回的SQL
$query = '';
$data = $this->query("SELECT * FROM `{$table}`");
foreach($data as $value){
$dataSql = '';
foreach($value as $v)
{
if($v == ''){
$dataSql .= "NULL,";
}else{
$dataSql .= "'{$v}',";
}
}
$dataSql = substr($dataSql, 0, -1);
$query .= "INSERT INTO `{$table}` ({$columns}) VALUES ({$dataSql});\r\n";
}
return $query;
}
/**
* 写入文件
* @param array $tables
* @param array $ddl
* @param array $data
*/
private function writeToFile($tables = array(), $ddl = array(), $data = array())
{
$verRes = $this->query("SELECT VERSION()");
$version = str_replace('.', '', str_replace('-log', '', $verRes[0][0]));
$version = substr($version, 0, 1).'0'.substr($version, 1);
$str = "/*\r\nMySQL Database Backup Tools\r\n";
$str .= "Source Server Type : {$this->config['type']}\r\n";
$str .= "Source Server Version : {$version}\r\n";
$str .= "Source Host : {$this->config['hostname']}:{$this->config['hostport']}\r\n";
$str .= "Source Schema : {$this->config['database']}\r\n\r\n";
$str .= "Date: ".date('d/m/Y H:i:s')."\r\n*/\r\n";
$str .= "SET NAMES utf8mb4;\r\n";
$str .= "SET FOREIGN_KEY_CHECKS = 0;\r\n";
$i = 0;
foreach($tables as $table){
$str .= "-- ----------------------------\r\n";
$str .= "-- Table structure for {$table}\r\n";
$str .= "-- ----------------------------\r\n";
$str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";
$str .= $ddl[$i]."\r\n";
$str .= "-- ----------------------------\r\n";
$str .= "-- Records of {$table}\r\n";
$str .= "-- ----------------------------\r\n";
$str .= $data[$i]."\r\n";
$i++;
}
if(!file_exists($this->config['path'])){
mkdir($this->config['path']);
}
return file_put_contents($this->config['path'].$this->config['sqlbakname'], $str) ? true : false;
}
/**
* 设置要备份的表
* @param array $tables
*/
private function setTables($tables = array())
{
if(!empty($tables) && is_array($tables)){
//备份指定表
$this->tables = $tables;
}else{
//备份全部表
$this->tables = $this->get_dbname();
}
}
/**
* 备份
* @param array $tables
* @return bool
*/
public function backup($tables = array())
{
//存储表定义语句的数组
$ddl = array();
//存储数据的数组
$data = array();
$this->setTables($tables);
if(!empty($this->tables)){
foreach($this->tables as $table){
$tableIsExist = $this->query("SELECT * FROM `information_schema`.TABLES WHERE TABLE_NAME='{$table}'");
if(!$tableIsExist){
$this->error = "表 {$table} 不存在";
exit($this->getError());
}
$ddl[] = $this->get_dbhead($table);
$data[] = $this->get_dbdata($table);
}
//开始写入
return $this->writeToFile($this->tables, $ddl, $data);
}else{
$this->error = '数据库中没有表';
return false;
}
}
/**
* 错误信息
* @return mixed
*/
public function getError()
{
return $this->error;
}
/**
* 恢复数据库
*/
public function restore()
{
$filename = $this->config['sqlbakname'];
$path = $this->config['path'].$filename;
if(!file_exists($path)){
$this->error("SQL文件不存在");
return false;
}else{
$sql = $this->parseSQL($path);
$sqlArr = explode(";\r\n", $sql);
try{
$this->handler->beginTransaction();
for($i=1; $i<=ceil(count($sqlArr)/$this->execRowNum); $i++){
$newSqlArr = array_slice($sqlArr, ($i-1)*$this->execRowNum, $this->execRowNum);
$sqlStr = "";
foreach($sqlArr as $val){
$sqlStr .= trim($val).";";
}
$this->handler->exec($sqlStr);
}
$this->handler->commit();
return true;
}catch (PDOException $E){
$this->error = $e->getMessage();
return false;
}
}
}
/**
* 解析SQL文件为SQL语句数组
* @param string $path
* @return array|mixed|string
*/
private function parseSQL($path = '')
{
$sql = file_get_contents($path);
$sql = explode("\r\n", $sql);
//先消除 -- 注释
$sql = array_filter($sql, function($data){
if(empty($data) || preg_match('/.*--.*/', $data)){
return false;
}else{
return true;
}
});
$sql = implode("\r\n", $sql);
//删除 /**/ 注释
$sql = preg_replace('/\/\*.*\*\//is', '', $sql);
return $sql;
}
/**
* 下载备份文件
* @param string $fileName
* @return array|mixed|string
*/
public function downloadFile()
{
$fileName = $this->config['sqlbakname'];
$fileName = $this->config['path'].$fileName;
if(file_exists($fileName)){
ob_end_clean();
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Description: File Transfer");
header("Content-Type: application/octet-stream");
header("Content-Length: ".filesize($fileName));
header("Content-Disposition: attachment; filename=".basename($fileName));
readfile($fileName);
}else{
$this->error="文件不存在";
}
}
/**
* 获取文件时间
* @param string $file
* @return string
*/
private function getfiletime($file)
{
$path = $this->config['path'].$file;
$a = filemtime($path);
$time = date('Y-m-d H:i:s', $a);
return $time;
}
/**
* 获取文件大小
* @param string $file
* @return string
*/
private function getfilesize($file)
{
$perms = stat($this->config['path'].$file);
$size = $perms['size'];
$a = ['B', 'KB', 'MB', 'GB', 'TB'];
$pos = 0;
while ($size >= 1024) {
$size /= 1024;
$pos++;
}
return round($size, 2).$a[$pos];
}
/**
* 获取文件列表
* @param string $order 级别
* @return string
*/
public function get_filelist($order = 0)
{
$filepath = opendir($this->config['path']);
$fileAndFolderAyy = array();
$i = 1;
while (false != ($filename = readdir($filepath))) {
if($filename != '.' && $filename != '..'){
$i++;
$fileAndFolderAyy[$i]['name'] = $filename;
$fileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);
$fileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);
}
}
$order == 0 ? sort($fileAndFolderAyy) : rsort($fileAndFolderAyy);
return $fileAndFolderAyy;
}
/**
* 删除备份文件
*/
public function delfile()
{
$filename = $this->config['sqlbakname'];
$path = $this->config['path'].$filename;
if(file_exists($path)){
unlink($path);
}
return true;
}
}