在 Yii 2.0 中，数据库迁移时，查询 A 表的数据，插入至 B 表中的实现
1、A 表的数据结构，如图1
图1
2、B 表的数据结构，如图2
图2
3、现在的需求是，需要将 A 表中的 8 个字段查询出来，插入至 B 表中，使用如下命令来创建一个新的迁移，插入数据至 B 表中
1 | ./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 文件中，代码如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | <?phpuse 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 行 )
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | <?phpuse 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | PS E:\wwwroot\pcs-api> ./yii migrateYii Migration Tool (based on Yii v2.0.15.1)Total 1 new migration to be applied: m190411_092923_insert_to_plan_attended_user_relationApply 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
近期评论