如何通过MySQL将货币时序查询结果转换为指定JSON格式？

问题描述 #

我有一张百万级数据量的currency表，结构包含时间戳字段datetime以及上百种货币字段（如Rupee、Yen等）。执行基础查询：

SELECT datetime, Rupee, Yen 
FROM currency 
WHERE datetime BETWEEN value1 AND value2

得到的JSON结果是按时间戳聚合的单条记录格式，但需要转换为按货币分组，每条货币对应一组时间序列数据的目标格式：

原查询JSON结果 #

[
  {
    "datetime": "2019-02-16T10:40:00.000Z",
    "Rupee": 10,
    "Yen": 60
  },
  {
    "datetime": "2019-02-16T10:50:00.000Z",
    "Rupee": 30,
    "Yen": 70
  },
  {
    "datetime": "2019-02-16T10:55:00.000Z",
    "Rupee": 40,
    "Yen": 80
  },
  {
    "datetime": "2019-02-16T10:58:00.000Z",
    "Rupee": 50,
    "Yen": 90
  }
]

目标JSON格式 #

[
  {
     "currency": "Rupee",
      "timeseriesdata": [
        ["2019-02-16T10:40:00.000Z",10],
        ["2019-02-16T10:50:00.000Z", 30],
        ["2019-02-16T10:55:00.000Z", 40],
        ["2019-02-16T10:58:00.000Z", 50]
      ]
  },
  {
    "currency": "Yen",
      "timeseriesdata": [
        ["2019-02-16T10:40:00.000Z",60],
        ["2019-02-16T10:50:00.000Z", 70],
        ["2019-02-16T10:55:00.000Z", 80],
        ["2019-02-16T10:58:00.000Z", 90]
      ]
  }
]

需求：不通过Node.js/JavaScript处理，直接用MySQL实现转换，适配百万级数据量和上百种货币的场景。

解决方案 #

1. 固定货币列场景（少量货币） #

如果货币列固定且数量不多，可直接用UNION ALL结合JSON函数构造结果：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'currency', currency_name,
        'timeseriesdata', timeseries_data
    )
) AS result
FROM (
    -- 处理Rupee
    SELECT 
        'Rupee' AS currency_name,
        JSON_ARRAYAGG(JSON_ARRAY(datetime, Rupee)) AS timeseries_data
    FROM currency
    WHERE datetime BETWEEN value1 AND value2
    UNION ALL
    -- 处理Yen
    SELECT 
        'Yen' AS currency_name,
        JSON_ARRAYAGG(JSON_ARRAY(datetime, Yen)) AS timeseries_data
    FROM currency
    WHERE datetime BETWEEN value1 AND value2
) AS currency_series;

该方法简单直接，但不适用于上百种货币的场景。

2. 动态货币列场景（上百种货币） #

通过动态SQL+存储过程自动遍历所有货币列，生成目标JSON：

步骤1：创建存储过程

DELIMITER //

CREATE PROCEDURE GetCurrencyTimeseries(IN start_datetime DATETIME, IN end_datetime DATETIME)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(255);
    -- 遍历currency表中除datetime外的所有列（即货币列）
    DECLARE cur CURSOR FOR 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = DATABASE() 
          AND table_name = 'currency' 
          AND column_name != 'datetime';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET @json_result = '[]';

    -- 创建临时表存储时间范围内的数据，避免重复扫描原表
    CREATE TEMPORARY TABLE temp_currency 
    SELECT datetime, * FROM currency WHERE datetime BETWEEN start_datetime AND end_datetime;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 动态构造当前货币的时间序列数组
        SET @sql = CONCAT(
            'SELECT JSON_ARRAYAGG(JSON_ARRAY(datetime, ', col_name, ')) INTO @series_data FROM temp_currency;'
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 将当前货币对象追加到结果JSON中
        SET @json_result = JSON_ARRAY_APPEND(
            @json_result,
            '$',
            JSON_OBJECT(
                'currency', col_name,
                'timeseriesdata', @series_data
            )
        );
    END LOOP;
    CLOSE cur;

    -- 输出最终结果并清理临时表
    SELECT @json_result AS result;
    DROP TEMPORARY TABLE IF EXISTS temp_currency;
END //

DELIMITER ;

步骤2：调用存储过程

CALL GetCurrencyTimeseries('2019-02-16 10:40:00', '2019-02-16 10:58:00');

3. 性能优化建议（针对百万级数据） #

• 索引优化：给datetime字段创建索引，减少全表扫描：

  CREATE INDEX idx_currency_datetime ON currency(datetime);
  

• 版本要求：使用MySQL 8.0+，该版本对JSON函数的性能有显著提升。
• 内存控制：若数据量极大，可在存储过程中加入分批聚合逻辑，避免单次聚合内存溢出。

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