如何在CakePHP 3中实现INSERT ON DUPLICATE KEY UPDATE asert？ [英] How to implement INSERT ON DUPLICATE KEY UPDATE aka upsert in CakePHP 3?

问题描述

我使用CakePHP 3和MySQL。

I am using CakePHP 3 and MySQL.

我想在DUPLICATE KEY UPDATE上实现 INSERT a upsert 通过 CakePHP 3 模型查询。

I would like to implement a INSERT on DUPLICATE KEY UPDATE aka upsert query via CakePHP 3 model.

strong>如下表所示：

Given the following table:

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | admin    |  33 |
|  2 | Timmy    |  17 |
|  3 | Sally    |  23 |
+----+----------+-----+

b $ b

其中 id 是主键，用户名是唯一索引

当我有以下值等待被插入：

When I have the following values awaiting to be upserted:

Felicia, 27
Timmy, 71

我希望在之后 >

I expect the following result after the upsert:

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | admin    |  33 |
|  2 | Timmy    |  71 |
|  3 | Sally    |  23 |
|  4 | Felicia  |  27 |
+----+----------+-----+

b $ b

我知道如何在MySQL查询中执行upsert ：

INSERT INTO `users` (`username`, `age`) 
VALUES ('Felicia', 27), ('Timmy', 71) 
ON DUPLICATE KEY UPDATE 
`username`=VALUES(`username`),`age`=VALUES(`age`);

我知道如何在CakePHP3的多个查询中执行此操作。

   $newUsers = [
        [
            'username' => 'Felicia',
            'age' => 27,
        ],
        [
            'username' => 'Timmy',
            'age' => 71,
        ],
    ];

    foreach ($newUsers as $newUser) {
        $existingRecord = $this->Users->find()
            ->select(['id'])
            ->where(['username' => $newUser['username']])
            ->first();

        if (empty($existingRecord)) {
            $insertQuery = $this->Users->query();
            $insertQuery->insert(array_keys($newUser))
                ->values($newUser)
                ->execute();
        } else {
            $updateQuery = $this->Users->query();
            $updateQuery->update()
                ->set($newUser)
                ->where(['id' => $existingRecord->id])
                ->execute();
        }
    }

我想知道的是：

有一种方法可以使用CakePHP 3在一行中进行升级，即使我使用链接吗？

请告诉我如何实现。

推荐答案

使用http://stackoverflow.com/a/24990944/80353 ，我想使用问题中提供的代码示例来重新整理。

Using the answer provided at http://stackoverflow.com/a/24990944/80353, I want to rephrase this using the code sample given in the question.

对以下记录运行upsert：

To run upsert on the following records

Felicia, 27
Timmy, 71

到此表中

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | admin    |  33 |
|  2 | Timmy    |  17 |
|  3 | Sally    |  23 |
+----+----------+-----+

b $ b

最好的方法是把它写成

the best way is to write it as

$newUsers = [
    [
        'username' => 'Felicia',
        'age' => 27,
    ],
    [
        'username' => 'Timmy',
        'age' => 71,
    ],
];

$columns = array_keys($newUsers[0]);

$upsertQuery = $this->Users->query();

$upsertQuery->insert($columns);

// need to run clause('values') AFTER insert()
$upsertQuery->clause('values')->values($newUsers);

$upsertQuery->epilog('ON DUPLICATE KEY UPDATE `username`=VALUES(`username`), `age`=VALUES(`age`)')
                ->execute();

检查 有关 epilog

请查看此了解如何在单个查询中写入插入多个记录的详细信息

Check this out for details on how to write insert multiple records in single query

这篇关于如何在CakePHP 3中实现INSERT ON DUPLICATE KEY UPDATE asert？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！