0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

任意の環境から別環境へDBの全データをコピーする(WebUI用のコード)MySQL編

Posted at

注)MySQL専用
管理ツールなどのWebUIで任意の環境から全データを別環境にコピーするような画面を作る時に使うコード
Laravel用のコードになっているので他で使う場合はDBクラスの部分を書き換えて。
※なおエンジニアが手動でコピーするならダンプ・リストアで終わりなので参考にならない。

用途
・開発環境がたくさんあってデータを統一するために
・開発環境から本番環境にデータ反映

コード

[xxxx]の部分はリクエストパラメータに置き換えてください。

$master_data_schema = '[マスターデータのスキーマ名]';
$conn_in = '[コピー元DBの接続文字列]';
$conn_dest = '[コピー先DBの接続文字列]';

$bulk_insert_chunk = 1000;
$table_exclude = [
    'テーブル名' => true,  // 除外したいテーブル名を配列のKEYにして設定しておく(issetでチェックしたいのでこの形式)
];

$table_list = [];
$column_list = [];
$master_data = [];
$tmp = DB::connection($conn_in)
    ->table('information_schema.tables')
    ->where('table_schema', $master_data_schema)
    ->get([
        'table_name',
        'table_comment',
    ]);
foreach ($tmp as $v) {
    $a = (array)$v;
    if (!isset($table_exclude[$a['table_name']])) {
        $table_list[$a['table_name']] = $a;
    }
}
$tmp = DB::connection($conn_in)
    ->table('information_schema.columns')
    ->where('table_schema', $master_data_schema)
    ->get([
        'table_name',
        'column_name',
        'column_type',
        'column_comment',
    ]);
foreach ($tmp as $v) {
    $a = (array)$v;
    if (!isset($table_exclude[$a['table_name']])) {
        $column_list[$a['table_name']][$a['column_name']] = $a;
    }
}
foreach ($table_list as $v) {
    $a = (array)$v;
    $count = 0;
    $chunk = 0;
    $tmp = DB::connection($conn_in)
        ->table($master_data_schema . '.' . $a['table_name'])
        ->orderBy('id')
        ->get(['*']);
    foreach ($tmp as $v2) {
        $d = (array)$v2;
        if ($count > $bulk_insert_chunk) {
            $count = 0;
            ++$chunk;
        }
        $master_data[$a['table_name']][$chunk][] = $d;
        ++$count;
    }
}
try {
    $table_name = null;
    DB::connection($conn_dest)->transaction(function () use (
        &$conn_dest,
        &$table_list,
        &$master_data,
        &$table_name,
        &$master_data_schema
    ) {
        foreach ($table_list as $v) {
            $table_name = $v['table_name'];
            DB::connection($conn_dest)->table($master_data_schema . '.' . $v['table_name'])->delete();
            if (isset($master_data[$v['table_name']])) {
                $insert_data = $master_data[$v['table_name']];
                foreach ($insert_data as $data) {
                    DB::connection($conn_dest)->table($master_data_schema . '.' . $v['table_name'])->insert($data);
                    unset($data);
                }
                unset($insert_data, $master_data[$v['table_name']]);
            }
        }
    });
} catch (Exception $e) {
    $sql_error = true;
    // エラー画面で表示する情報
    $error_table_name = $table_name;
    $error_message = explode(PHP_EOL, $e->getMessage())[0] . ' ...';
}
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?