mysql - 如何在 MySQL 表中创建一条新记录作为前一个记录的副本，仅进行差异更改？

Question

我正在为将数据发布到我的服务器的第三方服务构建端点。为了减少带宽，第三方仅发送先前记录字段的更改。

想象一下表格看起来像这样，有第一条记录:

+----+------+------+-------+-------+-------+
| id | item | size | color | state | count |
+----+------+------+-------+-------+-------+
|  1 | 0001 |  500 | blue  | on    |    12 |
+----+------+------+-------+-------+-------+

下一个可能发送给我的数据只是item: 0001, color: green, count: 15，我必须插入一条记录，所以表格如下所示:

+----+------+------+-------+-------+-------+
| id | item | size | color | state | count |
+----+------+------+-------+-------+-------+
|  1 | 0001 |  500 | blue  | on    |    12 |
|  2 | 0001 |  500 | green | on    |    15 |
+----+------+------+-------+-------+-------+

请注意 size 和 state 字段如何保持不变，但会向下复制到下一行。 (id 只是一个自增键)

此外，每个 item 标识符都是唯一的。因此，如果我得到一些不同的 item 记录，表格将如下所示:

+----+------+------+-------+-------+-------+
| id | item | size | color | state | count |
+----+------+------+-------+-------+-------+
|  1 | 0001 |  500 | blue  | on    |    12 |
|  2 | 0001 |  500 | green | on    |    15 |
|  3 | 0002 |  700 | white | off   |    42 |
|  4 | 0001 |  500 | red   | off   |     9 |
|  5 | 0003 |  300 | black | on    |    66 |
+----+------+------+-------+-------+-------+

然后我收到此消息:item: 0002, size: 100 表格将如下所示:

+----+------+------+-------+-------+-------+
| id | item | size | color | state | count |
+----+------+------+-------+-------+-------+
|  1 | 0001 |  500 | blue  | on    |    12 |
|  2 | 0001 |  500 | green | on    |    15 |
|  3 | 0002 |  700 | white | off   |    42 |
|  4 | 0001 |  500 | red   | off   |     9 |
|  5 | 0003 |  300 | black | on    |    66 |
|  6 | 0002 |  100 | white | off   |    42 |
+----+------+------+-------+-------+-------+

有没有一种方法可以单独使用 MySQL 来完成此操作？存储过程或类似的东西？

Answer 1

INSERT INTO ... SELECT 可能会让您走得更远。考虑以下插入语句:

INSERT INTO yourTable (item, size, color, state, count)
SELECT '0001', t1.size, 'green', t1.state, 15
FROM yourTable t1
INNER JOIN
(
    SELECT item, MAX(id) AS max_id
    FROM yourTable
    GROUP BY item
) t2
    ON t1.item = t2.item AND
       t1.id   = t2.max_id
WHERE t1.item = '0002';

我在这里假设您的后端技术将接收您要插入的 item 以及新的列值。

请注意，如果通过准备好的语句执行上述操作，则查询看起来更像这样:

INSERT INTO yourTable (item, size, color, state, count)
SELECT ?, t1.size, ?, t1.state, ?
FROM yourTable t1
INNER JOIN
(
    SELECT item, MAX(id) AS max_id
    FROM yourTable
    GROUP BY item
) t2
    ON t1.item = t2.item AND
       t1.id   = t2.max_id
WHERE t1.item = ?;

然后您将为占位符绑定(bind)值，即此查询可能用于每个插入请求。

对此的一个轻微替代方案是在单个事务中执行完整选择，然后执行插入。但是，如果您可以在单个查询中执行此操作，那么它可能比两个查询更可取。