MySQL中JSON类型数据插入、查询与修改详解

结果或目标

掌握MySQL中JSON列的高效读写方法，能够根据业务需求灵活存储嵌套结构，并实现高性能的条件筛选与动态字段调整。

准备条件

1. MySQL数据库环境（建议5.7及以上版本，完整JSON支持需8.0+）。 2. 具备基本SQL编写能力，了解JSON数据的基本结构（对象与数组）。

真实操作顺序

第1步：插入JSON数据

目的：向包含JSON列的表中添加新记录或更新现有记录的JSON内容。

入口/动作：执行 INSERT 或 UPDATE 语句，采用以下两种方式之一：

1. 普通字符串形式：确保传入的字符串严格符合JSON格式规范（键名必须使用双引号包裹），作为普通文本存入数据库。
2. 内置函数辅助：使用 JSON_OBJECT() 创建JSON对象，或使用 JSON_ARRAY() 创建JSON数组后，将生成的字符串插入数据库。

第2步：查询与提取JSON字段

目的：从存储的JSON文档中精准定位并获取特定子项的值。

参数说明：访问路径通常遵循类似XPath的语法，例如 $.key 用于JSON对象节点，$[index] 用于JSON数组元素。$ 代表根节点。若Key名称中包含空格等特殊字符，必须使用双引号将其包裹，如 $."""".

1. 基础提取：
   • 使用箭头运算符 ->：SELECT column->'path' FROM table;
   • 使用标准函数 JSON_EXTRACT(column, 'path')。
2. 去除外层引号：
   上述方式返回的结果通常仍带有JSON格式的字符串双引号。若需获取纯净值（如数字或去除转义符），推荐：
   - 快捷语法：column->>'path'
   - 函数等价写法：JSON_UNQUOTE(JSON_EXTRACT(column, 'path'))。
3. 多级与批量查询：
   • 级联查询：使用点号连接，如 column->'$.key.childKey'，或通过递归调用 JSON_EXTRACT。
   • 数组全部匹配：若需提取JSON数组内所有同名属性的值，可使用通配符 column->'$[*].key'，这将返回一个包含多个结果的JSON数组。

第3步：基于JSON的条件搜索

目的：在 WHERE 子句中筛选包含特定JSON数据的行。

1. 精确查询单值：适用于查询对象中的某个字段的准确匹配，例如：WHERE column->'$.key' = 'value'。
2. 数组内精确查找：判断数组是否完全包含指定的JSON对象片段，示例：WHERE JSON_CONTAINS(column, JSON_OBJECT('key', 'value'))。
3. 模糊搜索：
   • 针对数组某列内容：WHERE column->'$[*].key' LIKE '%search_term%'。
   • 深层模糊查找：WHERE column->'$**.key' LIKE '%search_term%'（部分版本支持）。

第4步：JSON字段的局部更新与维护

目的：在不覆盖整个JSON文档的前提下，实现对内部特定字段的增加、修改或删除。

动作分类：

• 新增或覆盖：使用 JSON_INSERT(column, path, val)。仅当该路径不存在时才会执行插入，若已存在则不改变原有值。
• 强制更新：使用 JSON_SET(column, path, val)。无论路径是否存在都会写入新值；若路径不存在且父级为对象则自动创建。
• 仅替换：使用 JSON_REPLACE(column, path, val)。只有路径存在且被匹配时才会进行更新，新增操作会被忽略。
• 删除字段：使用 JSON_REMOVE(column, path)。将指定路径的键值对彻底移除。

完成判断

所有针对JSON字段的插入、查询、过滤及更新操作均按预期返回数据，无报错信息；提取出的数值精度保持正确，且无需二次处理多余的双引号字符。

简要总结

MySQL提供了强大的JSON原生处理能力，结合 ->> 等简便符号与一系列内建扩展函数，可以极大地简化后端应用对半结构化数据的持久化与检索逻辑。

以上就是MySQL中JSON的使用方式的详细内容，更多关于MySQL JSON字段使用的资料请关注本站其它相关文章！