在线dump生成sql文件的php程序
<?php
session_start();
ini_set('time_limit',3600);
//--------------- 参数配置 ---------------
define('CONF_DB_SERVER','127.0.0.1');
define('CONF_DB_USER','root');
define('CONF_DB_PASSWD','');
define('CONF_DB_CODE','utf8');
define('CONF_PASSWORD','123456');
define('CONF_CRLF',"\n");
define('CONF_EXPORT_DIR',"");
//--------------- 连接数据库 ---------------
mysql_connect(CONF_DB_SERVER,CONF_DB_USER,CONF_DB_PASSWD);
mysql_query('set names '.CONF_DB_CODE);
//--------------- 开始执行导出 ---------------
if(isset($_POST['act']) && $_POST['act'] == 'export'){
$db = $_POST['db'];
$file = CONF_EXPORT_DIR.$db."_".date('Ymd').".sql";
@unlink($file);
$str = 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";'."\n\n";
$str .= '/*!40101 SET NAMES utf8 */;'."\n\n";
//$str .= 'CREATE DATABASE IF NOT EXISTS `'.$db.'` DEFAULT CHARACTER SET '.CONF_DB_CODE.';'."\n\n";
_log($str, $file);//防止导入乱码
mysql_select_db($db);
$sql = 'show tables';
$q = mysql_query($sql);
while($rs=mysql_fetch_row($q)){
$str = '';
$str = get_tbl_struct($rs[0]);
_log($str, $file);//数据表结构
get_table_content($rs[0], $file);//数据
}
die('finish');
}
//--------------- 查询全部数据库 ---------------
$sql = "SELECT lower(schema_name) schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('mysql','information_schema','test','search','tbsearch','sbtest','my_db','sys_info')";
$q = mysql_query($sql);
while($rs=mysql_fetch_row($q)){
$db_list[] = $rs[0];
}
//$str = 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";'.CONF_CRLF;
function _log($str, $file){
error_log($str, 3, $file);
}
//--------------- 数据库结构 ---------------
function get_tbl_struct($table){
$sql = "show create table `$table`";
$q = mysql_query($sql);
$rs = mysql_fetch_row($q);
$str = $rs[1];
$str = "DROP TABLE IF EXISTS `$table`;\n".$str;
$str = str_replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS',$str);
return $str.";\n\n";
}
//--------------- 数据表的数据 ---------------
function get_table_content($table, $file){
$page_size = 1000;
$max_page = 1;
$insert_len = 30;
$schema_create = " ";
$temp = " ";
$q = mysql_query("SELECT count(*) FROM $table");
$rs = mysql_fetch_row($q);
$total = $rs[0];
$max_page = ceil($total/$page_size);
if($total == 0) return false;
//取出字段名称
$sql = "show columns from `$table`";
$q = mysql_query($sql);
while($rs = mysql_fetch_array($q)){
$tbl_fields[] = $rs['Field'];
}
$insert_header = "INSERT INTO `$table` (`".implode('`, `',$tbl_fields)."`) VALUES".CONF_CRLF;
_log($insert_header, $file);
$i = 0;
for($page=0;$page<$max_page;$page++)
{
$result = mysql_query("SELECT * FROM $table LIMIT ".($page*$page_size).",$page_size");
$curr_total = mysql_num_rows($result);
$fields_meta = PMA_DBI_get_fields_meta($result);
$search = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
$replace = array('\0', '\n', '\r', '\Z');
$curr_i = 0;
while($row = mysql_fetch_row($result))
{
$str = '(';
for($j=0; $j<mysql_num_fields($result);$j++)
{
// NULL
if (!isset($row[$j]) || is_null($row[$j])) {
$str .= 'NULL';
// a number
// timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp'
&& ! $fields_meta[$j]->blob) {
$str .= $row[$j];
// a true BLOB : 忽略了二进制数据处理
} else {
$str .= '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\'';
} // end if
if($j<mysql_num_fields($result)-1) $str .= ', ';
}
//$str = ereg_replace( ",$ ", " ", $str);
$str .= ")";
$i++;$curr_i++;
if($i >= $total && $curr_i==$curr_total){
$str .= ";".CONF_CRLF.CONF_CRLF;
}elseif(($i%$insert_len==0)){
$str .= ";".CONF_CRLF.$insert_header;
}else{
$str .= ",".CONF_CRLF;
}
_log($str, $file);
}
}
return true;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>MySQL数据库导出</title>
<style>
body {font-size:14px; font-family:Arial, Helvetica, sans-serif}
ul li{ padding:5px;}
label { width:160px;display:inline-block;font-size:12px; }
</style>
</head>
<body>
<ul>
<li>
数 据 库：(<?php echo(sizeof($db_list));?>) <input type="button" id="select_all" value="反选" /><br/>
<?php foreach($db_list as $v):?>
<label><input type="checkbox" name="db" checked="checked" value="<?php echo($v);?>" /> <?php echo($v);?></label>
<?php endforeach;?>
</li>
<li>
导出选项：<br/>
<label><input type="checkbox" name="e_struct" checked="checked" value="1" /> db struct</label>
<label><input type="checkbox" name="e_data" checked="checked" value="1" /> db data</label>
</li>
<li><input type="button" id="run" value="开始执行" /></li>
<li id="status">....</li>
</ul>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.6.min.js"></script>
<script>
$(function(){
$('#run').click(function(){
run_export(0);
});
$('#select_all').click(function(){
$('input[name="db"]').each(function(){
if(this.checked) this.checked = false;
else this.checked = true;
});
});
});
function run_export(i){
var db = $('input[name="db"]:checked');
$('#status').html('running : '+db[i].value);
$.post('dump.php',{act:'export',db:db[i].value},function(data){
$('#status').html(data+' : '+db[i].value);
i ++;
if(i < db.length){
run_export(i);
}else{
alert('全部完成');
}
});
}
</script>
</body>
</html>
<?php
/**
* @todo add missing keys like in from mysqli_query (orgname, orgtable, flags, decimals)
*/
function PMA_DBI_get_fields_meta($result) {
$fields = array();
$num_fields = mysql_num_fields($result);
for ($i = 0; $i < $num_fields; $i++) {
$fields[] = mysql_fetch_field($result, $i);
}
return $fields;
}
/**
* Add slashes before "'" and "\" characters so a value containing them can
* be used in a sql comparison.
*
* @uses str_replace()
* @param string the string to slash
* @param boolean whether the string will be used in a 'LIKE' clause
* (it then requires two more escaped sequences) or not
* @param boolean whether to treat cr/lfs as escape-worthy entities
* (converts \n to \\n, \r to \\r)
*
* @param boolean whether this function is used as part of the
* "Create PHP code" dialog
*
* @return string the slashed string
*
* @access public
*/
function PMA_sqlAddslashes($a_string = '', $is_like = false, $crlf = false, $php_code = false)
{
if ($is_like) {
$a_string = str_replace('\\', '\\\\\\\\', $a_string);
} else {
$a_string = str_replace('\\', '\\\\', $a_string);
}
if ($crlf) {
$a_string = str_replace("\n", '\n', $a_string);
$a_string = str_replace("\r", '\r', $a_string);
$a_string = str_replace("\t", '\t', $a_string);
}
if ($php_code) {
$a_string = str_replace('\'', '\\\'', $a_string);
} else {
$a_string = str_replace('\'', '\'\'', $a_string);
}
return $a_string;
} // end of the 'PMA_sqlAddslashes()' function
?>
评论
React 18的并发渲染确实是个重大改进，我们在项目中已经升级使用，性能提升明显！