这段内容主要讲的是：在 MySQL 5.7 及以上版本里，面对以 TEXT 类型保存的 JSON 日志字段（例如 gateway_log），怎样把 amount_received 这类嵌套键值准确提取出来；同时借助 JSON_EXTRACT 和 JSON_SET，直接在数据库层面对目标列（如 amount）完成批量更新，整个过程不需要再交给应用层额外解析。

本文介绍在 MySQL 5.7+ 中，如何从存储为 TEXT 类型的 JSON 格式日志字段（如 gateway_log）中，精准提取 amount_received 等嵌套键值。

同时，还可以通过 JSON_EXTRACT 和 JSON_SET 实现批量更新目标列（如 amount），无需应用层解析。

适用场景

在真实的支付网关回调日志场景里，结构化数据通常会先以 JSON 字符串的方式落到 MySQL 的 TEXT 或 LONGTEXT 字段中，比如示例里的 gateway_log。

这类字段虽然表面上存的是字符串，但只要内容本身符合 JSON 规范，就可以直接调用 MySQL 原生的 JSON 函数做解析和处理，效率通常也比较可观。

提取 amount_received 值：使用 JSON_EXTRACT

MySQL 不支持类似 MS SQL 的 XPath 风格 ExtractValue() 解析非标准 XML，但对合法 JSON 提供了强大支持。

先看示例数据结构：

["call_result", {
"payment_id": "5917457",
"amount_received": 396.460139,
...
}]

该 JSON 是一个数组，其中第二个元素，也就是索引 [1]，是包含业务字段的对象。

要提取 amount_received，需要使用路径表达式 '$[1].amount_received'：

SELECT 
id,
JSON_EXTRACT(gateway_log, '$[1].amount_received') AS extracted_amount
FROM payment
WHERE gateway_log IS NOT NULL 
AND JSON_VALID(gateway_log);

关键注意事项

• 必须确保 gateway_log 内容是合法 JSON（原文本中存在语法错误，如 null 后多逗号、ipn_callback_url: 后缺值等），否则 JSON_EXTRACT 返回 NULL。建议先用 JSON_VALID(gateway_log) 过滤。
• JSON_EXTRACT 返回带双引号的 JSON 字符串（如 "396.460139"），若需数值参与计算或写入 DECIMAL 类型字段，应配合 CAST(... AS DECIMAL(12,6)) 或 JSON_UNQUOTE()。

CAST(JSON_EXTRACT(gateway_log, '$[1].amount_received') AS DECIMAL(12,6))
-- 或
JSON_UNQUOTE(JSON_EXTRACT(gateway_log, '$[1].amount_received'))

批量更新 amount 字段：使用 UPDATE + JSON_EXTRACT

结合 UPDATE 语句，可以一次性将所有有效记录的 amount_received 写入 amount 列：

UPDATE payment 
SET amount = CAST(
JSON_EXTRACT(gateway_log, '$[1].amount_received') 
AS DECIMAL(12,6)
)
WHERE JSON_VALID(gateway_log) 
AND JSON_EXTRACT(gateway_log, '$[1].amount_received') IS NOT NULL;

此语句安全可靠：仅更新 JSON 合法且 amount_received 存在的行，避免 NULL 或类型转换异常。

进阶：动态修改 JSON 内容

若需反向操作，例如将新计算的金额写回 gateway_log 中的 amount_received 字段，可以使用 JSON_SET：

UPDATE payment 
SET gateway_log = JSON_SET(
gateway_log, 
'$[1].amount_received', 
CAST(amount AS CHAR)
)
WHERE JSON_VALID(gateway_log);

其他常用 JSON 函数

• JSON_REPLACE()：仅当路径存在时替换；
• JSON_INSERT()：仅当路径不存在时插入；
• JSON_REMOVE()：删除指定路径键值；
• JSON_CONTAINS() / JSON_SEARCH()：条件过滤。

总结

MySQL 5.7+ 的 JSON 函数是处理嵌入式日志数据的利器。

• 验证 JSON 合法性（JSON_VALID）
• 精确路径定位（$[1].key）
• 类型安全转换（CAST/JSON_UNQUOTE）

避免正则或应用层解析，既提升性能，又保障数据一致性。