在 Yii 2.0 中,修改 MySQL 5.6 数据库中的一列数据(类型为:text,数据结构为:json)的其中某个字段的值(即搜索并替换字段中的一些文本),基于一条 SQL 的实现
1、表:pub_log 中的字段:data 的类型为:text,如图1
2、表:pub_log 中的字段:data 的数据结构为:json,格式化后的数据如下,如图2
1 2 3 4 5 6 7 8 9 10 | {"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、现阶段的需求是,如果 “channel_app_task_status”: 6,需要修改为:”channel_app_task_status”: 7,即搜索(“channel_app_task_status”: 6) 并替换为 (“channel_app_task_status”: 7),因此最终的执行结果应该如下:
1 2 3 4 5 6 7 8 9 10 | {"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、执行 SQL,影响了 1 行,如图3,执行结果,表:pub_log 中的字段:data 的数据已经修改为: (“channel_app_task_status”: 7),符合预期,如图4
1 | 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、基于数据库迁移命令,生成如上所示的一条 SQL 的实现,编辑 \console\migrations\m190814_093631_have_pub_number_and_task_status.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 | <?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、重置为 6 之后,执行数据库迁移命令,如图5
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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、查看表:pub_log 中的字段:data 的数据已经修改为: (“channel_app_task_status”: 7),符合预期
1 2 3 4 5 6 7 8 9 | {"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 } |
近期评论