php - 是否可以且安全地将 MySQL 语法组合用于 INSERT INTO SET 和 ON DUPLICATE KEY UPDATE(具有相同的列集)？

Question

我知道 INSERT INTO SET 和 ON DUPLICATE KEY UPDATE 的特定 MySQL 查询语法是如何工作的。但是，你能把它们结合起来吗？并且...您可以为 INSERT 和 UPDATE 部分使用相同的 $columnSet 字符串(见下文)，而将唯一字段保留在在那里？

考虑这个例子(我在我的服务器上使用 php 和 pdo):

// Define the query
$columnSet = "id=:id, date=:date, created=:created, modified=:modified, removed=:removed, synced=1, points=:points, comment=:comment";
$insertQuery = "INSERT INTO point_events SET"
               . $columnSet." ON DUPLICATE KEY UPDATE ".$columnSet;

   // Define the data
    $pointData['id'] = 'someuniqueid not auto incremented';
$pointData['created'] = 1234; // a timestamp
$pointData['modified'] = 1234; // a timestamp
$pointData['removed'] = NULL;
$pointData['points'] = 10;
$pointData['comment'] = "A comment";
$pointData['date'] = time();

// Execute the query. pdoModify is custom function that executes the query.
pdoModify($pdoNew, $insertQuery, $oldData); 

我见过的所有帖子都没有结合这两种语法，并且关于 ON DUPLICATE KEY UPDATE 的所有帖子似乎都建议您不应在 之后的字符串中包含(重复)键更新。那么，这安全吗？或者它的行为是否会有所不同/产生不需要的副作用。具体来说，我关心的是在 $columnSet 中为 UPDATE 保留唯一的 id。

Answer 1

我相信你的代码应该可以工作。通常，由于重复 id最初检查唯一键，保证当 ON DUPLICATE KEY 时它不会改变。子句被调用。

但是，这种行为不是最理想的(您正在更新未更改的字段)，而且非常严格:如果您需要增加 amount 该怎么办？与新插入的值，或连接新的 comment在现有的末尾？

正如 Rogue 评论的，MySQL语法有一个很好的功能，允许使用VALUES(<some column>)在 ON DUPLICATE KEY子句引用为 INSERT 传递的值。我建议使用它，因为它更优雅，并且为您提供更多的控制和灵 active 。

示例:

$insertQuery = 
    "INSERT INTO point_events SET"
    . $columnSet
    ." ON DUPLICATE KEY UPDATE"
    ." modified =  VALUES(modified)"                  # replace this value
    .", synced = 1"                                  # assign a fixed value
    .", amount = amount + VALUES(amount)"             # add the new value
    .", comment = CONCAT(comment, ', ', VALUES(comment))"    # concatenante the new value