在 Yii 2.0 中的命令行中，从A数据库迁移数据至B数据库的实现
1、将数据从csp的数据库中迁移至cloud的数据库中。`channel-pub-api-csp`.`cpa_channel_app_source`。条件查询出 95 条记录。如图1
图1
2、`channel-pub-api-cloud`.`cpa_channel_app_source`。总计查询出 158 条记录。如图2
图2
3、将数据从csp的数据库中迁移至cloud的数据库中。字段值变动：id，INSERT `channel-pub-api-cloud`.`cpa_channel_app_source`。编辑命令行脚本。/console/controllers/Migrate20210425Controller.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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <?php/** * Created by PhpStorm. * User: Qiang Wang * Date: 2021/04/25 * Time: 13:59 */namespace console\controllers;use Yii;use yii\console\Controller;use yii\console\ExitCode;use yii\db\Connection;use yii\db\Exception;/** * 数据库迁移(20210425) * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */class Migrate20210425Controller extends Controller{ /** * 将数据从csp的数据库中迁移至cloud的数据库中(仅导入视频兔兔下的相关数据) * * @return int * @throws Exception */ public function actionCspToCloud() { // 创建一个单独的非缓存链接到数据库(dsp) $cspDb = new Connection([ 'dsn' => 'mysql:host=localhost;dbname=channel-pub-api-csp', 'username' => 'mysql', 'password' => 'hqy-webtv', 'tablePrefix' => 'cpa_', 'charset' => 'utf8mb4', ]); $cspDb->open(); // 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false $cspDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); // 创建一个单独的非缓存链接到数据库(cloud) $cloudDb = new Connection([ 'dsn' => 'mysql:host=localhost;dbname=channel-pub-api-cloud', 'username' => 'mysql', 'password' => 'hqy-webtv', 'tablePrefix' => 'cpa_', 'charset' => 'utf8mb4', ]); $cloudDb->open(); // 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false $cloudDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $channelAppSources = $cspDb->createCommand('SELECT group_id, group_name, uuid, channel_id, channel_code, channel_type_id, channel_type_code, name, avatar, fans_count, source, source_uuid, source_product_id, source_user_token, permission, status, is_deleted, created_at, updated_at, deleted_at FROM {{%channel_app_source}} WHERE channel_type_code LIKE \'vtt_%\'')->queryAll(); $cloudDb->createCommand()->batchInsert('cpa_channel_app_source', ['group_id', 'group_name', 'uuid', 'channel_id', 'channel_code', 'channel_type_id', 'channel_type_code', 'name', 'avatar', 'fans_count', 'source', 'source_uuid', 'source_product_id', 'source_user_token', 'permission', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $channelAppSources)->execute(); $cspDb->close(); $cloudDb->close(); return ExitCode::OK; }} |
4、执行命令：./yii migrate20210425/csp-to-cloud。`channel-pub-api-cloud`.`cpa_channel_app_source`。总计查询出 253 条记录，新增 95 条记录。符合预期。如图3
图3
近期评论