php 如何更新postgres表中的多行(Yii2)

bybem2ql 于 2023-05-12 发布在 PHP

关注(0)|答案(2)|浏览(208)

$campaigns数据数组：

[
...
27 => array:10 [
    "name" => "0ca9b377dd066958_ru_acc9_map86"
    "cid" => "53099327454745088"
    "status" => 2
    "id" => 36842
  ]
...
]

更新前准备：

foreach ($campaigns as $campaign) {
            $id = $campaign['campaign_id'];
            $name = $campaign['name'];
            $status = $campaign['status'];
            $index = Campaign::getIndex($name);
            $aSource = Campaign::aSource($name, $status);
            $streamId = Campaign::getStreamIdByName($name);

            $updateValues[] = [
                'id' => $id,
                'name' => $name,
                'status' => $status,
                'index' => $index,
                'a_source' => $aSource,
                'stream_id' => $streamId,
                'updated_at' => new Expression('NOW()')
            ];
        }

我如何用name, status, index, a_source, stream_id和updated_at的新值更新$campaigns数组中ids中包含id的所有行？
请帮助我，我已经尝试了所有的选项从doc和chatGPT，像：
--批量插入

a) with `ON DUPLICATE KEY UPDATE` 
b) and `ON CONFLICT (id) DO UPDATE SET`

php

来源：https://stackoverflow.com/questions/76181047/how-to-update-many-rows-in-postgres-table-yii2

2条答案

按热度按时间

vjrehmav1#

我现在尝试了这种方式，如果你分享你的经验，我会很感激：

$transaction = \Yii::$app->db->beginTransaction();

        try {

            foreach ($campaigns as $campaign) {
                $cid = $campaign['id'];
                $name = $campaign['name'];
                $status = $campaign['status'];
                $index = Campaign::getIndex($name);
                $aSource = Campaign::aSource($name, $status);
                $streamId = Campaign::getStreamIdByName($name);

                // Update the row in the database
                \Yii::$app->db->createCommand()->update(
                    'campaigns',
                    [
                        'name' => $name,
                        'status' => $status,
                        'index' => $index,
                        'a_source' => $aSource,
                        'stream_id' => $streamId,
                        'updated_at' => new Expression('NOW()')
                    ],
                    ['cid' => $cid]
                )->execute();
            }

            $transaction->commit();

        } catch (\Exception $e) {
            $transaction->rollBack();
        }

赞(0）回复(0）举报 2023-05-12

cyvaqqii2#

如果活动的行数如此巨大，最好批量提取数据。它将给予您的内存/硬件资源使用得到控制。

$query = (new Query)->from('campaigns');
foreach ($query->batch() as $campaigns) {
    // $rows is an array of 100 (default) or fewer rows from campaigns table
$transaction = \Yii::$app->db->beginTransaction();

        try {

            foreach ($campaigns as $campaign) {
             ......
             }    

            $transaction->commit();

        } catch (\Exception $e) {
            $transaction->rollBack();
        }
}

https://www.yiiframework.com/doc/api/2.0/yii-db-query#batch()-detail

赞(0）回复(0）举报 2023-05-12

我来回答

php 如何更新postgres表中的多行(Yii2)

2条答案

相关问题

热门标签

最新问答