MySQL技术需求：新增MMV列计算前后首个更大行ID的最小值

嘿，我先把你的需求理清楚，避免理解偏差：
对表中每一行的ID，我们需要找到两个参考值：

• 第一个值：在比当前ID小的所有行里取最大的ID；如果当前行是表中ID最小的记录，就用表的最小ID
• 第二个值：在比当前ID大的所有行里取最小的ID；如果当前行是表中ID最大的记录，就用表的最大ID
  最后MMV列的值就是这两个参考值的最小值

如果这个理解和你的Excel示例逻辑一致，那下面就是具体的实现方法：

推荐方案：MySQL 8.0+用窗口函数（高效简洁） #

MySQL 8.0及以上支持的LAG()和LEAD()窗口函数可以轻松获取前后行的ID，再配合聚合函数处理边界情况：

-- 先运行这个查询验证结果，和你的Excel示例比对没问题再执行后续操作
SELECT
    id,
    -- 处理第一个参考值：小于当前ID的最大ID，没有的话用表最小ID
    COALESCE(LAG(id) OVER (ORDER BY id), (SELECT MIN(id) FROM 你的表名)) AS 前参考值,
    -- 处理第二个参考值：大于当前ID的最小ID，没有的话用表最大ID
    COALESCE(LEAD(id) OVER (ORDER BY id), (SELECT MAX(id) FROM 你的表名)) AS 后参考值,
    -- 计算MMV：取两个参考值的最小值
    LEAST(
        COALESCE(LAG(id) OVER (ORDER BY id), (SELECT MIN(id) FROM 你的表名)),
        COALESCE(LEAD(id) OVER (ORDER BY id), (SELECT MAX(id) FROM 你的表名))
    ) AS MMV
FROM 你的表名
ORDER BY id;

要是你需要永久给表加上MMV列，可以分两步：

-- 第一步：新增MMV列（假设ID是INT类型，MMV也设为INT，根据实际情况调整）
ALTER TABLE 你的表名 ADD COLUMN MMV INT;

-- 第二步：计算并填充MMV的值
WITH 临时表 AS (
    SELECT
        id,
        COALESCE(LAG(id) OVER (ORDER BY id), (SELECT MIN(id) FROM 你的表名)) AS 前参考值,
        COALESCE(LEAD(id) OVER (ORDER BY id), (SELECT MAX(id) FROM 你的表名)) AS 后参考值
    FROM 你的表名
)
UPDATE 你的表名 t
JOIN 临时表 r ON t.id = r.id
SET t.MMV = LEAST(r.前参考值, r.后参考值);

兼容方案：MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本低于8.0，没法用窗口函数，那就用关联子查询来实现：

-- 先查询验证结果
SELECT
    t.id,
    -- 小于当前ID的最大ID，无则用表最小ID
    COALESCE(
        (SELECT MAX(id) FROM 你的表名 WHERE id < t.id),
        (SELECT MIN(id) FROM 你的表名)
    ) AS 前参考值,
    -- 大于当前ID的最小ID，无则用表最大ID
    COALESCE(
        (SELECT MIN(id) FROM 你的表名 WHERE id > t.id),
        (SELECT MAX(id) FROM 你的表名)
    ) AS 后参考值,
    -- 计算MMV
    LEAST(
        COALESCE((SELECT MAX(id) FROM 你的表名 WHERE id < t.id), (SELECT MIN(id) FROM 你的表名)),
        COALESCE((SELECT MIN(id) FROM 你的表名 WHERE id > t.id), (SELECT MAX(id) FROM 你的表名))
    ) AS MMV
FROM 你的表名 t
ORDER BY t.id;

新增列并填充的版本：

-- 第一步：添加MMV列
ALTER TABLE 你的表名 ADD COLUMN MMV INT;

-- 第二步：填充MMV的值
UPDATE 你的表名 t
SET MMV = LEAST(
    COALESCE((SELECT MAX(id) FROM 你的表名 WHERE id < t.id), (SELECT MIN(id) FROM 你的表名)),
    COALESCE((SELECT MIN(id) FROM 你的表名 WHERE id > t.id), (SELECT MAX(id) FROM 你的表名))
);

注意事项 #

• 记得把代码里的你的表名替换成你实际的表名称
• 假设你的ID是唯一且用于排序的主键，如果ID有重复或者需要按其他字段排序，得适当调整逻辑
• 窗口函数方案的性能比子查询好很多，数据量越大越明显，你的50条记录两种方案都没问题，但建议优先用窗口函数

你可以先跑查询语句看看结果是不是和你的Excel示例一致，确认没问题再执行新增列和更新的操作~

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