在MySQL中复制数据库，如何复制视图？

Question

尝试使用在MySQL服务器上运行SQL查询的PHP脚本将数据库复制到新数据库中。到目前为止，我拥有的代码是：

$dbh->exec("CREATE DATABASE IF NOT EXISTS $new_news CHARACTER SET UTF8;");
$results = $dbh->query("SHOW TABLES FROM $old_news");
$table_list = $results->fetchAll(PDO::FETCH_NUM);

foreach($table_list as $table_row){
    foreach($table_row as $table){
        $results = $dbh->query("SELECT table_type FROM information_schema.tables where table_schema = '$old_news' and table_name = '$table'");
        $table_type = $results->fetch(PDO::FETCH_ASSOC);
        $table_type = $table_type['table_type'];
        if($table_type == 'BASE TABLE'){
            echo "Creating table $table and populating...\n";
            $dbh->exec("CREATE TABLE $new_news.$table LIKE $old_news.$table");
            $dbh->exec("INSERT INTO $new_news.$table SELECT * FROM $old_news.$table");
        }else if($table_type == 'VIEW'){
            //echo "Creating view $table...\n";
            //$dbh->exec("CREATE VIEW $new_news.$table LIKE $old_news.$table");
            echo "$table is a view, which cannot be copied atm\n";  
        }else{
            echo "Skipping $table_type $table, unsupported type\n"; 
        }
    }
}

这将查看$old_news中的所有表，查找information_schema中的表类型，并根据类型在$new_news中创建一个相同的表。对于表，它创建相同的表结构，然后'INSERT INTO SELECT‘来填充它们。

如何在没有mysqldump整个数据库的情况下复制视图？

Answer 1

您可以使用INFORMATION_SCHEMA.VIEWS查看视图定义，或者显示整个语句的CREATE view。

Answer 2

一种选择是对每个视图使用SHOW CREATE VIEW，这将返回视图创建语法。

详情请参见。

Answer 3

下面的代码对我很有效。它会将所有视图从source_database复制到target_database

$link = mysql_connect('hostname', 'user', 'password',false,128) or die(mysql_error());
mysql_select_db('source_database') or die(mysql_error());

$qry=mysql_query("SHOW FULL TABLES IN source_database WHERE TABLE_TYPE LIKE 'VIEW'");

while ($rows= mysql_fetch_object($qry)){
        $view_name=$rows->Tables_in_techdb_beta;
        $select_view=mysql_query("SHOW CREATE VIEW ".$view_name);
        while ($view_rows= mysql_fetch_object($select_view)){
            foreach($view_rows as $key=>$val){
                if ($key=='Create View'){
                    $views_array[]=$val;
                }
            }
        }
}
//echo "<pre>"; print_r($views_array);
$link2 = mysql_connect('hostname', 'user', 'password',false,128) or die(mysql_error());
mysql_select_db('target_database') or die(mysql_error());
ini_set('MAX_EXECUTION_TIME', -1);
foreach($views_array as $key => $val){
    $sql=trim($val);
    mysql_query($sql);
}