深入解析MySQL中JSON数据字段的应用与实践

本文详细介绍了如何在MySQL数据库中高效使用JSON数据字段，包括创建JSON列、数据操作、查询优化及索引策略，同时分析了适用场景与注意事项，帮助开发者合理利用JSON特性提升开发效率。

如何在MySQL数据库中使用JSON数据字段

关键要点

  • MySQL 5.7+ InnoDB数据库和PostgreSQL 9.2+直接支持JSON文档类型，但由于直接索引限制需谨慎使用
  • JSON最适合稀疏数据、自定义属性、层次结构和需要灵活性的场景，不应替代经常查询或索引的规范化列
  • MySQL提供多种函数操作JSON对象：JSON_ARRAY()、JSON_OBJECT()、JSON_QUOTE()、JSON_TYPE()、JSON_VALID()、JSON_CONTAINS()、JSON_SEARCH()，以及JSON_SET()和JSON_MERGE_PATCH()等更新函数
  • MySQL 9.1支持基于JSON数据生成列的函数索引，实现特定JSON元素的高效查询
  • 尽管支持JSON，MySQL仍是关系型数据库，过度使用JSON可能抵消SQL的优势

可以在MySQL JSON列中存储JSON文档，但…

…并不意味着你应该这样做。

规范化是优化数据库结构的技术。第一范式（1NF）规则要求每列应包含单个值——存储多值JSON文档明显违反了此规则。

如果有明确的关系数据需求，应使用适当的单值字段。JSON应作为最后手段谨慎使用。JSON值字段无法直接索引，因此避免在经常更新或搜索的列上使用。

基于JSON生成列的函数索引允许索引JSON对象的部分内容，提高查询性能。

尽管如此，JSON在稀疏数据或自定义属性方面仍有很好的应用场景。

创建包含JSON数据类型列的表

考虑一个书店场景。所有书籍都有ID、ISBN、标题、出版商、页数等明确的关系数据。

如果要为每本书添加任意数量的类别标签，在SQL中可以通过以下方式实现：

  • 存储每个标签名称和唯一ID的标签表
  • 多对多记录映射书籍ID到标签ID的标签映射表

这种方法可行，但对于小功能来说繁琐且工作量较大。因此，可以在MySQL数据库的书籍表中为标签定义JSON字段：

1
2
3
4
5
6

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

MySQL JSON列不能有默认值，不能用作主键或外键，也不能有直接索引。

但在MySQL 9.1中，可以基于JSON数据创建生成列的函数索引，从而索引JSON文档中的特定元素。这些生成列可以是虚拟的或存储的，并作为二级索引。

1
2
3

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

添加JSON数据

完整的JSON文档可以在INSERT或UPDATE语句中传递，便于将JSON移动到MySQL进行存储和操作。

例如，书籍标签可以作为数组传递（在字符串内）：

1
2
3
4
5

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

还可以使用以下函数创建JSON：

  • JSON_ARRAY()：创建数组。例如：SELECT JSON_ARRAY(1, 2, 'abc'); 返回 [1, 2, "abc"]
  • JSON_OBJECT()：创建对象。例如：SELECT JSON_OBJECT('a', 1, 'b', 2); 返回 {"a": 1, "b": 2}
  • JSON_QUOTE()：将字符串引用为JSON值。例如：SELECT JSON_QUOTE('[1, 2, "abc"]'); 返回 "[1, 2, \"abc\"]"
  • CAST(anyValue AS JSON)：将值转换为JSON类型以确保有效性：SELECT CAST('{"a": 1, "b": 2}' AS JSON);

JSON_TYPE()函数允许检查JSON值类型，应返回OBJECT、ARRAY、标量类型（INTEGER、BOOLEAN等）、NULL或错误。例如：

1
2
3
4
5
6
7
8

-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');

JSON_VALID()函数在JSON有效时返回1，否则返回0：

1
2
3
4
5
6
7
8

