MySQL:新しいテーブル構造に合わせてテーブルをコピー

前記事（http://www.igreks.jp/dev/2010/01/mysql.html）のようにALTER TABLEを使って更新するのもいいのだが、できれば構造のテンプレートに合わせて一発でコピーしたい。

ただ単にテーブルの構造とデータをコピーする場合、普通は、

---------------------------------------------------------------------------------------
CREATE TABLE sample_new LIKE sample_original
INSERT INTO sample_new SELECT * FROM sample_original
---------------------------------------------------------------------------------------


しかし、新しい構造のテーブルにあわせて、構造を変更しながらデータをコピーするといった方法が見つからないため、スクリプトの処理を合わせて実現してみる。
あんまりスマートじゃないけど・・・

まず、最新の構造のテンプレートを作っておく。


■最新のテーブル構造のテンプレート（tbl.pl）
---------------------------------------------------------------------------------------

package tbl;

sub conf{

my %tbl = (

'table_a' => {

'column' => '

id INT AUTO_INCREMENT PRIMARY KEY,
column_1 VARCHAR(36),
column_2 VARCHAR(255),
column_3 VARCHAR(255)

',
'option' => '

ENGINE = MyISAM DEFAULT CHARSET = utf8

'

},

'table_b' => {

'column' => '

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
column_1 INT,
column_2 VARCHAR(255),
column_3 VARCHAR(255)

',
'option' => '

ENGINE = MyISAM DEFAULT CHARSET = utf8

'

},

・
・
・

);
return \%tbl;

}
1;

---------------------------------------------------------------------------------------


テンプレートを読み込んで新しいテーブルを一時作成し、
そのテーブルに既存のテーブルのデータをインポートする。

■更新処理（tbl_update.pl）
---------------------------------------------------------------------------------------
#!/usr/bin/perl

package::main;

my $dbh;
・
・
・
# この辺でデータベースへの接続処理（詳細割愛）

print "Content-type: text/html\n\n";

print "<p>データベースの再構築を開始します</p>\n";

# 既存のテーブル名をリストで取得
my $sth = $dbh->prepare("SHOW TABLES LIKE 'hoge_%'") || die $dbh->errstr();
$sth->execute();
my @hoge_tbl = ();
while(my @tbl = $sth->fetchrow_array()){

push(@hoge_tbl,$tbl[0]);

}

# テンプレート読み込み
require "./tbl.pl";
my $tbl = &tbl::conf(); # ※リファレンスで受け取り

foreach(@hoge_tbl){

# 既存テーブルのカラム名をリストで取得
my @cols = ();
my $sth = $dbh->prepare("DESCRIBE $_") || die $dbh->errstr();
$sth->execute();
while(my $href = $sth->fetchrow_hashref()){

push(@cols,$href->{'Field'});  # unshiftしちゃだめよ

}
my $cols = join(',',@cols);

#####################################
# テーブル名の末尾に個別にIDとかが付いてなければこの処理はいらない
$_ =~ /hoge_(.+?)(_.+?)?$/;
my $name = $1;
######################################

# 新しい構造の空テーブルを「pre_***」という名前で一旦作成
my $sth = $dbh->prepare("

CREATE TABLE IF NOT EXISTS pre_$_ (

$$tbl{$name}{'column'}

) $$tbl{$name}{'option'}

") || die $dbh->errstr();
$sth->execute();

# 既存のテーブルのデータを、カラム名を明示的に指定して挿入
# これをしないとカラムの数が合いませんよ！と怒られる
my $sth = $dbh->prepare("INSERT INTO pre_$_ ($cols) SELECT * FROM $_"); # ※1
$sth->execute();

# 既存のテーブル削除
my $sth = $dbh->prepare("DROP TABLE IF EXISTS $_");
$sth->execute();

# 新しいテーブルのリネーム（"pre_"をとる）
my $sth = $dbh->prepare("ALTER TABLE pre_$_ RENAME $_");
$sth->execute();

}

print "<p>データベースの再構築が完了しました。</p>\n";

exit;
---------------------------------------------------------------------------------------


以上。

新しいテーブルにカラムが追加されてる場合は問題ないが、カラムが削除された場合は、多分※1のところでエラーになる・・・

こういうの一発でできるSQLないのかなぁ。