如何在MySQL SELECT中动态将JSON列的所有键设为列名

这是个挺实用的动态解析JSON列需求，咱们分两种场景来解决，适配不同的使用情况：

场景1：已知所有Class对应的JSON结构（当前仅Class3/4） #

如果已经明确只会有Class3（name/email）和Class4（first_name/last_name）这两种结构，直接用CASE结合JSON解析语法就能实现，写法简洁直观：

SELECT
  class,
  -- 为Class3解析对应字段
  CASE WHEN class = 3 THEN my_tags->>'$.name' END AS name,
  CASE WHEN class = 3 THEN my_tags->>'$.email' END AS email,
  -- 为Class4解析对应字段
  CASE WHEN class = 4 THEN my_tags->>'$.first_name' END AS first_name,
  CASE WHEN class = 4 THEN my_tags->>'$.last_name' END AS last_name
FROM your_table_name;

注：->>是MySQL 8.0+支持的简化语法，等价于JSON_UNQUOTE(JSON_EXTRACT(...))，能直接返回不带引号的字符串结果。

查询后，对应Class的字段会返回有效值，非对应Class的字段则为NULL，完全符合你要的“Class3返回name/email，Class4返回first_name/last_name”的需求。

场景2：需要完全动态适配未知的Class和JSON结构 #

如果未来可能新增其他Class，且每个Class的JSON键不确定，就得用动态SQL来实现（因为静态SQL无法在运行时动态调整返回的列数和列名）。这里可以写一个存储过程自动生成并执行查询：

DELIMITER //

CREATE PROCEDURE dynamic_extract_json_columns()
BEGIN
  DECLARE sql_content TEXT DEFAULT '';
  DECLARE is_done INT DEFAULT 0;
  DECLARE current_class INT;
  DECLARE class_keys TEXT;
  
  -- 游标遍历表中所有不同的Class值
  DECLARE class_cursor CURSOR FOR SELECT DISTINCT class FROM your_table_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = 1;

  OPEN class_cursor;
  class_loop: LOOP
    FETCH class_cursor INTO current_class;
    IF is_done THEN
      LEAVE class_loop;
    END IF;

    -- 提取当前Class对应的所有JSON键（假设同Class的JSON结构一致，取第一条数据的键）
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'CASE WHEN class = ', current_class, ' THEN my_tags->>\'$.', j.key, '\' END AS `', j.key, '`'
    )) INTO class_keys
    FROM your_table_name t,
         JSON_TABLE(JSON_KEYS(t.my_tags), '$[*]' COLUMNS(key TEXT PATH '$')) j
    WHERE t.class = current_class
    LIMIT 1;

    -- 拼接SQL语句
    IF sql_content = '' THEN
      SET sql_content = CONCAT('SELECT class, ', class_keys);
    ELSE
      SET sql_content = CONCAT(sql_content, ', ', class_keys);
    END IF;
  END LOOP;

  -- 补全FROM子句并执行动态SQL
  SET sql_content = CONCAT(sql_content, ' FROM your_table_name;');
  PREPARE dynamic_stmt FROM sql_content;
  EXECUTE dynamic_stmt;
  DEALLOCATE PREPARE dynamic_stmt;
END //

DELIMITER ;

调用这个存储过程就能自动适配所有Class的JSON结构：

CALL dynamic_extract_json_columns();

注意事项：

• 这个方案依赖JSON_TABLE函数，需要MySQL 8.0.4及以上版本；
• 假设同一Class的所有my_tags结构完全一致，如果有不一致的情况，会取该Class第一条数据的JSON键作为解析依据；
• 如果Class值是外部输入，要注意做好SQL注入防护（本示例中Class来自表内已有数据，风险较低）。

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