如何在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文档的函数如JSON_SET()和JSON_MERGE_PATCH()。
MySQL 9.1支持从JSON数据派生的生成列上的函数索引，从而能够高效查询特定的JSON元素。
尽管MySQL支持JSON，但它仍然是一个关系数据库。过度使用JSON可能会抵消SQL的优势。

仅仅因为您可以在MySQL JSON列中存储JSON文档…

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

规范化是一种用于优化数据库结构的技术。第一范式（1NF）规则规定每一列应保存一个单一值——这显然被存储多值JSON文档所打破。

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

从JSON派生的生成列上的函数索引允许您索引JSON对象的部分，从而提高查询性能。

也就是说，对于稀疏数据或自定义属性，有很好的JSON用例。

创建带有JSON数据类型列的表

考虑一个销售书籍的商店。所有书籍都有ID、ISBN、标题、出版商、页数和其他明确的关系数据。

现在，如果您想为每本书添加任意数量的类别标签。您可以使用SQL实现这一点：

一个存储每个标签名称及其唯一ID的标签表，以及
一个具有多对多记录将书籍ID映射到标签ID的标签映射表

这会奏效，但对于一个小功能来说，它很繁琐且需要相当大的努力。因此，您可以在MySQL数据库的书籍表中为标签定义一个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中使用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函数如JSON_EXTRACT、JSON_SET和JSON_OBJECTAGG允许您过滤、修改、聚合和处理JSON数据。您还可以使用WHERE子句基于JSON值过滤行。MySQL的JSON功能提供了一种在数据库查询中直接交互和操作JSON对象的通用方式。

何时在MySQL中使用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字段中提取特定值？

要从MySQL JSON字段中提取特定值，请使用JSON_EXTRACT()函数或简写->运算符。

1
2
3
4
5


# 使用JSON_EXTRACT
SELECT JSON_EXTRACT(tags, '$[0]') AS first_tag FROM book;

# 使用简写
SELECT tags->'$[0]' AS first_tag FROM book;

如何查询和过滤MySQL JSON字段中的数据？

要查询和过滤存储在MySQL JSON字段中的数据，您可以使用JSON_CONTAINS()和JSON_SEARCH()等函数。您还可以使用JSON_EXTRACT()检索特定值以进行进一步过滤。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


# 使用JSON_CONTAINS
SELECT * FROM book 
WHERE JSON_CONTAINS(tags, '["JavaScript"]');

# 使用JSON_SEARCH
SELECT * FROM book 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

# 使用JSON_EXTRACT
SELECT * FROM book 
WHERE JSON_EXTRACT(tags, '$[0]') = 'JavaScript';

深入解析MySQL中JSON数据字段的应用与操作技巧

本文详细介绍了如何在MySQL数据库中高效使用JSON数据字段，包括创建JSON列、插入与查询JSON数据、使用JSON路径提取值、修改JSON文档以及索引优化等实用技术，帮助开发者在关系型数据库中灵活处理半结构化数据。