MySQL更新各用户最早发布帖子状态的SQL问题排查

Hey Joe，我来帮你梳理下当前遇到的问题，以及给出正确的解决方案~

你的需求回顾 #

你想要给每个用户（id_user为343、345、356）发布的最早日期的帖子设置status=0，也就是每个用户仅更新其第一条发布的帖子。

你之前写法的错误分析 #

1. 第一个SQL的逻辑偏差

   UPDATE posts SET status = 0 WHERE 1 ORDER BY date ASC LIMIT 3
   

   这个语句只是从全局所有帖子中取日期最早的3条进行更新，刚好你的示例里有3个用户，结果看起来符合预期，但如果有用户发布了更多帖子，或者用户数量增加，这个写法就会错误地更新全局最早的N条，而不是每个用户各一条。

2. 第二个SQL的语法+逻辑错误

   UPDATE posts p
   WHERE 1
   LEFT JOIN post c ON p.id_user !=c.id_user
   ORDER BY date ASC LIMIT 3
   

   • 语法上：MySQL的UPDATE JOIN语法顺序应该是UPDATE 表1 JOIN 表2 ON 关联条件 SET 字段=值 ...，你把WHERE放在了JOIN前面，这是语法错误；
   • 逻辑上：p.id_user !=c.id_user这个关联条件完全无法帮你定位到每个用户的最早帖子，反而会产生大量无意义的关联数据，完全不符合需求。

正确的SQL写法 #

根据你的MySQL版本，有两种常用的解决方案：

方案1：MySQL 8.0及以上版本（支持窗口函数）

用ROW_NUMBER()窗口函数按用户分组，给每个用户的帖子按日期升序排名，排名为1的就是该用户最早的帖子：

WITH ranked_posts AS (
    SELECT 
        id_post,
        -- 按用户分组，日期升序排名，每个用户最早的帖子rn=1
        ROW_NUMBER() OVER (PARTITION BY id_user ORDER BY date ASC) AS rn
    FROM posts
)
UPDATE posts p
JOIN ranked_posts rp ON p.id_post = rp.id_post
SET p.status = 0
WHERE rp.rn = 1;

如果同一个用户有多个帖子在同一时间发布，这个写法只会更新其中一条（按MySQL默认的排序规则），如果需要更新所有同时间的最早帖子，可以把ROW_NUMBER()换成RANK()。

方案2：MySQL 5.x版本（不支持窗口函数）

通过子查询先找出每个用户的最早发布日期，再关联原表更新对应帖子：

UPDATE posts p
JOIN (
    -- 分组获取每个用户的最早发布日期
    SELECT id_user, MIN(date) AS earliest_date
    FROM posts
    GROUP BY id_user
) u ON p.id_user = u.id_user AND p.date = u.earliest_date
SET p.status = 0;

如果同一个用户有多个帖子在同一最早日期发布，这个写法会更新所有这些帖子。如果只想更新其中一条，可以修改子查询为获取每个用户最早日期对应的最小id_post：

UPDATE posts p
JOIN (
    SELECT id_user, MIN(id_post) AS target_post_id
    FROM posts
    WHERE (id_user, date) IN (
        SELECT id_user, MIN(date) FROM posts GROUP BY id_user
    )
    GROUP BY id_user
) u ON p.id_post = u.target_post_id
SET p.status = 0;

备注：内容来源于stack exchange，提问作者joe