DBから取得した値を加工して別のDBへ挿入したい

質問

MySQLから取得した値を加工して、別のMySQLへ挿入したいのですが、
どうすればよいでしょうか？

例えば、リンク先の$row["Name"]に「さん」を付与して、別のDBへ挿入したい場合

▽PHP: mysqli_result::fetch_assoc - Manual
　 http://www.php.net/manual/ja/mysqli-result.fetch-assoc.php
例1 オブジェクト指向型を参考に、連想配列を取得するところまでは出来たのですが、
この後、どうすればよいでしょうか？

■分からないこと1
・連想配列で取得したデータを、$mysqli->close();した後でも利用するにはどうすれば良いでしょうか？
whileの中で、
$data["Name"]=$row["Name"];
$data["CountryCode"])=$row["CountryCode"]);
とやると1件しか取得できません。

whileの中で、
$data["Name"].=$row["Name"];
$data["CountryCode"]).=$row["CountryCode"]);
とやると、ちょっとイメージと異なる配列になります。

■分からないこと2
$mysqli->close();した後、$newmysqli = = new mysqli("localhost", "my_user", "my_password", "world");を書いて、もう一度同じようなことをやる感じなのでしょうか？
一回、閉じてから、新しいDBへ接続する流れなのでしょうか？

回答No.5

めとろいと（@naktak） · ベストアンサー

すみません、もしOLDTABLE側のカラム値がint型、bigint型などの
数値型であるとおっしゃっており、時分秒まで格納されていた場合は
以下になります。

INSERT INTO NEWDB.NEWTABLE (
　　NAME, CREATED
)
SELECT CONCAT(NAME, 'さん'),
　　　　DATE_FORMAT(CREATED, '%Y-%m-%d %H:%i:%s')
FROM　　OLDDB.OLDTABLE

実行結果はこんな感じになるでしょう。

mysql> use olddb;
Database changed
mysql> select * from oldtable;
+--------+----------------+
| name | created |
+--------+----------------+
| テスト | 20120810122334 |
+--------+----------------+
1 row in set (0.01 sec)

mysql> use newdb;
Database changed
mysql> select * from newtable;
Empty set (0.00 sec)

mysql> INSERT INTO NEWDB.NEWTABLE (
-> NAME, CREATED
-> )
-> SELECT CONCAT(NAME, 'さん'),
-> DATE_FORMAT(CREATED, '%Y-%m-%d %H:%i:%s')
-> FROM OLDDB.OLDTABLE;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from newtable;
+------------+---------------------+
| name | created |
+------------+---------------------+
| テストさん | 2012-08-10 12:23:34 |
+------------+---------------------+
1 row in set (0.00 sec)

お礼

何度も回答いただき、ありがとうございました。
大変参考になりましたー

回答No.4

めとろいと（@naktak）

やり方としてはこうです。

INSERT INTO NEWDB.NEWTABLE (
　　NAME, CREATED
)
SELECT CONCAT(NAME, 'さん'),
　　　　CREATED
FROM　　OLDDB.OLDTABLE

TIMESTAMPからDATETIMEに置き換えて登録したいということであれば、
いずれも内部ではタイムスタンプ扱いなので、特に変換かける
必要はありません。

お礼

回答ありがとうございましたー

回答No.3

めとろいと（@naktak）

> $data[] = $row;としました
なるほどなるほど、それなら、まぁありっちゃありですね。
その分メモリを食うわけですが。

> 違うDBへ接続する時は、具体的に、どうなるのでしょうか？
$res1 = mysqli_connect($host1, $user1, $pass1); //これは接続先1のリソース
$res2 = mysqli_connect($host2, $user2, $pass2); //これは接続先2のリソース
接続リソースごとにSQLを発行したりcloseすればよいです。
mysqli_query()のSQL発行命令は、上記で取得した
接続リソースを引数に指定して発行します。
同様にフェッチする命令も、mysqli_query()からの
戻り値であるリソースを引数に指定してフェッチします。

DB間のデータ移動については、具体例が＃２で挙がっているので、その通りにすればよいです。
移動先DBに接続して、移動先DBでSQLを発行すればよいです。
当然、同一DBサーバ内であることと、移動元、移動先ともに適切なアクセス権限が
なければなりませんが。
ここの手順の3つ目がまさにそれです。
http://tagajo.blogspot.jp/2010/12/mysql.html

DBサーバが分かれていたり、あくまでPHPスクリプトを介して処理したい
場合は手順はこんな感じです。
１．移動元DBに接続
２．移動先DBに接続
３．移動元DBへSELECT発行
　　※ここ繰り返し
　　３－１．結果をフェッチ
　　３－２．フェッチしたデータを加工
　　３－３．移動先DBへINSERT発行
４．移動元DBを切断
５．移動先DBを切断

元々のやり方だと$data[]分をINSERT時に更にループしなければならないので、とても非効率です。
とはいえ、上記方法も何回もSQLを発行するしPHPスクリプトが密に絡むので、圧倒的に遅いです。

