1. In the table pub_log, the type of the field data is text, as shown in Figure 1.
2. In the table pub_log, the data structure of the field data is json. The formatted data is as follows, as shown in Figure 2.
{"channel_app_source_uuid":"3efd0cbabf2a11e98f9d54ee75d2ebc1","channel_app_task_uuid":"569d5ddebf2a11e993cb54ee75d2ebc1","channel_code":"weibo","channel_type_code":"weibo_weibo_connect_web","channel_app_task_status":6}
{
"channel_app_source_uuid": "3efd0cbabf2a11e98f9d54ee75d2ebc1",
"channel_app_task_uuid": "569d5ddebf2a11e993cb54ee75d2ebc1",
"channel_code": "weibo",
"channel_type_code": "weibo_weibo_connect_web",
"channel_app_task_status": 6
}
3. The current requirement is that if “channel_app_task_status”: 6, it needs to be changed to “channel_app_task_status”: 7. That is, search for (“channel_app_task_status”: 6) and replace it with (“channel_app_task_status”: 7). Therefore, the final execution result should be as follows:
{"channel_app_source_uuid":"3efd0cbabf2a11e98f9d54ee75d2ebc1","channel_app_task_uuid":"569d5ddebf2a11e993cb54ee75d2ebc1","channel_code":"weibo","channel_type_code":"weibo_weibo_connect_web","channel_app_task_status":7}
{
"channel_app_source_uuid": "3efd0cbabf2a11e98f9d54ee75d2ebc1",
"channel_app_task_uuid": "569d5ddebf2a11e993cb54ee75d2ebc1",
"channel_code": "weibo",
"channel_type_code": "weibo_weibo_connect_web",
"channel_app_task_status": 7
}
4. Execute the SQL, which affects 1 row, as shown in Figure 3. The execution result shows that the data in the field data of the table pub_log has been modified to (“channel_app_task_status”: 7), which meets expectations, as shown in Figure 4.
UPDATE `cpa_pub_log` SET `cpa_pub_log`.`data` = REPLACE(`cpa_pub_log`.`data`, '"channel_app_task_status":6', '"channel_app_task_status":7');
5. Based on the database migration command, generate the implementation of the SQL shown above. Edit \console\migrations\m190814_093631_have_pub_number_and_task_status.php
<?php
use yii\db\Expression;
use yii\db\Migration;
/**
* Class m190814_093631_have_pub_number_and_task_status
*/
class m190814_093631_have_pub_number_and_task_status extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->update('{{%pub_log}}', ['data' => new Expression('REPLACE([[data]], \'"channel_app_task_status":6\', \'"channel_app_task_status":7\')')]);
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
echo "m190814_093631_have_pub_number_and_task_status cannot be reverted.\n";
return false;
}
/*
// Use up()/down() to run migration code without a transaction.
public function up()
{
}
public function down()
{
echo "m190814_093631_have_pub_number_and_task_status cannot be reverted.\n";
return false;
}
*/
}
6. After resetting it to 6, execute the database migration command, as shown in Figure 5.
PS E:\wwwroot\channel-pub-api> ./yii migrate
Yii Migration Tool (based on Yii v2.0.15.1)
Total 1 new migration to be applied:
m190814_093631_have_pub_number_and_task_status
Apply the above migration? (yes|no) [no]:yes
*** applying m190814_093631_have_pub_number_and_task_status
> update {{%pub_log}} ... done (time: 0.010s)
*** applied m190814_093631_have_pub_number_and_task_status (time: 0.022s)
1 migration was applied.
Migrated up successfully.
7. Check that the data in the field data of the table pub_log has been modified to (“channel_app_task_status”: 7), which meets expectations.
{"channel_app_source_uuid":"3efd0cbabf2a11e98f9d54ee75d2ebc1","channel_app_task_uuid":"569d5ddebf2a11e993cb54ee75d2ebc1","channel_code":"weibo","channel_type_code":"weibo_weibo_connect_web","channel_app_task_status":7}
{
"channel_app_source_uuid": "3efd0cbabf2a11e98f9d54ee75d2ebc1",
"channel_app_task_uuid": "569d5ddebf2a11e993cb54ee75d2ebc1",
"channel_code": "weibo",
"channel_type_code": "weibo_weibo_connect_web",
"channel_app_task_status": 7
}