MySQL如何更新下一条记录并实现EAV结构表行转列查询

1. 更新表中的下一条关联数据 #

MySQL没有原生的“下一条记录”判定逻辑，必须先明确两个规则：

• 分组维度：按哪个关联字段归类（比如同一个用户、同一个订单下的记录为一组）
• 排序规则：组内按什么字段排序判定先后（比如自增ID递增、创建时间递增）

MySQL 8.0及以上版本（支持窗口函数）实现 #

用LEAD()窗口函数直接定位分组内每条记录的下一条记录ID，再关联更新：

WITH next_record_map AS (
    SELECT
        主键字段,
        -- 取分组内排序后当前行的下一行主键
        LEAD(主键字段) OVER (PARTITION BY 关联分组字段 ORDER BY 排序字段 ASC) AS next_primary_id
    FROM 你的业务表
    -- 可加WHERE条件限定需要处理的数据范围
)
UPDATE 你的业务表 t
JOIN next_record_map m ON t.主键字段 = m.next_primary_id
SET t.待更新字段 = 更新目标值;

MySQL 5.x版本（不支持窗口函数）实现 #

用用户变量遍历记录，手动记录下一条ID后关联更新：

UPDATE 你的业务表 t
JOIN (
    SELECT
        主键字段,
        @pre_id AS next_primary_id,
        @pre_id := 主键字段
    FROM 你的业务表, (SELECT @pre_id := NULL) init_var
    -- 排序规则必须和你定义的先后逻辑一致，先按分组字段排序，再按排序字段排序
    ORDER BY 关联分组字段, 排序字段 ASC
) m ON t.主键字段 = m.next_primary_id
SET t.待更新字段 = 更新目标值;

注意：不要依赖数据库默认的行存储顺序判定先后，必须显式指定ORDER BY规则，否则会出现定位错误。

2. EAV结构表行转列查询 #

你之前写的语句未生效的核心原因：仅对单行做了条件判断，没有把同一个人员的Name、Gender、Salary三个属性聚合到同一行，因此查询结果中每行只有一个字段有值，其余字段为NULL。
你的测试表没有人员唯一标识字段，且每个人员固定按Name→Gender→Salary的顺序连续存储3条记录，因此可以先给记录打人员分组标记，再做聚合行转列。

MySQL 8.0及以上版本实现 #

WITH person_mark AS (
    SELECT
        `name`,
        `value`,
        -- 每3条记录为一个人员，生成统一的人员分组ID
        (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1) DIV 3 AS person_id
    FROM mpr
)
SELECT
    MAX(CASE WHEN `name` = 'Name' THEN `value` END) AS Name,
    MAX(CASE WHEN `name` = 'Gender' THEN `value` END) AS Gender,
    MAX(CASE WHEN `name` = 'Salary' THEN `value` END) AS Salary
FROM person_mark
GROUP BY person_id;

MySQL 5.x版本实现 #

SELECT
    MAX(CASE WHEN `name` = 'Name' THEN `value` END) AS Name,
    MAX(CASE WHEN `name` = 'Gender' THEN `value` END) AS Gender,
    MAX(CASE WHEN `name` = 'Salary' THEN `value` END) AS Salary
FROM (
    SELECT
        `name`,
        `value`,
        @row := @row + 1 AS row_num,
        (@row -1) DIV 3 AS person_id
    FROM mpr, (SELECT @row := 0) init_var
    -- 生产环境请替换为明确的排序字段（如自增ID、插入时间），不要依赖默认存储顺序
    ORDER BY (SELECT 1)
) tmp
GROUP BY person_id;

查询结果 #

执行后将得到你预期的结构化人员列表：

生产环境使用EAV模型时，强烈建议给表增加自增主键、实体唯一ID（如person_id）字段，不要依赖插入顺序做分组，一旦出现属性缺失、插入顺序错乱，查询结果会完全异常。

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