如何用一条SQL语句更新插入两个数据库表？

Question

下面描述两个数据库表。星号突出显示主键。

+----------------+    +----------------+
| posts          |    | url_references |
+----------------+    +----------------+
| id*            |    | url*           |
| post_content   |    | post_id        |
+----------------+    +----------------+

我想根据表中是否存在相应条目来插入或更新帖子 url_references。达到此目的的最佳SQL 命令组合是什么？我想避免在PHP中处理有关插入或更新的决定。
以下场景描述了使用 PHP 命令的替代分步行为。

SELECT * FROM url_references WHERE url = $url;

场景 1：插入新条目。

// mysql_num_rows() returns 0
INSERT INTO post (post_content) VALUES ($postContent);
$postId = mysql_insert_id();
INSERT INTO url_references (url, post_id) VALUES ($url, $postId);

场景 2：更新现有条目。

// mysql_num_rows() returns 1
$row = mysql_fetch_array($rows);
$postId = $row['post_id'];
UPDATE posts SET post_content = $postContent WHERE id = post_id;

---

编辑1：请注意，我无法直接检查帖子中的id！我想根据 url 作为主键来管理（插入/更新）帖子。

Answer 1

如果您想以经典方式执行上述操作，请执行以下操作。

首先，我们可能同意 url 是一个自然主键。无论如何，您需要在该列中建立一个索引来加快查找速度：

CREATE UNIQUE INDEX url_references_idx ON url_references(url);

之后，如果您执行：

INSERT INTO url_references (url) VALUES ($url);

您最终会遇到两种情况：

• INSERT 成功。这意味着您的 $url 是新的，您可以继续：

INSERT INTO posts (id, post_content) values (NULL, $postContent);
SELECT LAST_INSERT_ID(); // This will return $post_id
UPDATE url_references SET post_id = $post_id WHERE url = $url;
COMMIT;

在这种情况下，url_references 中新插入的行上的锁保证另一个线程将进入第二个场景（如果第一个场景）事务已成功提交（如果失败，则为第一种情况）。

• INSERT 失败。这意味着您的 $url 已经已知，您可以继续：

SELECT post_id FROM url_references WHERE url = $url;
UPDATE posts SET post_content = $postContent WHERE id = $post_id;
COMMIT;

注意：第一个 INSERT 语句保证 url_references 表上的赛车条件正确如果您已启用正确的事务隔离级别和 autocommit=off，则已处理。

注意：在这种情况下使用 SELECT ... FOR UPDATE 不起作用，因为它只会锁定现有行（并且我们需要锁定即将插入的不存在行）。抱歉，如果我让您感到困惑，请忽略我在您的问题下的评论。

Answer 2

您可以使用 REPLACE INTO 来避免在 posts 表上选择 INSERT/UPDATE，然后根据受 影响的行数进行选择REPLACE 确定是否需要INSERT到url_references中。像这样的事情：

$sql = "REPLACE INTO posts (post_id, post_content) VALUES ($postId, $postContent)";
$result = mysql_query($sql);
$numRows = mysql_num_rows($result);

if ($numRows == 1) {
    // was INSERT not DELETE/INSERT (UPDATE) - need to insert into url_references

    $postId = mysql_insert_id();
    $sql = "INSERT INTO url_references (url, post_id) VALUES ($url, $postId)";

    ... do SQL INSERT etc
}

来自 MySQL REPLACE 文档：

REPLACE 语句返回一个计数来指示行数
做作的。这是删除和插入的行的总和。如果
单行 REPLACE 的计数为 1，插入了一行但没有行
被删除了。如果计数大于 1，则显示一个或多个旧行
在插入新行之前被删除。

请注意，我尚未测试上述伪代码，因此您可能需要对其进行调整。

请参阅： http://dev.mysql.com/doc/refman/5.0 /en/replace.html 供参考。