如何在MySQL中动态实现行转列并存储至临时表？

需求说明 #

现有临时表tempDateTable存储了一系列日期行（如2022-10-10、2022-10-11等），需要将这些日期动态转为列，每个列对应一个日期，列值初始化为null，最终结果存入新的临时表。

实现步骤 #

由于日期是动态生成的，无法直接写死列名，必须使用动态SQL完成转换。以下是完整实现代码：

1. 生成日期列的动态定义

从tempDateTable提取所有日期，拼接成符合SQL语法的列定义字符串：

SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('`', start_period, '` DATE NULL')) INTO @cols
FROM tempDateTable;

这里用GROUP_CONCAT将每个日期转为日期字符串 DATE NULL格式，用反引号包裹列名避免语法冲突。

2. 动态创建结果临时表

用拼接好的列定义创建存储结果的临时表tempResultTable：

SET @create_table_sql = CONCAT(
    'DROP TEMPORARY TABLE IF EXISTS tempResultTable; ',
    'CREATE TEMPORARY TABLE tempResultTable (', @cols, ')'
);
PREPARE stmt FROM @create_table_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 插入初始null数据

如果需要插入多行全为null的数据（比如示例中的2行），动态生成插入语句：

-- 计算日期列的数量
SET @col_count = (SELECT COUNT(*) FROM tempDateTable);
-- 生成对应数量的NULL值字符串
SET @null_values = REPEAT('NULL, ', @col_count);
SET @null_values = LEFT(@null_values, LENGTH(@null_values) - 2); -- 移除末尾多余的逗号

-- 插入两行数据
SET @insert_sql = CONCAT(
    'INSERT INTO tempResultTable VALUES (', @null_values, '), (', @null_values, ')'
);
PREPARE stmt FROM @insert_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

完整整合代码 #

将以上步骤和你原有的日期生成代码整合，完整执行流程如下：

-- 1. 生成日期临时表tempDateTable
DROP TEMPORARY TABLE IF EXISTS tempDateTable;
CREATE TEMPORARY TABLE tempDateTable
WITH RECURSIVE cte AS 
(
  (
  SELECT 
    tempJsonTable.duration_day, 
    tempJsonTable.start_period, 
    tempJsonTable.end_period
  FROM tempJsonTable
  LIMIT 1
  )
  UNION ALL
  SELECT 
    cte.duration_day, 
    cte.start_period + INTERVAL 1 DAY , 
    cte.end_period
  FROM cte
  WHERE cte.start_period < cte.end_period
) 
SELECT
  cte.start_period
FROM cte;

-- 2. 动态生成列定义并创建结果表
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('`', start_period, '` DATE NULL')) INTO @cols
FROM tempDateTable;

SET @create_table_sql = CONCAT(
    'DROP TEMPORARY TABLE IF EXISTS tempResultTable; ',
    'CREATE TEMPORARY TABLE tempResultTable (', @cols, ')'
);
PREPARE stmt FROM @create_table_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 插入多行null数据
SET @col_count = (SELECT COUNT(*) FROM tempDateTable);
SET @null_values = REPEAT('NULL, ', @col_count);
SET @null_values = LEFT(@null_values, LENGTH(@null_values) - 2);

SET @insert_sql = CONCAT(
    'INSERT INTO tempResultTable VALUES (', @null_values, '), (', @null_values, ')'
);
PREPARE stmt FROM @insert_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

• GROUP_CONCAT有默认长度限制，如果日期数量过多，需先调整参数：SET SESSION group_concat_max_len = 1000000;
• 临时表仅在当前会话有效，关闭会话后自动销毁。

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