前記事（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{
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()){
# テンプレート読み込み
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()){
my $cols = join(',',@cols);
#####################################
# テーブル名の末尾に個別にIDとかが付いてなければこの処理はいらない
$_ =~ /hoge_(.+?)(_.+?)?$/;
my $name = $1;
######################################
# 新しい構造の空テーブルを「pre_***」という名前で一旦作成
my $sth = $dbh->prepare("
$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ないのかなぁ。
ただ単にテーブルの構造とデータをコピーする場合、普通は、
---------------------------------------------------------------------------------------
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_$_ (") || die $dbh->errstr();$$tbl{$name}{'column'}) $$tbl{$name}{'option'}
$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ないのかなぁ。
