本文介绍在 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 列：
使用 JSON Schema 验证 JSON 数据，从示例 JSON 生成 schema，并将其转换为 TypeScript 接口、Python 数据类或 Markdown 文档。
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）。避免正则或应用层解析，既提升性能，又保障数据一致性。
