In Yii 2.0, when the database is migrated, the data of the A table is queried and inserted into the B table.
1. The data structure of the A table, as shown in Figure 1
Figure 1
2. The data structure of the B table, as shown in Figure 2
Figure 2
3. The current requirement is that 8 fields in the A table need to be queried, inserted into the B table, and the following commands are used to create a new migration, and the data is inserted into the B table.
./yii migrate/create insert_to_plan_attended_user_relation
4. Yii\db\migration does not provide the query method of the database. Decide to build and query based on Query Builder query builder, as shown in Figure 3
Figure 3
5. In the generated \Console\Migrations\M190411_092923_insert_to_plan_attended_user_relation.php, the code is as follows
6. Edit the \console\migrations\m190411_092923_insert_to_plan_attended_user_relation.php file to query the data of the A table, insert it into In the B table, based on the batch query, only 10 rows of data are read to the client memory at a time (the local environment is 10 rows, and the later period can be adjusted to 100 rows)
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. Execute the command, a total of 23 records, and execute 3 batches of SQL batch insertion, the result is shown in Figure 4
Figure 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. View the data in the B table, a total of 23 records, in line with expectations, as shown in Figure 5
Figure 5
