如何从 MySQL 数据库导出 SQL 文件

最新推荐文章于 2025-05-10 12:31:25 发布

原创最新推荐文章于 2025-05-10 12:31:25 发布 · 245 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #SQL #数据结构 #PHP #Cache

PHP 专栏收录该内容

46 篇文章

订阅专栏

本文提供了一个使用PHP进行MySQL数据库备份的脚本示例。通过设置数据库连接参数，该脚本可以实现数据库表结构及数据的完整备份，并将其导出为SQL文件格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

From: http://topic.youkuaiyun.com/t/20040726/11/3209900.html
改进: mysql_query("set names utf8");

 <?   
          /*   
          程序功能：mysql数据库备份功能   
          作者：         唐小刚   
          说明：本程序主要是从mysqladmin中提取出来，并作出一定的调整，希望对大家在用php编   
                      程时备份数据有定帮助.   
                      如果不要备份结构：请屏掉这句://echo   get_table_def($dbname,   $table,   $crlf).";$crlf$crlf";   
                      如果不要备份内容：请屏掉这句:echo   get_table_content($dbname,   $table,   $crlf);   
          */   

          header("Content-disposition:   filename=backup.sql");//所保存的文件名   
          header("Content-type:   application/octetstream");   
          header("Pragma:   no-cache");   
          header("Expires:   0");   

  //备份数据   

    $i   =   0;   
    $crlf="\r\n";   
    $dbname="数据库名";//数据库名   
    global     $dbconn;   
    $dbconn   =   mysql_connect("localhost","用户名","密码");//数据库主机，用户名，密码   
    $db   =   mysql_select_db($dbname,$dbconn);   
    $tables   =   mysql_list_tables($dbname,$dbconn);   
    $num_tables   =   @mysql_numrows($tables);   
          while($i   <   $num_tables)   
          {     
                  $table   =   mysql_tablename($tables,   $i);   

                  print   $crlf;   
                  print   $crlf;   

                  echo   get_table_def($dbname,   $table,   $crlf).";$crlf$crlf";   
                  echo   get_table_content($dbname,   $table,   $crlf);   
                  $i++;   
          }   

  //定义抽取表结构与数据   


  function   get_table_def($db,   $table,   $crlf)   
  {   
          global   $drop;   

          $schema_create   =   "";   
          if(!empty($drop))   
                  $schema_create   .=   "DROP   TABLE   IF   EXISTS   $table;$crlf";   

          $schema_create   .=   "CREATE   TABLE   $table   ($crlf";   
          $result   =   mysql_db_query($db,   "SHOW   FIELDS   FROM   $table");   
          while($row   =   mysql_fetch_array($result))   
          {   
                  $schema_create   .=   "       $row[Field]   $row[Type]";   

                  if(isset($row["Default"])   &&   (!empty($row["Default"])   ||   $row["Default"]   ==   "0"))   
                          $schema_create   .=   "   DEFAULT   '$row[Default]'";   
                  if($row["Null"]   !=   "YES")   
                          $schema_create   .=   "   NOT   NULL";   
                  if($row["Extra"]   !=   "")   
                          $schema_create   .=   "   $row[Extra]";   
                  $schema_create   .=   ",$crlf";   
          }   
          $schema_create   =   ereg_replace(",".$crlf."$",   "",   $schema_create);   
          $result   =   mysql_db_query($db,   "SHOW   KEYS   FROM   $table");   
          while($row   =   mysql_fetch_array($result))   
          {   
                  $kname=$row['Key_name'];   
                  if(($kname   !=   "PRIMARY")   &&   ($row['Non_unique']   ==   0))   
                          $kname="UNIQUE|$kname";   
                    if(!isset($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)";   
          return   (stripslashes($schema_create));   
  }   

  //保存数据表的值   
  function   get_table_content($db,   $table,   $crlf)   
  {   
          $schema_create   =   "";   
          $temp   =   "";   
          $result   =   mysql_db_query($db,   "SELECT   *   FROM   $table");   
          $i   =   0;   
          while($row   =   mysql_fetch_row($result))   
          {   
                  $schema_insert   =   "INSERT   INTO   $table   VALUES   (";   
                  for($j=0;   $j<mysql_num_fields($result);$j++)   
                  {   
                          if(!isset($row[$j]))   
                                  $schema_insert   .=   "   NULL,";   
                          elseif($row[$j]   !=   "")   
                                  $schema_insert   .=   "   '".addslashes($row[$j])."',";   
                          else   
                                  $schema_insert   .=   "   '',";   
                  }   
                  $schema_insert   =   ereg_replace(",$",   "",   $schema_insert);   
                  $schema_insert   .=   ");$crlf";   
                  $temp   =   $temp.$schema_insert   ;   
                  $i++;   
          }   
          return   $temp;   
  }   
  ?>