php - 在 MySQL 中执行更新时，对重复键求和 `counts` 列

Question

我有一个计数表，我希望能够在重命名复合键字段之一时对计数进行求和。这很难解释，所以让我向您展示一些 SQL:

示例表:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`,`type`)
);

插入一些数据:

INSERT INTO test VALUES(1, 10, 1);
INSERT INTO test VALUES(2, 20, 3);
INSERT INTO test VALUES(2, 10, 3);
INSERT INTO test VALUES(2, 30, 3);

查询数据:

mysql> SELECT SUM(count) as count FROM test WHERE id = 2;
+-------+
| count |
+-------+
|     9 |
+-------+

mysql> SELECT SUM(count) as count FROM test WHERE type = 10;
+-------+
| count |
+-------+
|     4 |
+-------+

效果很好，快速且灵活。

现在，我想重新映射类型 10 到类型 20

UPDATE test SET type = 20 WHERE type = 10;
Duplicate entry '2-20' for key 'PRIMARY'

此处使用ON DUPLICATE KEY UPDATE是无效的。我认为通过创意插入应该可以实现，但我不确定。有人能想到一种方法吗？

Answer 1

一种方法是“放松”主键，也就是说，将其从主(唯一)键更改为非唯一键。这将允许重复值，并允许您的 UPDATE 语句成功，这样您将有两个(或更多)行具有匹配的(id，type)。(请注意，这会使更新单行出现问题，因此您可能需要添加一个可以是唯一的新列。AUTO_INCREMENT 列可以很好地实现这一点。)

<小时/>

如果您不想这样做，那么另一种方法是将类型 10 和类型 20 行的计数“组合”在一起(对于每个 id)到类型 20 行中，设置类型 20 的行的计数将 10 行键入为零，并(可选)在单独的语句中删除多余的类型 10 行。

下面的语句通过在第一个选择中将 10 值“映射”到 20 来“组合”类型 10 和类型 20 计数。

<小时/>

 -- combine count for types 10 and 20 as new count for type 20
 -- and set count to zero for type 10

 INSERT INTO test (id, `type`, `count`)
 SELECT t.id
      , IF(t.`type`=10,20,t.`type`) AS new_type
      , SUM(t.`count`) AS `count`
   FROM test t
  WHERE t.`type` IN (10,20)
  GROUP BY id, new_type
  UNION ALL
 SELECT u.id
      , u.`type`
      , 0 AS `count`
   FROM test u
  WHERE u.`type` = 10
     ON DUPLICATE KEY UPDATE `count` = VALUES(`count`);

 -- remove unnecessary type 10 rows.
 DELETE FROM test WHERE `type` = 10 AND `count` = 0;

<小时/>

注意:第一个 SELECT 中的 IF() 表达式相当于:

CASE WHEN t.type = 10 THEN 20 ELSE t.type END

第二个选择为我们提供了所有需要更新的类型 10 行。我们使用 UNION ALL 运算符连接这两个结果集。

然后我们获取组合(连接)的结果集，并将它们运行到插入中。在任何我们点击“重复”键的地方，我们都会通过 ON DUPLICATE KEY 子句执行更新操作。

您可能希望在事务中执行此操作(如果您使用的是 InnoDB)，并在执行 DELETE 之前验证第一个语句是否成功完成。如果第一个语句抛出异常(可能存在不稳定的触发器)，那么您可能需要回滚事务。如果 DELETE 由于某种原因失败(可能违反了外键约束)，您还需要回滚。

或者，您不一定需要执行 DELETE，您可以将类型 10 行保留为计数为零。

<小时/>

重要:

请勿同时实现这两种解决方案!只有其中之一。

第一种方法是架构更改，不需要任何数据更改。该更改将使您的更新成功。

第二种方法要求架构保持相同，并且取决于 (id,type) 上 UNIQUE KEY 的存在(和强制执行)。