在 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
近期评论