如何执行UPSERT操作，以便在更新部分中同时使用新值和旧值。

18 浏览
0 Comments

如何执行UPSERT操作，以便在更新部分中同时使用新值和旧值。

愚蠢但简单的例子：

假设我有一个名为“Item”的表，其中存储了接收到的物品的总数。

物品名称在这里是主键。每当我收到数量为X的物品A时，我如何实现以下目标：

如果该物品不存在，我插入一个新的记录，物品A的库存设置为X，如果存在一条记录，其中库存物品为Y，则库存物品的新值为（X + Y）。

我的问题是，我在实际表格中有多个列。在更新部分写多个select语句是个好主意吗？

当然，我可以在代码中完成，但是否有更好的方法？

0

3 答案

0 Comments

UPSERT是一种在数据库中执行插入或更新操作的方法。在某些情况下，我们希望在更新数据时能同时使用新旧值。以下是一个出现这个问题的示例：

假设我们想要按用户和日期添加七天的数据。我们希望userid和day的组合在表中是唯一的。表的创建语句如下：

CREATE TABLE `table_name` (
  `userid` char(4) NOT NULL,
  `day` char(3) NOT NULL,
  `open` char(5) NOT NULL,
  `close` char(5) NOT NULL,
  UNIQUE KEY `seven_day` (`userid`,`day`)
);

我们的查询语句将如下所示：

INSERT INTO table_name (userid,day,open,close) 
    VALUES ('val1', 'val2','val3','val4') 
        ON DUPLICATE KEY UPDATE open='val3', close='val4';

以上是一个PHP的例子，它使用循环将数据插入表中：

<?php
$data= array(
        'sat'=>array("userid"=>"1001", "open"=>"01.01", "close"=>"11.01"),
        'sun'=>array("userid"=>"1001", "open"=>"02.01", "close"=>"22.01"),
        'sat'=>array("userid"=>"1001", "open"=>"03.01", "close"=>"33.01"),
        'mon'=>array("userid"=>"1002", "open"=>"08.01", "close"=>"08.01"),
        'mon'=>array("userid"=>"1002", "open"=>"07.01", "close"=>"07.01")
    );
foreach($data as $day=>$info) {
    $sql = "INSERT INTO table_name (userid,day,open,close) 
                VALUES ('$info[userid]', '$day','$info[open]','$info[close]') 
            ON DUPLICATE KEY UPDATE open='$info[open]', close='$info[close]'";
    mysql_query($sql);
}
?>

通过执行上述代码，我们可以在表中看到以下数据：

+--------+-----+-------+-------+
| userid | day | open  | close |
+--------+-----+-------+-------+
| 1001   | sat | 03.01 | 33.01 |
| 1001   | sun | 02.01 | 22.01 |
| 1002   | mon | 07.01 | 07.01 |
+--------+-----+-------+-------+

需要注意的是，上述代码使用的是已在PHP 7中删除的mysql_*函数。在替换时，可以考虑使用PDO或MySQLi等预处理/参数化查询。此外，我们还应该注意，代码中没有直接将数据连接到查询中，而是对数据进行了转义处理，以防止SQL注入攻击。

0
0 Comments

在这段内容中，问题的原因是在执行插入操作时，由于cellId和entityRowId相同，导致出现了重复键错误。解决方法是使用upsert命令，即在插入操作中使用ON DUPLICATE KEY UPDATE语句来更新值。

首先，创建一个表并指定想要在哪些列上创建唯一索引。然后插入一些值，再次尝试执行相同的插入操作，会出现重复键错误。因此，需要使用upsert命令来实现更新操作。

具体的解决方法是，在插入操作中使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句。这条命令会将已存在的值作为value，并执行value = value + 新值 的更新操作。

通过执行以上命令，最终表中只会有一行数据，并且值将变为301.0。

这是一个很好且有解释性的答案。

0
0 Comments

问题的原因：

在进行UPSERT操作时，需要同时使用新值和旧值进行更新，但是在给出的示例中，使用了子查询来引用触发ON DUPLICATE KEY的行，从而导致了代码的复杂性增加。

解决方法：

可以使用以下代码来进行UPSERT操作，不需要使用子查询：

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = `new_items_count` + 27

需要注意的是，当使用InnoDB引擎时，ON DUPLICATE KEY UPDATE子句将会增加自增字段的值，而不管是INSERT触发还是UPDATE触发。可以参考MySQL参考手册中的说明。

为了避免代码重复，可以使用`new_items_count = new_items_count + VALUES(items_in_stock)`来简化代码。

另外，是否可以使用REPLACE查询来实现呢？这样做会更简单吗？

“记住，大多数事情都是很简单的，如果你发现自己在处理本应该简单的事情时过于复杂，那么你很可能是在走错方向。”

0