dthtvk3666
2014-09-10 06:47 阅读 27
已采纳

如何按名称删除多个数据库,与sql约会?

The code below,list all the databases that exist and fetch them into an array,same thing for the tables of each database listed.Then it creates new databases and tables,containing every value of the older db and tables,but with th extension _backup-(a certain date).

$sql="SHOW DATABASES";
$query=mysql_query($sql,$connect);              
while ($row = mysql_fetch_assoc($query)) {
    $sql2="SHOW TABLES FROM ".$row['Database'];
    $query2=mysql_query($sql2, $connect);            
    $sql3 = "CREATE DATABASE `".$row['Database']."_backup-".$d."`";
    $query3=mysql_query($sql3,$connect);                                                    
 while( $row2 = mysql_fetch_assoc($query2) ) {   
  foreach($row2 as $rand2) { 
  $sql4 = "CREATE TABLE `".$row['Database']."_backup-".$d."`.`".$rand2."` SELECT * FROM `".$row['Database']."`.`".$rand2."`";
  $query4=mysql_query($sql4,$connect);
  }              
 }   
}

This code works as a backup,but the only thing that i cant figure out is how to drop the older databases.Lets say we trigger this script today (10.9.2014),and this code will creates a backup db1_backup-10.0.2014 for the original database named db1,if we trigger it today again,it will make backup of the previous backup,and thats a nasty thing,so i need to drop the previous backup,after the new backup is created,and keep the original db of course.I could really use your help guys,please.Thank you.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doucheng3407 doucheng3407 2014-09-10 08:01

    You can add DROP DATABASE before below code

    $sql="SHOW DATABASES";
    $query=mysql_query($sql,$connect);              
    while ($row = mysql_fetch_assoc($query)) {
        //Blacklist these to not make backup 
        if(! in_array($row['Database'], array('mysql', 'information_schema', 'performance_schema')) && strpos($row['Database'], "_backup-".$d) === false) {
            $sql2="SHOW TABLES FROM `".$row['Database']."`;";
            $query2=mysql_query($sql2, $connect) or die(mysql_error());
            $dropDb = "DROP DATABASE IF EXISTS `".$row['Database']."_backup-".$d."`";
            mysql_query($dropDb, $connect);
            $sql3 = "CREATE DATABASE `".$row['Database']."_backup-".$d."`";
            $query3=mysql_query($sql3,$connect) or die(mysql_error());                                                    
    
            while( $row2 = mysql_fetch_assoc($query2) ) {   
              foreach($row2 as $rand2) { 
                $sql4 = "CREATE TABLE `".$row['Database']."_backup-".$d."`.`".$rand2."` SELECT * FROM `".$row['Database']."`.`".$rand2."`";
                $query4=mysql_query($sql4,$connect);
              }              
            }
        }
    }
    

    That way there will be only one copy of backup for same date

    Update : You are getting mysql_fetch_assoc() expects parameter 1 to be resource because it was missing back ticks `` in table name of $sql2 & keep database name length below 16 character

    Tested and working

    点赞 评论 复制链接分享

相关推荐