mysql - 准备好的语句 : Duplicate Key Update with Where

Question

我有这些表:

stundenplan
+----+---------+-------+--------+---------+---------+------+
| id | user_id | tag   | stunde | fach    | lehrer  | raum |
+----+---------+-------+--------+---------+---------+------+
|  1 |       1 |     1 |      1 | MATH    | SM      | 101  |
|  2 |       1 |     1 |      2 | MATH    | SM      | 101  |
|  3 |       1 |     1 |      3 | MATH    | SM      | 101  |
|  4 |       1 |     1 |      4 | MATH    | SM      | 101  |
|  5 |       1 |     1 |      5 | MATH    | SM      | 101  |
|  6 |       1 |     1 |      6 | MATH    | SM      | 101  |
|  7 |       1 |     2 |      1 | MATH    | SM      | 101  |
|  8 |       1 |     2 |      2 | MATH    | SM      | 101  |
| .. |     ... |   ... |    ... |     ... |     ... |  ... |

users
+---------+----------+----
| user_id | username | ...
+---------+----------+----
|       1 | User1    | ...
+---------+----------+----

我的查询(这对我来说没有意义，因为更新部分缺少 user_id):

$sql = $db->prepare("
INSERT INTO stundenplan
    (tag, stunde, user_id, fach, lehrer, raum )
VALUES
    (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    fach = VALUES(fach),
    lehrer = VALUES(lehrer),
    raum = VALUES(raum)
");
$sql->bind_param('iiissi', $input_tag, $input_stunde, $input_fach, $input_lehrer, $input_raum);

如果我必须插入一些东西，查询将是这样的:

INSERT INTO stundenplan (tag, stunde, user_id, fach, lehrer, raum) VALUES (?, ?, ?, ?, ?, ?)

如果我必须更新查询将是这样的:

UPDATE stundenplan SET fach = ?, lehrer = ?, raum = ? WHERE user_id = ?

如何将这两个查询合并为一个，就像我以前尝试过的那样？

Answer 1

我只在 SQL 部分做了它(你的查询是正确的，但我通过 user_id 扩展了键):

    INSERT INTO stundenplan
       (tag, stunde, user_id, fach, lehrer, raum )
    VALUES
       (1,1,1,'MATH_NEW','SM',101)
    ON DUPLICATE KEY UPDATE
       fach = VALUES(fach),
       lehrer = VALUES(lehrer),
       raum = VALUES(raum);

请查看下面的演示。它在模式构建部分使用 ON DUPLICATE KEY UPDATE 并在插入时使用静态值(在 1,1,1 值上重复)。执行 select 语句后，您可以看到值已正确更新。

DEMO HERE

问题是您无法修改此查询中的 UPDATE 条件(它们与检测到的重复项完全相同)。所以可能的解决方案很少，都包含 2 个查询和事务:

• 从 stundenplan 中删除重复行并重新插入
• 插入不重复的行，然后更新重复行。

我还找到了一篇关于花式插入/更新的不错的文章 HERE