优化MySQL JSON列合并：标量置空与数组空串合并方案

针对你遇到的「结构相同仅标量值不同的JSON被视为不同值，导致distinct数据量过大」的问题，以下是分步解决的具体实现：

1. 替换JSON中所有标量值为空串 #

使用MySQL的REGEXP_REPLACE函数，精准匹配并替换键值对、数组中的各类标量值（字符串、数字、布尔值、null），仅保留JSON的结构和键名：

REGEXP_REPLACE(
  arbitraryjsonvalues,
  '(:\\s*)(?:"[^"]*"|\\d+|true|false|null)',
  '$1""',
  1,
  0
)

正则逻辑说明： #

• (:\\s*)：匹配键名后的冒号及可选空白字符，捕获该部分用于替换时保留结构
• (?:"[^"]*"|\\d+|true|false|null)：匹配所有标量类型：
  • "[^"]*"：双引号包裹的字符串值
  • \\d+：数字值
  • true|false：布尔值
  • null：null值
• $1""：将匹配到的标量值替换为空字符串，保留冒号和原空白

2. 合并数组中连续的空串元素 #

在完成标量值替换后，再次使用REGEXP_REPLACE合并数组内连续的空串元素，避免因连续空串导致的结构误判：

REGEXP_REPLACE(
  -- 第一步处理后的结果
  REGEXP_REPLACE(arbitraryjsonvalues, '(:\\s*)(?:"[^"]*"|\\d+|true|false|null)', '$1""', 1, 0),
  '(\\[|,)""(,"")+(?=\\]|,)',
  '$1""',
  1,
  0
)

正则逻辑说明： #

• (\\[|,)：匹配数组开头或元素分隔符逗号，捕获该部分
• ""(,"")+：匹配连续的空串元素
• (?=\\]|,)：确保匹配的是数组内的元素（后面是数组结尾或下一个元素分隔符）
• $1""：将连续空串替换为单个空串，保留数组结构

完整查询示例 #

将两步整合，获取去重后的JSON结构：

SELECT DISTINCT
  REGEXP_REPLACE(
    REGEXP_REPLACE(arbitraryjsonvalues, '(:\\s*)(?:"[^"]*"|\\d+|true|false|null)', '$1""', 1, 0),
    '(\\[|,)""(,"")+(?=\\]|,)', '$1""', 1, 0
  ) AS normalized_json_structure
FROM ITEMS;

你可以将这个归一化后的结果传入MERGE_ALL_JSON存储过程，由于结构相同的JSON已被合并为同一值，待处理的数据量会大幅减少，合并效率显著提升，最终得到的结构可直接用于前端树形搜索界面的渲染。

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