使用MySQL实现基于关联的动态行转列（INVOICES/COLUMNS场景）

现有业务场景 #

当前涉及两张核心业务表：

• INVOICES 发票主表：存储每张发票的核心信息，包括发票唯一标识、发票总金额、金额对应的展示位置列ID（与COLUMNS表主键关联）
• COLUMNS 自定义列配置表：存储所有可用于前端展示的自定义列元数据，包括列ID、列展示名称、列排序规则
  最终需要实现的输出效果为：
• 以单张发票为行维度，以所有已配置的自定义列为列维度
• 每张发票的总金额仅填充在其对应列ID匹配的列下，其余列位置为空
• 实现动态查询能力：当COLUMNS表新增自定义列时，无需修改原有SQL语句，即可自动将新列纳入输出结果

实现方案 #

由于MySQL原生不支持PIVOT行转列语法、也无法在普通查询中动态生成列名，因此采用「动态拼接查询片段 + 预处理执行」的标准实现，完全满足无需改SQL自动适配新列的需求，具体代码如下：

-- 步骤1：从COLUMNS表动态提取所有自定义列，生成对应的取值判断逻辑
SET @select_part = NULL;
SELECT
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
      -- 当发票关联的列ID和当前自定义列ID匹配时，取发票总金额
      'MAX(CASE WHEN i.target_column_id = c.id THEN i.total_amount END) AS `',
      c.column_name, -- 直接使用COLUMNS表中配置的列名作为输出列名
      '`'
    )
    ORDER BY c.sort_weight -- 按配置的排序值调整列的输出顺序
    SEPARATOR ','
  )
INTO @select_part
FROM COLUMNS c;

-- 步骤2：拼接完整的查询SQL
SET @full_sql = CONCAT(
  'SELECT i.invoice_id, ', -- 发票本身的固定字段可按需补充
  @select_part, '
  FROM INVOICES i
  LEFT JOIN COLUMNS c ON i.target_column_id = c.id
  GROUP BY i.invoice_id'
);

-- 步骤3：执行预处理语句得到结果
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案特性 #

• 零硬编码适配：所有自定义列的逻辑都从COLUMNS表实时生成，新增列记录时原有SQL完全无需修改，查询会自动带上新列
• 列顺序可控：修改COLUMNS表中对应列的排序权重值，即可调整最终结果的列排列顺序
• 结果匹配度高：每个发票行仅在对应匹配的列位置填充金额，其余列返回NULL，和预期输出格式完全一致

注意：如果COLUMNS表的列数量过多，需要提前调整MySQL的group_concat_max_len参数值，避免拼接的SQL片段被截断导致执行报错

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