<?php 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);