tp5导出mysql语句_基于ThinkPHP5的数据库表导入导出类

//+----------------------------------------------------------------------

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

// +----------------------------------------------------------------------

// | 当前版本：1.0.0

// +----------------------------------------------------------------------

// | 作者：何效名

// +----------------------------------------------------------------------

namespace app\index\common;usethink\Controller;usethink\Db;class BackupSql extendsController

{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 functionfileList()

{$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 functionopenFile()

{if($this -> file === null)

{$fullPath = $this -> path . '/' . $this -> table . '-' . date('YmdHis') . '.sql';$this -> file = fopen($fullPath, 'a');

}

}

}