1. Migrate data from the csp database to the cloud database. `channel-pub-api-csp`.`cpa_channel_app_source`. A conditional query returns 95 records. See Figure 1.
2. `channel-pub-api-cloud`.`cpa_channel_app_source`. A total of 158 records are queried. See Figure 2.
3. Migrate data from the csp database to the cloud database. Field value changed: id, INSERT `channel-pub-api-cloud`.`cpa_channel_app_source`. Edit the command-line script. /console/controllers/Migrate20210425Controller.php
PHP
<?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. Execute the command: ./yii migrate20210425/csp-to-cloud. `channel-pub-api-cloud`.`cpa_channel_app_source`. A total of 253 records are queried, with 95 new records added. This matches the expectation. See Figure 3.
