本来想着用SHOW CREATE TABLE + ergodic 来做备份的但是发现如果Table 有 comment 而且还是乱码的话,会导致SHOW CREATE TABLE 出来的脚本缺少一个符号。所以有了这个版本。
-
class MysqlExport{
-
- /**
- * database connect
- */
- private $_db;
-
- private $_resource;
-
- /**
- * create table structure sql
- */
- private $_create_table = '';
-
- public function __construct($host = '', $user = '', $pass = '', $db = '', $port = 3306) {
- if (empty($host) || empty($user)) {
-
- } else {
- $this->real_connect($host, $user, $pass, $db, $port);
- }
- }
-
- public function init() {
- return $this;
- }
-
- /**
- * 连接数据库
- */
- public function real_connect($host, $user, $pass, $db = '', $port = 3306) {
- $this->_db = mysql_connect($host . ':' . $port, $user, $pass);
- if ($db) {
- $this->select_db($db);
- }
- return $this->init();
- }
-
- /**
- * 选择数据库
- */
- public function select_db($db) {
- if (mysql_select_db($db, $this->_db)) {
- return true;
- }
- }
-
- /**
- * 查询语句
- */
- public function query($sql) {
- if ($this->_db) {
- if ($this->_resource = mysql_query($sql, $this->_db)) {
- return $this->init();
- }
- }
- throw new Exception($this->get_error());
- }
-
- /**
- * 获取结果集
- */
- public function fetch_array($arg = MYSQL_BOTH) {
- $result = array();
- if ($this->_resource && !mysql_errno($this->_db)) {
- while ($rs = mysql_fetch_array($this->_resource, $arg)) {
- $result[] = $rs;
- }
- }
- return $result;
- }
-
- /**
- * 获取错误
- */
- public function get_error() {
- return mysql_errno($this->_db) . ": " . mysql_error($this->_db). "\n";
- }
-
- /**
- * 显示数据表
- */
- public function show_tables($table = '') {
- $sql = "SHOW TABLES";
- $sql .= $table ? " LIKE '{$table}'" : '';
- $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
- return $result;
- }
-
- /**
- * 显示数据表字段
- */
- public function show_columns($table) {
- if (empty($table)) {
- return array();
- }
- $sql = "SHOW FULL COLUMNS FROM {$table}";
- $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
- return $result;
- }
-
- /**
- * 显示数据表状态
- */
- public function show_table_status($table) {
- if (empty($table)) {
- return array();
- }
- $result = $this->query("SHOW TABLE STATUS LIKE '{$table}'")->fetch_array(MYSQL_ASSOC);
- $result = reset($result);
- return $result;
- }
-
- /**
- * 显示数据表结构
- */
- public function show_create_table($table) {
- if (empty($table)) {
- return '';
- }
- $this->_create_table = "CREATE TABLE IF NOT EXISTS `{$table}`(" . PHP_EOL;
- $table_status = $this->show_table_status($table);
- $columns = $this->show_columns($table);
-
- foreach ($columns AS $col) {
- $this->_create_table .= "`{$col['Field']}` {$col['Type']} NOT NULL {$col['Extra']}," . PHP_EOL;
- }
-
- $this->_create_table .= $this->create_indexSyntax($table);
-
- $char = substr($table_status['Collation'], 0, strpos($table_status['Collation'], '_'));
- $table_status['Auto_increment'] = $table_status['Auto_increment'] ? $table_status['Auto_increment'] : 0;
- $this->_create_table .= ")Engine={$table_status['Engine']} AUTO_INCREMENT={$table_status['Auto_increment']} DEFAULT CHARSET={$char};" . str_repeat(PHP_EOL, 3);
-
- return $this->_create_table;
- }
-
- /**
- * 显示触发器
- */
- public function show_constraint($db_name) {
- if (empty($db_name)) {
- return array();
- }
- $sql = "SELECT a.CONSTRAINT_NAME AS constrint_name, a.TABLE_name AS table_name, a.COLUMN_NAME AS column_name, a.REFERENCED_TABLE_NAME as referenced_table_name, a.REFERENCED_COLUMN_NAME AS referenced_column_name, b.UPDATE_RULE as update_rule, b.DELETE_RULE AS delete_rule FROM information_schema.KEY_COLUMN_USAGE AS a LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS AS b ON a.constraint_name=b.constraint_name WHERE a.constraint_schema = '{$db_name}' AND a.POSITION_IN_UNIQUE_CONSTRAINT = 1";
- $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
- }
-
- /**
- * 显示索引
- */
- public function show_index($table) {
- if (empty($table)) {
- return array();
- }
- $sql = "SHOW INDEX FROM {$table}";
- $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
- return $result;
- }
-
- /**
- * 显示数据库结构
- */
- public function show_database_char() {
- $sql = "SHOW VARIABLES LIKE 'character_set_database'";
- $char = $this->query($sql)->fetch_array(MYSQL_ASSOC);
- return reset($char);
- }
-
- /**
- * 创建索引语法
- */
- public function create_indexSyntax($table) {
- if (empty($table)) {
- return array();
- }
- $indexing = $this->show_index($table);
- $syntax = array();
- $indexSyntax = array();
- foreach ($indexing as $index) {
- $syntax[$index['Index_type']][$index['Key_name']][] = $index['Column_name'];
- }
-
- foreach ($syntax as $index_type => $index_value) {
- foreach ($index_value as $key_name => $columns) {
- if ($key_name == 'PRIMARY') {
- $indexSyntax[] = 'PRIMARY KEY (`' . implode("`,`", $columns) . '`)';
- } else {
- if ($index_type == 'FULLTEXT') {
- $indexSyntax[] = "FULLTEXT KEY `{$key_name}` (`" . implode("`,`", $columns) . '`)';
- } else{
- $indexSyntax[] = "KEY `{$key_name}` USING {$index_type} (`" . implode("`,`", $columns) . '`)';
- }
- }
- }
- }
-
- return implode(',' . PHP_EOL, $indexSyntax) . PHP_EOL;
- }
-
- /**
- * 创建 insert 语法
- */
- public function create_insertSyntax($table) {
- if (empty($table)) {
- return '';
- }
- $sql = "SELECT * FROM {$table}";
- $result = $this->query($sql)->fetch_array(MYSQL_ASSOC);
- $insertStr = '';
- if ($result) {
- $first = reset($result);
- $key = implode('`,`', array_keys($first));
- $insert = "INSERT INTO `{$table}` (`{$key}`) VALUES ";
- $valuesStr = array();
- foreach ($result as $value) {
- $values = array();
- foreach ($value as $v) {
- $v = mysql_real_escape_string($v);
- $values[] = preg_replace("#\\\+#", "\\", $v);
- }
- $valuesStr[] = "('" . implode("','", $values) . "')";
- }
- $valuesStr = array_chunk($valuesStr, 5000);
- foreach ($valuesStr as $str) {
- $insertStr .= $insert . implode(',', $str) . ';' . PHP_EOL;
- }
-
- }
- return $insertStr . str_repeat(PHP_EOL, 3);
- }
-
- }
-
- $export = '';
- $test = new MysqlExport('localhost', 'root', '', 'pm_cms');
- $char = $test->show_database_char();
- $test->query("SET NAMES {$char['Value']}");
- $tables = $test->show_tables();
- foreach ($tables as $table) {
- list($table_name) = array_values($table);
- $export .= $test->show_create_table($table_name);
- $export .= $test->create_insertSyntax($table_name);
- }
- $fp = fopen('pm_cms.sql', 'w');
- fwrite($fp, $export);
- fclose($fp);
- ?>
复制代码
|