-- 返回1：
SELECT JSON_VALID('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_VALID('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_VALID('{"a": 1, "b": 2');

尝试插入无效的JSON文档将引发错误，整个记录不会被插入/更新。

在MySQL JSON列中搜索JSON文档

使用JSON_CONTAINS()等MySQL JSON函数可以检查JSON文档是否包含特定值。找到匹配项时返回1。例如：

1
2

-- 所有带有'JavaScript'标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

JSON_SEARCH()函数返回JSON文档中值的路径。没有匹配项时返回NULL。

还可以通过传递’one’和’all’标志以及搜索字符串来指定需要查找所有匹配项还是单个匹配项（其中%匹配任意数量的字符，_匹配一个字符，与LIKE相同）。例如：

1
2

-- 所有标签以'Java'开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON_TABLE()函数将JSON数据转换为关系格式以便于查询：

1
2
3
4
5

SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

JSON路径

使用JSON_EXTRACT()函数的MySQL JSON查询可以根据指定路径从JSON文档中检索特定值。

1
2

-- 返回"SitePoint"：
SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

所有路径定义以$开头，后跟其他选择器：

  • 后跟名称的句点，如$.website
  • [N]，其中N是零索引数组中的位置
  • .[*]通配符计算对象的所有成员
  • [*]通配符计算数组的所有成员
  • prefix**suffix通配符计算以指定前缀开头并以指定后缀结尾的所有路径

以下示例引用以下JSON文档：

1
2
3
4
5
6
7
8
9

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

示例路径：

  • $.a 返回 1
  • $.c 返回 [3, 4]
  • $.c[1] 返回 4
  • $.d.e 返回 5
  • $**.e 返回 [5]

可以使用JSON提取MySQL函数高效地从书籍表中提取名称和第一个标签：

1
2
3

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

更复杂的示例，假设有一个包含JSON配置文件数据的用户表：

id name profile
1 Craig { “email”: [“craig@email1.com”, “craig@email2.com”], “twitter”: “@craigbuckler” }
2 SitePoint { “email”: [], “twitter”: “@sitepointdotcom” }

可以使用JSON路径提取Twitter名称：

1
2
3

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

可以在WHERE子句中使用JSON路径仅返回有Twitter帐户的用户：

1
2
3
4
5

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

修改JSON文档的部分内容

有多个MySQL函数使用路径表示法修改JSON文档的部分内容：

  • JSON_SET(doc, path, val[, path, val]…)：插入或更新文档中的数据

    1

    UPDATE book SET tags = JSON_SET(tags, '$[0]', 'Updated Tag');

  • JSON_INSERT(doc, path, val[, path, val]…)：插入数据而不覆盖现有值

    1

    UPDATE book SET tags = JSON_INSERT(tags, '$[0]', 'New Tag');

  • JSON_REPLACE(doc, path, val[, path, val]…)：替换文档中的数据

    1

    UPDATE book SET tags = JSON_REPLACE(tags, '$[0]', 'Replaced Tag');

  • JSON_MERGE_PATCH(doc, doc[, doc]…)：合并两个或多个JSON文档，用后续文档的值替换现有键

    1

    UPDATE book SET tags = JSON_MERGE_PATCH(tags, '["technical"]') WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

  • JSON_ARRAY_APPEND(doc, path, val[, path, val]…)：将值追加到数组末尾

    1

    UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');

  • JSON_ARRAY_INSERT(doc, path, val[, path, val]…)：在JSON数组的特定位置插入值

    1

    UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'Inserted Tag');

  • JSON_REMOVE(doc, path[, path]…)：从文档中删除数据

    1

    UPDATE book SET tags = JSON_REMOVE(tags, '$[1]');

  • JSON_PRETTY(val)：美化JSON文档以提高可读性

    1

    SELECT JSON_PRETTY('{"name": "SitePoint", "tags": ["MySQL", "JSON"]}');

例如，如果要为已有"JavaScript"标签的书籍添加"technical"标签，可以使用JSON_MERGE_PATCH()函数：

1
2
3

UPDATE book
SET tags = JSON_MERGE_PATCH(tags, '["technical"]')
WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

更多信息

MySQL文档提供了有关MySQL JSON数据类型和相关JSON函数的详细信息。

再次强调，除非绝对必要，否则不要使用JSON。虽然可以在MySQL中模拟整个面向文档的NoSQL数据库，但这会抵消SQL的许多优势，不如切换到真正的NoSQL系统！

