1. The data structure of Table A is shown in Figure 1.
2. The data structure of Table B is shown in Figure 2.
3. The current requirement is to query 8 fields from Table A and insert them into Table B. Use the following command to create a new migration that inserts data into Table B:
./yii migrate/create insert_to_plan_attended_user_relation
4. yii\db\Migration does not provide a database query method. I decided to build and execute the query using the Query Builder, as shown in Figure 3.
5. In the generated \console\migrations\m190411_092923_insert_to_plan_attended_user_relation.php file, the code is as follows:
<?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. Edit the \console\migrations\m190411_092923_insert_to_plan_attended_user_relation.php file to query data from Table A and insert it into Table B. Using batch processing, only 10 rows of data are read into client memory at a time (the local environment uses 10 rows, which can be adjusted to 100 rows later).
<?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. Execute the command. For a total of 23 records, 3 batch SQL inserts were executed. The result is shown in 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. Check the data in Table B. There are 23 records in total, which matches the expected result, as shown in Figure 5.
