如果存在具有 2 个特定列的行，则 MYSQL 更新表；如果不存在，则插入新行

Question

我有一个 MYSQL 表，如下所示:

id    id_jugador    id_partido    team1     team2
1        2              1           5         2
2        2              2           1         1
3        1              2           0         0

我需要创建一个查询来在表中插入新行或更新表。条件基于 id_jugador 和 id_partido，这意味着如果我想插入 id_jugador = 2 和 id_partido = 1，那么它应该只用我发送的新 team1 和 team2 值更新现有行。并且不要重复该行。

但是，如果我有一个条目 id_jugador=2 和 id_partido=3，由于此组合尚不存在，因此应该添加新行。

我读到了有关 REPLACE INTO 的内容，但它似乎无法检查唯一键的组合。

Answer 1

如果您在两列(id_jugador, id_partido)上定义了唯一键，那么您可以使用:

INSERT ... ON DUPLICATE KEY ...

例如

INSERT INTO mytable (id_jugador, id_partido, team1, team2) 
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY
UPDATE team1 = VALUES(team1)
     , team2 = VALUES(team2)

(显然，我假设 id 是一个 AUTO_INCRMENT PRIMARY KEY，或者有一个 BEFORE INSERT 触发器在未提供 id 时生成一个值。)

MySQL 将尝试插入(使用 auto_increment id 值)。如果INSERT成功，MySQL就完成了，这只是一个常规的插入。但如果INSERT抛出DUPLICATE KEY异常，那么MySQL就会执行更新 Action ，相当于:

UPDATE mytable SET team1 = ?, team2 = ? WHERE id_jugador = ? AND id_partido = ?

请注意，一个表上可以定义多个 UNIQUE KEY 约束。对于 UPDATE 操作的谓词，MySQL 将使用 DUPLICATE KEY 异常中标识的唯一(或主)键的列/值。

另请注意，MySQL 确实会尝试 INSERT，因此即使 INSERT 抛出 DUPLICATE KEY 异常，MySQL 也会用完 AUTO_INCRMENT 值。

<小时/>

我通常避免使用 REPLACE，因为我通常有很多外键，并且我不想要 DELETE 操作。 REPLACE 执行的 DELETE 操作是“真正的”DELETE；如果存在外键引用，则将遵守与外键关联的 DELETE 规则...如果 DELETE 规则为 RESTRICT 或 NO ACTION 并且引用行存在，或者如果 DELETE 规则为 CASCADE 或 SET NULL，则删除将失败，引用行将被删除或更新。我还相信任何 BEFORE/AFTER DELETE 触发器也将被触发。

<小时/>

除了这些选项之外，您还必须运行两个单独的语句。

当我需要避免在未定义 UNIQUE KEY 的情况下执行 INSERT 时，我通常会执行以下操作:

INSERT INTO mytable (id_jugador, id_partido, team1, team2) 
SELECT i.*
  FROM (SELECT ? AS id_jugador, ? AS id_partido, ? AS team1, ? AS team2) i
  LEFT
  JOIN mytable s
    ON s.id_jugador <=> i.id_jugador
   AND s.id_partido <=> i.id_partido
 WHERE s.id IS NOT NULL

语句执行后，测试受影响的行数。如果它为零，我们就知道该行未插入，因此我们可以继续进行更新。