更新重复密钥更新

Question

我有一个表playerspoints，它包含一个商店id和一个播放器的id，以及一个玩家的点。

  SHOP_ID   |     PLAYER_ID     |  POINTS
  ----------------------------------------
      1     |        7          |     66
      2     |        4          |     33

我想做的是从一家商店转到另一家商店。

• 问题:商店id和玩家id形成一个唯一的索引。
• 我想做的是对重复的键更新，而不是让它失败，添加一个条目的点到另一个，并删除“从”条目。

类似于：

UPDATE `playerspoints`
SET `boardId`=$to
WHERE `boardId`=$from
ON DUPLICATE KEY UPDATE `points`=.... 

你明白这个主意了吗？

Answer 1

您只能在ON DUPLICATE KEY区域中的一个冲突行的上下文中进行更改。此外，据我所知，这是INSERT语句的一个属性。

您需要的是一个简单的分类账，其中您记录的增减从一个平衡，然后表格，要么手动或使用触发器。

例如，最简单的方法是：

INSERT INTO points_adjustments (boardId_from, boardId_to, points)
  VALUES (?, ?, ?)

这可以更容易地表示为一对条目：

INSERT INTO points_adjustments (boardId, points)
  VALUES (?, ?)

您将为+n点添加一个条目，为-n添加一个匹配的条目。在任何时候，您都可以使用SUM(points)获得平衡。您可以将其封装在一个VIEW中，以便更容易地检索，或者如果需要，可以使用触发器将这些和降为另一个表的一列。

一个简单的触发器将为每个受影响的boardId发出以下语句

INSERT INTO balances (boardId, points) VALUES (?, ?)
  ON DUPLICATE KEY SET points=points+VALUES(points)

这首先避免了密钥冲突，并提供了发生的事务的可审计记录。

无论如何，要自动完成所有这些操作，您可能需要使用触发器。

第三方编辑

从docs 插入..。关于重复键更新语句

通常，应尽量避免对具有多个唯一索引的表使用ON重复键更新子句。

Answer 2

不是的。在MySQL中，不能在违反约束时删除记录。您可能会在更新前执行一个触发器来检查即将发生的约束冲突，但是即使到那时( 5.1)，您也不能修改同一个表的数据(在这种情况下，这可能会导致一个没完没了的循环)。

在塔德曼回答之前，只完成了一半。我个人喜欢他的主意。