麻烦做过PHP备份数据库的朋友来看下

since2006 2004-11-23 12:28:07

小弟想写个备份数据库的程序，但写到备份表结构时不知道该怎么写了。
假如一字段是主建和 index 的。操作时
show fields from test
没有显示主键和index

我show keys from test 也没见到相关的字段。
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
test 0 PRIMARY 1 age A 0 NULL NULL BTREE
test 0 dddd 1 dddd A 0 NULL NULL BTREE
test 1 aa 1 aa A NULL NULL NULL BTREE

请问备份表结构时该如何操作。谢谢！

...全文

184 点赞收藏 15

写回复

15 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

magicsnake 2004-11-27

搬张凳子来座一下

回复赞

wchun 2004-11-26

我现在用的 ~

回复赞

wchun 2004-11-26

//*****************************************************************
//函数名：get_table_code
//功能：返回指定数据库中表的结构，用于创建指定表的SQL语句
//参数：$link　是数据库连接
// $database　数据库名
// $table_name表名
//*****************************************************************
function get_table_code($link,$database,$table_name)
{
mysql_select_db($database, $link);
$result = mysql_query("select * from ".$table_name, $link);
$return_str = "DROP TABLE IF EXISTS ".$table_name.";\n";
$return_str .= "CREATE TABLE `".$table_name."` (\n\t";
$fields_num = (int)mysql_num_fields($result);

$fun_my['string'] = "varchar";
$fun_my['datetime'] = "datetime";
$fun_my['blob'] = "text";
$fun_my['real'] = "float";
echo "<br>";
for($i=0; $i < $fields_num; $i++)
{
$return_str .= "`".mysql_field_name($result, $i)."` ";

$type = mysql_field_type($result, $i);
if(isset($fun_my[$type]))
{ //解析数据类型
if($fun_my[$type]=="datetime" || $fun_my[$type]=="text")
{
$return_str .= $fun_my[$type]." ";
}else{
$return_str .= $fun_my[$type]."(".mysql_field_len($result, $i).") ";
}//end if
}else{
$return_str .= $type."(".mysql_field_len($result, $i).") ";
}//end if

if($i==0)
{ //
$return_str .= "NOT NULL auto_increment ";
}else{
$return_str .= mysql_field_flags($result, $i)=="not_null"?"NOT NULL ":"default NULL ";
}//end if
$return_str .= ",\n\t";

}//end for

// get keys list
$result = mysql_query("SHOW KEYS FROM $table_name", $link);
while ($array=mysql_fetch_array($result)){
$kname=$array['Key_name'];
if ($kname != "PRIMARY" and $array['Non_unique'] == 0) {
$kname="UNIQUE|$kname";
}
if(!is_array($index[$kname])) {
$index[$kname] = array();
}
$index[$kname][] = $array['Column_name'];
//print_r($index[$kname]);
}
// get each key info
while(list($kname, $columns) = @each($index)){
$colnames=implode($columns,",");
if ($kname == "PRIMARY") {
// do primary key
$return_str .= "PRIMARY KEY ($colnames)\n";
} else {
// do standard key
if (substr($kname,0,6) == "UNIQUE") {
// key is unique
$kname=substr($kname,7);
}
$return_str .= "KEY $kname ($colnames)\n";
}
}

$return_str .= ")TYPE=MyISAM;\n\n";
return $return_str;
}//end function

//*****************************************************************
//函数名：get_table_data
//功能：返回指定数据库指定表的所有数据的，插入SQL语句
//参数：$link　是数据库连接
// $database　数据库名
// $table_name表名
//*****************************************************************
function get_table_data($link,$database,$table_name)
{
mysql_select_db($database, $link);
$result = mysql_query("select * from ".$table_name, $link);
$fields_num = (int)mysql_num_fields($result);
while($row = mysql_fetch_row($result))
{
$row_data = "INSERT INTO `".$table_name."` VALUES ('";
for($i=0;$i<$fields_num;$i++)
{
if(!isset($row[$i])) $row[$i]="NULL";
$row_data_arr[$i]=addslashes($row[$i]);
}//end for
//print_r($row_data_arr);
$row_data .= implode("','",$row_data_arr);
$row_data .= "');\n";
$table_data .= $row_data;
}//end while
$table_data .= "\n\n";
return $table_data;
}//end function

回复赞

arbran 2004-11-26

针对MYSQL的

回复赞

arbran 2004-11-26

回复赞

pinson 2004-11-25

这是PHPBB中的代码？

回复赞

nationzhou 2004-11-23

回复赞

abaowu 2004-11-23

//结构备份
if($backup_type != 'data')
{
echo "#\n# TABLE: " . $table_name . "\n#\n";
echo get_table_def_mysql( $table_name, "\n") . "\n";
}

//数据备份
if($backup_type != 'structure')
{
$table_content_function($table_name, "output_table_content");
}

回复赞

abaowu 2004-11-23

function output_table_content($content)
{
global $tempfile;

//fwrite($tempfile, $content . "\n");
//$backup_sql .= $content . "\n";
echo $content ."\n";
return;
}

回复赞

abaowu 2004-11-23

