In Yii 2.0, modify a column of data in the MySQL 5.6 database (type: text, the data structure is: json) in the value of a field (that is, to search and replace some text in the field), based on one Implementation of SQL
1. Table: Fields in pub_log: The type of data is: text, as shown in Figure 1
Figure 1
2. Table: field in pub_log: data structure of data is: json, the formatted data is as follows, as shown in Figure 2
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 (“channel_app_task_status”: 6) and replace it with (“channel_app_task_status”: 7), so 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 sql, which affects 1 row, as shown in Figure 3, the execution result, the field in the pub_log: The data of the data has been modified to: (“channel_app_task_status”: 7), in line with expectations, as shown in Figure 4
Figure 3
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 a SQL implementation as shown above, edit \console\migration\m190814_093631_have_pub_number_and_task_status.php
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 reset to 6, execute the database migration command, as shown in Figure 5
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. View the table: the field in pub_log: The data of data has been modified to: (“channel_app_task_status”: 7), which is in line with 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
}
![在 phpMyAdmin 中登录时，提示：mysqli::real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]。](https://www.shuijingwanwq.com/wp-content/uploads/2025/03/1-1-503x245.jpg)
