如何基于条件动态映射MySQL表列名？——temp_data表device_id列按需重命名方案咨询

嘿，我来帮你搞定这个问题！首先得搞清楚你之前的UNION ALL方案为啥没生效——UNION ALL会强制使用第一个SELECT语句的列名，所以哪怕你在第二个分支里把device_id别名成instance_id，最终结果集的列名还是device_id，这就导致输出JSON的时候所有键都是device_id，不符合你的预期。

下面给你两种高效的实现方式，直接满足你的需求：

一、单次扫描表的JSON构造查询（推荐） #

既然你的目标是输出特定格式的JSON，完全不需要用UNION ALL扫两次表，直接用MySQL的JSON_OBJECT函数动态生成键名就行，只需要扫描一次表，性能更优：

1. 生成单条JSON记录 #

SELECT 
  JSON_OBJECT(
    -- 动态判断键名：event_layer=2时用instance_id，否则用device_id
    CASE WHEN event_layer = 2 THEN 'instance_id' ELSE 'device_id' END,
    device_id,
    -- 固定输出event_layer字段
    'event_layer',
    event_layer
  ) AS json_result
FROM temp_data;

2. 直接生成预期的JSON数组 #

如果想直接得到你给出的那种完整JSON数组，搭配JSON_ARRAYAGG函数即可：

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    CASE WHEN event_layer = 2 THEN 'instance_id' ELSE 'device_id' END,
    device_id,
    'event_layer',
    event_layer
  )
) AS final_json
FROM temp_data;

这个查询会直接返回你想要的数组格式，而且只遍历一次表，比UNION ALL高效得多。

二、用视图封装逻辑（适合重复查询场景） #

如果你需要多次执行这个查询，可以把逻辑封装成视图，方便复用：

方式1：存储键名、值和event_layer的视图 #

CREATE VIEW dynamic_device_view AS
SELECT 
  CASE WHEN event_layer = 2 THEN 'instance_id' ELSE 'device_id' END AS key_name,
  device_id AS value,
  event_layer
FROM temp_data;

查询视图时再生成JSON：

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(key_name, value, 'event_layer', event_layer)
) FROM dynamic_device_view;

方式2：直接生成JSON记录的视图 #

如果每次都要JSON格式，也可以直接把JSON构造逻辑放进视图：

CREATE VIEW dynamic_json_view AS
SELECT 
  JSON_OBJECT(
    CASE WHEN event_layer = 2 THEN 'instance_id' ELSE 'device_id' END,
    device_id,
    'event_layer',
    event_layer
  ) AS json_record
FROM temp_data;

查询时合并成数组：

SELECT JSON_ARRAYAGG(json_record) FROM dynamic_json_view;

为什么你的原方案不行？ #

再补充下原方案的问题：当你用UNION ALL时，MySQL会将两个结果集的列名统一为第一个SELECT的列名（也就是device_id），所以第二个分支里的instance_id别名会被忽略，最终所有记录的键都是device_id，这就和你的预期不符啦。

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