MySQL -插入到…中使用从同一个表中选择的值更新重复键时

Question

我有两个表，一个是posts(ID, edited_date, is_public, ... etc)，另一个是post_pagination(index, post_id)，用于存储帖子的分页数据。在post_pagination中，index是主键。

在分页过程中，我需要检查排序的结果和post_pagination表，并执行插入/更新，如下所示：

INSERT INTO post_pagination(`index`, post_id)
SELECT * /* full outer join new sorted result and pagination table */
FROM (
    WITH 
    cte1 AS (
        SELECT ROW_NUMBER() OVER(ORDER BY edited_date DESC, ID DESC) AS new_rank, ID
        FROM posts
        WHERE is_public = 1
    ),
    cte2 AS (
        SELECT `index`
        FROM post_pagination
    )
    SELECT `index` AS dummy_rank, ID
    FROM cte2
    LEFT JOIN cte1 on `index` = new_rank
    UNION ALL
    SELECT new_rank AS dummy_rank, ID
    FROM cte2
    RIGHT JOIN cte1 on `index` = new_rank
    WHERE `index` IS NULL
) AS a
ORDER BY dummy_rank
ON DUPLICATE KEY UPDATE post_id = ID

第一次运行此查询时，post_pagination为空，因此MySQL将所有数据插入到表中。

index  post_id
1      390
2      391
3      392
4      393
5      307

它工作得很好。

当我第二次运行时，我希望所有数据都不会改变，但它会将所有post_id字段更新为结果的最后一个值。

index  post_id
1      307
2      307
3      307
4      307
5      307

我做了一些测试，似乎通过使用从同一个表中选择的值将值插入到一个表中会导致这个问题，但我不知道为什么

现在，我只需将查询的最后一行修改为：

ON DUPLICATE KEY UPDATE post_id = ID + 0

有没有更好的方法来解决这个问题？

Answer 1

您可以使用临时表吗？

CREATE TEMPORARY TABLE `post_pagination_temp` (
 `index` INTEGER(1) AUTO_INCREMENT PRIMARY KEY,
 `post_id` INTEGER(1)
);

INSERT INTO `post_pagination_temp` (`post_id`)
SELECT `ID` FROM `posts` ORDER BY `edited_date` DESC, `ID` DESC;

INSERT INTO `post_pagination`
SELECT * FROM `post_pagination_temp`
ON DUPLICATE KEY UPDATE `index` = VALUES(`index`);

这假设您在post_pagination上有正确的索引，即惟一的是post_id上的主键。