使用Union All与时间范围的MySQL查询返回NULL问题排查

可能的原因 #

• 时间范围扩大后，两个子查询的JSON聚合结果总长度超出了MySQL的max_allowed_packet限制，导致外层JSON_ARRAYAGG无法正常处理，最终返回NULL。
• 大数量级数据下，MySQL的JSON函数在合并结果时出现内存溢出或处理异常。

解决方案 #

1. 调整max_allowed_packet参数 #

先查看当前参数值：

SHOW VARIABLES LIKE 'max_allowed_packet';

如果当前值较小（比如默认4M/16M），临时调整为更大值（例如64M）：

SET GLOBAL max_allowed_packet = 67108864; -- 64M

若要永久生效，需在my.cnf/my.ini配置文件中修改：

max_allowed_packet = 64M

修改后重启MySQL服务即可。

2. 优化查询结构，减少嵌套聚合 #

原查询嵌套了两层JSON_ARRAYAGG，可以改为直接用JSON_ARRAY合并两个子查询结果，避免外层聚合的额外处理压力：

SELECT JSON_ARRAY(
    (SELECT json_object(
          "points", json_arrayagg(json_array(
                        UNIX_TIMESTAMP(timestamping)*1000, 
                        CONVERT(ROUND(P1,4), DECIMAL(9,4))
         ))                                   
     ) FROM mytable WHERE timestamping between '2021-10-01T00:00:00.000Z' AND '2022-05-10T23:59:59.999Z'),
    (SELECT json_object(
          "datapoints", json_arrayagg(json_array(
                        UNIX_TIMESTAMP(timestamping)*1000, 
                        CONVERT(ROUND(P2,4), DECIMAL(9,4))
           ))                                   
     ) FROM mytable WHERE timestamping between '2021-10-01T00:00:00.000Z' AND '2022-05-10T23:59:59.999Z')
) AS RESULT;

3. 分步查询，借助临时表 #

如果上述方法无效，可以先将两个子查询结果存入临时表，再进行聚合：

-- 创建临时表
CREATE TEMPORARY TABLE temp_json_results (obj JSON);

-- 插入第一个子查询结果
INSERT INTO temp_json_results
SELECT json_object(
      "points", json_arrayagg(json_array(
                    UNIX_TIMESTAMP(timestamping)*1000, 
                    CONVERT(ROUND(P1,4), DECIMAL(9,4))
     ))                                   
) FROM mytable WHERE timestamping between '2021-10-01T00:00:00.000Z' AND '2022-05-10T23:59:59.999Z';

-- 插入第二个子查询结果
INSERT INTO temp_json_results
SELECT json_object(
      "datapoints", json_arrayagg(json_array(
                    UNIX_TIMESTAMP(timestamping)*1000, 
                    CONVERT(ROUND(P2,4), DECIMAL(9,4))
       ))                                   
) FROM mytable WHERE timestamping between '2021-10-01T00:00:00.000Z' AND '2022-05-10T23:59:59.999Z';

-- 聚合结果
SELECT json_arrayagg(obj) AS RESULT FROM temp_json_results;

-- 临时表会在会话结束后自动删除，也可手动清理
DROP TEMPORARY TABLE temp_json_results;

4. 确保聚合结果不为空 #

给JSON_ARRAYAGG添加COALESCE，避免无数据时返回NULL，影响外层聚合：

SELECT json_object(
      "points", COALESCE(json_arrayagg(json_array(
                    UNIX_TIMESTAMP(timestamping)*1000, 
                    CONVERT(ROUND(P1,4), DECIMAL(9,4))
     )), JSON_ARRAY())                                   
) AS obj
FROM mytable 
WHERE timestamping between '2021-10-01T00:00:00.000Z' AND '2022-05-10T23:59:59.999Z'

第二个子查询也做同样修改即可。

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