也就是说，JSON数据类型可能为SQL应用程序中更晦涩的数据需求节省精力。

MySQL中处理JSON数据的常见问题

可以在MySQL中使用JSON吗？

MySQL通过提供JSON数据类型来支持JSON，用于在列中存储JSON格式的数据。从MySQL 5.7.8开始，可以创建带有JSON列的表，允许使用SQL插入、更新和查询JSON数据。MySQL提供了一系列JSON函数来处理这些列中的JSON数据，实现提取、修改和操作。

此外，可以在SQL查询中使用JSON数据，在需要时使用JSON_TABLE等函数将其转换为关系数据。但重要的是要理解，MySQL本质上是关系型数据库，其JSON数据类型支持旨在促进在关系上下文中处理JSON数据，而不是成为完整的NoSQL JSON数据库。

如上所述，仅仅因为可以存储JSON，并不意味着应该这样做：规范化是优化数据库结构的技术。第一范式（1NF）规则要求每列应包含单个值——存储多值JSON文档违反了此规则。

在MySQL中存储JSON可以吗？

在以下场景中可以在MySQL中存储JSON：

  • 半结构化或动态数据，不适合严格模式
  • 自定义属性，关系设计效率低下
  • 与基于JSON的API集成，存储有效负载或日志

但是，JSON不应替代结构化且经常查询的关系存储。虽然MySQL 9.1通过函数索引和JSON_TABLE等功能改进了JSON功能，但JSON操作可能仍会为大型数据集或复杂查询带来开销。

如何在MySQL查询中使用JSON？

可以通过使用MySQL的JSON函数在MySQL查询中使用JSON。这些函数能够提取、操作和查询存储在JSON列或数据库内JSON格式字符串中的JSON数据。要访问JSON列中的JSON数据，使用->运算符后跟所需JSON元素的路径。

JSON_EXTRACT、JSON_SET和JSON_OBJECTAGG等JSON函数允许过滤、修改、聚合和处理JSON数据。还可以使用WHERE子句基于JSON值过滤行。MySQL的JSON功能提供了在数据库查询中直接交互和操作JSON对象的灵活方式。

何时在MySQL中使用JSON？

应在以下场景中使用JSON：

  • 半结构化数据：处理不可预测或稀疏字段时（例如自定义属性）
  • 动态模式：数据需求频繁变化时，JSON提供灵活性
  • 层次或嵌套数据：JSON支持具有父子关系或数组的数据
  • API集成：将有效负载、响应或日志存储为JSON文档

但是，避免在以下情况使用JSON：

  • 需要索引的经常查询字段（函数索引可能有帮助，但关系设计通常更快）
  • 需要规范化的严格关系数据
  • 复杂JSON路径查询会降低性能的情况

如何在MySQL中存储JSON数据？

在MySQL中存储JSON数据有两种主要选择。首先，可以使用MySQL中引入的JSON数据类型创建带有JSON列的表。此方法为JSON数据提供结构化存储和更好的查询性能。

或者，可以将JSON数据作为文本存储在常规VARCHAR或TEXT列中。当主要需要存储和检索JSON数据而无需复杂数据库操作时，此方法适用。

如何在MySQL中索引JSON数据？

虽然无法直接索引JSON列，但MySQL允许基于JSON值创建生成列的函数索引。

例如，要索引JSON数组的第一个元素：

1
2
3

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

这种方法提高了频繁访问的JSON路径的查询性能。

应该使用MySQL还是NoSQL数据库处理JSON数据？

这取决于项目需求：

  • 如果需要关系存储并偶尔处理半结构化数据、自定义属性或关系模型中的层次数据，选择MySQL
  • 如果项目涉及大量JSON存储、灵活模式和基于文档的操作作为主要用例，选择NoSQL数据库（如MongoDB）

MySQL的JSON支持非常适合混合工作负载，但不能完全替代用于文档存储的专用NoSQL数据库。

如何从MySQL JSON字段提取特定值？

要

comments powered by Disqus
© 2020 - 2025 qife
使用 Hugo 构建
主题 Stack 由 Jimmy 设计
本站总访问量次 本站访客数人次