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

关键要点

JSON文档类型在MySQL 5.7+ InnoDB数据库和PostgreSQL 9.2+中直接支持，但由于直接索引的限制，应谨慎使用。

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 extract 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函数如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


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

# SHORTHAND
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列、插入数据、查询搜索、路径操作和文档修改等核心功能，同时提供了最佳实践和常见问题解答。