如何将MySQL中BLOB格式的JSON数据提取为列和对应值？

当然可以实现！针对你描述的场景，我们可以借助MySQL的原生JSON处理函数，把BLOB类型字段里的JSON内容解析出来，将指定的三个键值对转换成单独的列。以下是具体的实现步骤和SQL示例：

核心思路 #

1. 先将BLOB类型的request字段转换为MySQL可识别的JSON类型——因为BLOB是二进制数据，必须转成JSON格式才能进行后续的解析操作。
2. 利用JSON路径运算符，精准定位computes对象下的目标键值对。注意你的JSON结构里，每个目标值都嵌套在键为=的子对象中，所以路径要精确指向这个子键。

基础SQL查询 #

SELECT
  ref,
  -- 提取P_PRERefId_str对应的值
  CAST(request AS JSON)->>'$.computes.P_PRERefId_str."="' AS P_PRERefId_str,
  -- 提取P_PolicyNumber_str对应的值
  CAST(request AS JSON)->>'$.computes.P_PolicyNumber_str."="' AS P_PolicyNumber_str,
  -- 提取P_YearlyPolicyFeeAmt_rea对应的值
  CAST(request AS JSON)->>'$.computes.P_YearlyPolicyFeeAmt_rea."="' AS P_YearlyPolicyFeeAmt_rea
FROM
  your_table_name; -- 替换成你的实际表名

优化：转换数值类型 #

如果P_YearlyPolicyFeeAmt_rea需要作为数值类型（比如用于后续计算），可以额外加一层类型转换：

SELECT
  ref,
  CAST(request AS JSON)->>'$.computes.P_PRERefId_str."="' AS P_PRERefId_str,
  CAST(request AS JSON)->>'$.computes.P_PolicyNumber_str."="' AS P_PolicyNumber_str,
  -- 将字符串金额转换为DECIMAL类型，适配数值运算需求
  CAST(CAST(request AS JSON)->>'$.computes.P_YearlyPolicyFeeAmt_rea."="' AS DECIMAL(10,2)) AS P_YearlyPolicyFeeAmt_rea
FROM
  your_table_name;

关键函数说明 #

• CAST(request AS JSON)：把BLOB二进制数据转换为JSON类型，这是所有JSON解析操作的前提。
• ->>：MySQL的JSON路径简写运算符，等价于JSON_UNQUOTE(JSON_EXTRACT(...))，会自动去除JSON字符串的引号，直接返回纯文本值。
• 路径$.computes.P_PRERefId_str."="：$代表JSON根对象，computes是一级子对象，P_PRERefId_str是二级子对象，"="是最终存储值的键（因为键名是特殊字符，所以需要用双引号包裹）。

注意：该方案要求你的MySQL版本为5.7及以上，因为MySQL从5.7版本开始引入了原生JSON支持。

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