DBサーバが分かれている場合は、DBリンクの機能もMySQL5.0くらいから
あるようですが、私は使ったことがありません。
それが出来るようなら、DBサーバが分かれていたとしても、SQL発行だけで済むでしょう。
http://dev.mysql.com/doc/refman/5.1/ja/federated-create.html

補足

回答ありがとうございます。

PHPスクリプトを介する処理を丁寧に解説いただき(サンプルリンク先まで探していただき)、大変参考になりました。
また、今回のケースでは、SQLで処理可能だということも分かりました。

現状、「３－２．フェッチしたデータを加工」まで出来、後は「３－３．移動先DBへINSERT発行」するだけのところまで来たので、
今度は、PHPを介さない処理にも挑戦しているのですが、ちょっと詰まってしまいました。

2さんの補足に記入したのですが、
何か気がついた点等あれば、アドバイスよろしくお願いします

回答No.2

kosukejlampnet

先の回答の補足を読んでの回答になります。
SQL文のみで行うことが望ましいと思います。

INSERT INTO `NEW_DB`.`NEW_TABLE`
SELECT
CONCAT(`name`, 'さん')
, DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%M-%s')
FROM `OLD_DB`.`OLD_TABLE`

こんな風にMySQLの関数を使えば、間違いも起きません。
PHPを通すと、タイムアウトを考慮したり、なにかと面倒です。

補足

回答ありがとうございます。
早速試してみたのですが、異なるDB間だと、環境的にエラーになりました(ユーザー権限？)
#1142 - SELECT command denied to user '○○'@'localhost' for table '△△'

そこで、DUMPテーブルデータをインポートして、同じDB内で処理すればいいかな、と思ったので、その辺りについて教えてください。

■最終的にやりたいこと
・元テーブル「created」カラムでint保存しているタイムスタンプを、新テーブルではdatetime型カラムへ変換したい

■試したこと
INSERT INTO `NEW_TABLE` ( created )
SELECT DATE_FORMAT( `created` , '%Y-%m-%d %H:%M-%s' )
FROM `OLD_TABLE`( created )
としたら、

＞43 行挿入しました。
＞id 227 の行を挿入しました ( クエリの実行時間 0.0028 秒 )

と表示はされるのですが、実際には、「created」カラムの中身はNULLになってしまいます。

■手順
・元テーブルのDUMPデータを、DBにインポートした後、どうすれば良いのでしょうか？
・まず初めに、新テーブルを作成するのでしょうか？
・そして、新テーブルの「created」カラムを、intからdatetime型へ変換(この時点で、データがnull)？
・最後に、INSERT INTO～SQL文を実行？ それとも、UPDATE文の方がいいのでしょうか？

回答No.1

めとろいと（@naktak）

> MySQLから取得した値を加工して、別のMySQLへ挿入したいのですが

DBから抽出して$row["Name"]を取得しているのですか？

１．
ちょっとMySQLがどうだったか試したことなくて試す環境もなくて
分かりませんが、一般的に、DB接続をcloseしたあとにフェッチは
できないので、そもそもがおかしいです。

２．
DB接続をcloseしたあとは再度接続しなくてはなりません。
そもそもcloseせず、続けてSQLを発行し、処理が完全に終わったら
終了すればいいです。
特別な理由がない限り、いちいちcloseする必要はありません。

しかしながら、DBからDBへ、ならば、

INSERT INTO TBLA (
　Name, COL2, COL3
)
SELECT
　concat(Name, 'さん'),
　COL1,
　COL2
FROM
　TBLB
WHERE
　COL2 = うんたらかんたら;

という方法も考慮してはいかがでしょうか？

補足

回答ありがとうございました。

＞DBから抽出して$row["Name"]を取得しているのですか？
はい

＞１．
＞一般的に、DB接続をcloseしたあとにフェッチはできない
DB接続をcloseしたあとに変数として保持したかったので、
$data[] = $row;としました

＞２．
＞DB接続をcloseしたあとは再度接続しなくてはなりません。
＞そもそもcloseせず、続けてSQLを発行し、処理が完全に終わったら終了すればいいです。
違うDBへ接続する時は、具体的に、どうなるのでしょうか？
$newmysqli = = new mysqli(部分は、インスタンスを生成している(？)ので、ここで接続しているということなのでしょうか？

＞しかしながら、DBからDBへ、ならば
質問がややこしくなりそうだったので、【row["Name"]に「さん」を付与して】と書いたのですが、
本当にやりたいことは、intで保存しているタイムスタンプを
date("Y-m-d H:i:s", $created)
とかで変換して、別DBのdatetime型カラムへ挿入したいのです。
(最終的にやりたいのは、異なるDB構成へのデータ移行です)

DBからデータ取得して、データを一部PHPで加工して、異なるDBへ挿入したいのですが、
どういう手順で行なえば良いのでしょうか？

■現状
・旧DBよりデータ取得
・多次元配列でデータを取得
while ($row = $result->fetch_assoc()) {
$data[] = $row;

・この多次元配列のデータを一部加工して、別DBへ突っ込みたいのですが、
多次元配列のデータ配列の一部を加工して、戻す方法がよく分かりません。

DBからデータ取得して、データを一部PHPで加工して、異なるDBへ挿入しているような、簡単なサンプル、どこかにないでしょうか？