如何基于UNION查询结果更新scores表指定记录？效率如何？

可以通过嵌套子查询精准定位需要更新的目标记录，实现需求中的优先级逻辑：

UPDATE scores
SET option = 'some'
WHERE id = (
    SELECT id FROM (
        -- 优先获取Rubio的最新option为none的记录
        SELECT id
        FROM scores
        WHERE name = 'Rubio' AND option = 'none'
        ORDER BY datetime_added DESC, id DESC
        LIMIT 1
        UNION ALL
        -- 若Rubio无符合条件记录，则获取all的最新option为none的记录
        SELECT id
        FROM scores
        WHERE name = 'all' AND option = 'none'
        ORDER BY datetime_added DESC, id DESC
        LIMIT 1
    ) AS candidate_ids
    LIMIT 1
);

语句细节说明 #

• 内层两个子查询分别按datetime_added倒序（最新时间优先）、id倒序（时间相同时取最新插入的记录）筛选目标id
• 外层通过LIMIT 1确保优先取Rubio的记录，只有当Rubio无符合条件记录时，才会取all的记录
• 两层子查询嵌套是为了规避MySQL中更新同表时无法直接引用该表子查询的限制

结合请求每秒最多执行一次的频率，这个实现的效率完全满足需求，具体分析：

• 查询开销极低：只要给表建立(name, option, datetime_added DESC, id DESC)的联合索引，两个子查询都能直接通过索引定位到目标记录，无需全表扫描，查询耗时接近瞬时
• 并发压力极小：每秒一次的请求属于极低频率，数据库完全能轻松处理，不会产生锁竞争或性能瓶颈
• 锁范围可控：每次仅更新单条记录，行级锁的范围极小，不会影响其他业务操作

优化建议 #

创建针对性的联合索引进一步提升性能：

CREATE INDEX idx_scores_target ON scores(name, option, datetime_added DESC, id DESC);

内容的提问来源于stack exchange，提问作者Ajama