在 Yii 2.0 中，数据库迁移时，查询 A 表的数据，插入至 B 表中的实现

1、A 表的数据结构，如图1

图1

2、B 表的数据结构，如图2

图2

3、现在的需求是，需要将 A 表中的 8 个字段查询出来，插入至 B 表中，使用如下命令来创建一个新的迁移，插入数据至 B 表中

./yii migrate/create insert_to_plan_attended_user_relation

4、yii/db/Migration 并没有提供数据库的查询方法。决定基于 Query Builder 查询构建器来构建和查询，如图3

图3

5、在生成的 /console/migrations/m190411_092923_insert_to_plan_attended_user_relation.php 文件中，代码如下

<?php

use yii/db/Migration;

/**
 * Class m190411_092923_insert_to_plan_attended_user_relation
 */
class m190411_092923_insert_to_plan_attended_user_relation extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {

    }

    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        echo "m190411_092923_insert_to_plan_attended_user_relation cannot be reverted./n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m190411_092923_insert_to_plan_attended_user_relation cannot be reverted./n";

        return false;
    }
    */
}

6、编辑 /console/migrations/m190411_092923_insert_to_plan_attended_user_relation.php 文件，以实现查询 A 表的数据，插入至 B 表中，基于批处理查询，每次只读取 10 行的数据到客户端内存 ( 本地环境为 10 行，后期可调整为 100 行 )

<?php

use yii/db/Connection;
use yii/db/Exception;
use yii/db/Migration;
use yii/db/Query;

/**
 * Class m190411_092923_insert_to_plan_attended_user_relation
 */
class m190411_092923_insert_to_plan_attended_user_relation extends Migration
{
    /**
     * {@inheritdoc}
     * @throws Exception if connection fails
     */
    public function safeUp()
    {
        // 创建一个单独的非缓存链接到数据库
        $unbufferedDb = new Connection([
            'dsn' => Yii::$app->db->dsn,
            'username' => Yii::$app->db->username,
            'password' => Yii::$app->db->password,
            'tablePrefix' => Yii::$app->db->tablePrefix,
            'charset' => Yii::$app->db->charset,
        ]);
        $unbufferedDb->open();
        // 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false
        $unbufferedDb->pdo->setAttribute(/PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
        $query = (new Query())
            ->from('{{%plan}}')
            ->orderBy(['id' => SORT_ASC]);
        /* 创建 MySQL 模型(选题与参与用户的关联) */
        $table = '{{%plan_attended_user_relation}}';
        $columns = ['group_id', 'config_column_id', 'plan_id', 'relation_user_id', 'role', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'];
        foreach ($query->batch(10, $unbufferedDb) as $plans) {
            // $plans 是一个包含100条或小于100条用户表数据的数组
            $rows = [];
            foreach ($plans as $plan) {
                $rows[] = [
                    $plan['group_id'],
                    $plan['config_column_id'],
                    $plan['id'],
                    $plan['exec_user_id'],
                    1,
                    1,
                    $plan['is_not_isolated'],
                    $plan['is_deleted'],
                    $plan['created_at'],
                    0,
                    $plan['deleted_at'],
                ];
            }
            $this->batchInsert($table, $columns, $rows);
        }
        $unbufferedDb->close();
    }

    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        echo "m190411_092923_insert_to_plan_attended_user_relation cannot be reverted./n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m190411_092923_insert_to_plan_attended_user_relation cannot be reverted./n";

        return false;
    }
    */
}

7、执行命令，总计 23 条记录，执行了 3 次 SQL 批量插入，结果如图4

图4

PS E:/wwwroot/pcs-api> ./yii migrate
Yii Migration Tool (based on Yii v2.0.15.1)

Total 1 new migration to be applied:
        m190411_092923_insert_to_plan_attended_user_relation

Apply the above migration? (yes|no) [no]:yes
*** applying m190411_092923_insert_to_plan_attended_user_relation
    > insert into {{%plan_attended_user_relation}} ... done (time: 0.013s)
    > insert into {{%plan_attended_user_relation}} ... done (time: 0.003s)
    > insert into {{%plan_attended_user_relation}} ... done (time: 0.002s)
*** applied m190411_092923_insert_to_plan_attended_user_relation (time: 0.050s)


1 migration was applied.

Migrated up successfully.

8、查看 B 表中的数据，总计 23 条记录，符合预期，如图5

图5