$tables = array('auth_access', 'banlist', 'categories', 'config', 'disallow', 'forums', 'forum_prune', 'groups', 'posts', 'posts_text', 'privmsgs', 'privmsgs_text', 'ranks', 'search_results', 'search_wordlist', 'search_wordmatch', 'sessions', 'smilies', 'themes', 'themes_name', 'topics', 'topics_watch', 'user_group', 'users', 'vote_desc', 'vote_results', 'vote_voters', 'words');

回复赞

jxflll 2004-11-23

http://www.phome.net/empire/EmpireNews/n/2004/11-01/21335.html

这个软件很不错。是专门备份MYSQL数据库的。

回复赞

abaowu 2004-11-23

//
// This function is for getting the data from a mysql table.
//

function get_table_content_mysql($table, $handler)
{
global $db;

// Grab the data from the table.
if (!($result = $db->sql_query("SELECT * FROM $table")))
{
message_die(GENERAL_ERROR, "Failed in get_table_content (select *)", "", __LINE__, __FILE__, "SELECT * FROM $table");
}

// Loop through the resulting rows and build the sql statement.
if ($row = $db->sql_fetchrow($result))
{
$handler("\n#\n# Table Data for $table\n#\n");
$field_names = array();

// Grab the list of field names.
$num_fields = $db->sql_numfields($result);
$table_list = '(';
for ($j = 0; $j < $num_fields; $j++)
{
$field_names[$j] = $db->sql_fieldname($j, $result);
$table_list .= (($j > 0) ? ', ' : '') . $field_names[$j];

}
$table_list .= ')';

do
{
// Start building the SQL statement.
$schema_insert = "INSERT INTO $table $table_list VALUES(";

// Loop through the rows and fill in data for each column
for ($j = 0; $j < $num_fields; $j++)
{
$schema_insert .= ($j > 0) ? ', ' : '';

if(!isset($row[$field_names[$j]]))
{
//
// If there is no data for the column set it to null.
// There was a problem here with an extra space causing the
// sql file not to reimport if the last column was null in
// any table. Should be fixed now :) JLH
//
$schema_insert .= 'NULL';
}
elseif ($row[$field_names[$j]] != '')
{
$schema_insert .= '\'' . addslashes($row[$field_names[$j]]) . '\'';
}
else
{
$schema_insert .= '\'\'';
}
}

$schema_insert .= ');';

// Go ahead and send the insert statement to the handler function.
$handler(trim($schema_insert));

}
while ($row = $db->sql_fetchrow($result));
}

return(true);
}

回复赞

abaowu 2004-11-23

//
// This function returns the "CREATE TABLE" syntax for mysql dbms...
//
function get_table_def_mysql($table, $crlf)
{
global $drop, $db;

$schema_create = "";
$field_query = "SHOW FIELDS FROM $table";
$key_query = "SHOW KEYS FROM $table";

//
// If the user has selected to drop existing tables when doing a restore.
// Then we add the statement to drop the tables....
//
if ($drop == 1)
{
$schema_create .= "DROP TABLE IF EXISTS $table;$crlf";
}

$schema_create .= "CREATE TABLE $table($crlf";

//
// Ok lets grab the fields...
//
$result = $db->sql_query($field_query);
if(!$result)
{
message_die(GENERAL_ERROR, "Failed in get_table_def (show fields)", "", __LINE__, __FILE__, $field_query);
}

while ($row = $db->sql_fetchrow($result))
{
$schema_create .= ' ' . $row['Field'] . ' ' . $row['Type'];

if(!empty($row['Default']))
{
$schema_create .= ' DEFAULT \'' . $row['Default'] . '\'';
}

if($row['Null'] != "YES")
{
$schema_create .= ' NOT NULL';
}

if($row['Extra'] != "")
{
$schema_create .= ' ' . $row['Extra'];
}

$schema_create .= ",$crlf";
}
//
// Drop the last ',$crlf' off ;)
//
$schema_create = ereg_replace(',' . $crlf . '$', "", $schema_create);

//
// Get any Indexed fields from the database...
//
$result = $db->sql_query($key_query);
if(!$result)
{
message_die(GENERAL_ERROR, "FAILED IN get_table_def (show keys)", "", __LINE__, __FILE__, $key_query);
}

while($row = $db->sql_fetchrow($result))
{
$kname = $row['Key_name'];

if(($kname != 'PRIMARY') && ($row['Non_unique'] == 0))
{
$kname = "UNIQUE|$kname";
}

if(!is_array($index[$kname]))
{
$index[$kname] = array();
}

$index[$kname][] = $row['Column_name'];
}

while(list($x, $columns) = @each($index))
{
$schema_create .= ", $crlf";

if($x == 'PRIMARY')
{
$schema_create .= ' PRIMARY KEY (' . implode($columns, ', ') . ')';
}
elseif (substr($x,0,6) == 'UNIQUE')
{
$schema_create .= ' UNIQUE ' . substr($x,7) . ' (' . implode($columns, ', ') . ')';
}
else
{
$schema_create .= " KEY $x (" . implode($columns, ', ') . ')';
}
}

$schema_create .= "$crlf);";

if(get_magic_quotes_runtime())
{
return(stripslashes($schema_create));
}
else
{
return($schema_create);
}

} // End get_table_def_mysql

回复赞