MySQL 的 JSON 数据类型
概述
JavaScript Object Notation (JSON) 是一种轻量级、基于文本的文件格式，类似于 YAML 或 XML，它简化了数据交换。JSON 由 Douglas Crockford 在 2000 年代初发明，并随着文档型数据库（又称 NoSQL 数据库）的兴起越来越流行。
JSON 支持字符串、数字、布尔值、对象、数组以及空值。以下是一个简单的 JSON 示例，其中包含键值对 (“key-value pairs”)、一个名为 "bandMembers" 的对象以及一个名为 "songs" 的数组：
{
"artist": "Starlord Band",
"bandMembers": {
"vocals": "Steve Szczepkowski",
"guitar": "Yohann Boudreault",
"bass": "Yannick T.",
"drums": "Vince T."
},
"bandMembersCount": 4,
"album": "Space Rider",
"releaseDate": "2021-10-25",
"songs": [
"Zero to Hero",
"Space Riders with No Names",
"Ghost",
"Bit of Good (Bit of Bad)",
"Watch me shine",
"We're Here",
"The Darkness inside",
"No Guts No Glory",
"All for One",
"Solar Skies"
],
"songsCount": 10
}
MySQL 从 5.7.8 版本开始（2015年中发布）为 JSON 数据类型实现了基本支持，并且自此之后不断推出改进和新功能。七年后的今天，MySQL 支持了许多可用于处理 JSON 文档的 SQL 函数，还提供了自动内容验证、支持部分就地更新，并采用二进制存储格式以提高性能。
何时使用 JSON
关系型数据库遵循预定义结构，注重数据一致性和完整性。为了实现这一点，它通过架构严格强制执行数据类型、格式以及数据大小。
然而，JSON 数据类型在架构的这种严格性质下显得有些“反模式”（anti-pattern）。JSON 提供了更多灵活性，让您在需要时突破这些限制。而且，只要您了解其权衡（见下一节），它就能在特定场景中发挥作用。
以下是一些可能需要存储 JSON 文档的数据场景：
- 应用程序或服务器的日志输出
- 一份 Rest API 的响应数据希望保存下来
- 存储配置数据
- 一组具有可变属性的实体
此外，在关系型数据库设计中，您还可以使用 JSON 文档来简化跨多个表的复杂关系。这种过程称为 **反规范化**（denormalization），同样是一种关系型数据库的反模式。然而，在某些情况下，根据您的使用案例和应用设计，它可能带来性能提升。
注意事项
JSON 数据类型的灵活性带来了一些需要注意的事项：
1. 存储开销
JSON 文档通常需要更多存储空间。在 MySQL 中，JSON 的存储足迹类似于 LONGBLOB 或 LONGTEXT 数据类型。然而，由于其二进制编码以及为加速数据库读取而添加的元数据和字典，JSON 还有额外开销。一个简单的经验法则是，与 LONGBLOB 或 LONGTEXT 列相比，存储在 JSON 中的字符串大约会多占用 4 到 10 字节的额外空间。
如果您的目标是优化数据库架构以提高存储效率，建议选择 MySQL 的传统数据类型（如 CHAR、VARCHAR、INT），它们在存储效率方面往往比 JSON 更胜一筹。
2. 性能影响
类似于其他二进制格式，JSON 文档无法直接索引。此外，由于 JSON 文档中可以存储的变量数据量较大，这意味着查询 JSON 列通常需要更多的缓冲空间，并会返回较大的结果集，从而导致更多的数据交换。
注意：虽然 JSON 文档无法直接索引，但可以间接索引。可参阅文章“**MySQL 中的 JSON 索引**”了解详细内容。
3. JSON 文档大小的建议
虽然理论上 MySQL 可以存储最大 1 GB 的 JSON 文档，但建议将 JSON 文档的大小保持在几 MB 内。在 PlanetScale 中，我们支持最多 67 MB 的 JSON 文档。
JSON 函数
MySQL 提供了一组强大的 JSON 函数，支持创建、更新、读取或验证 JSON 文档。PlanetScale 支持所有 JSON 函数，唯一不支持的是 JSON_TABLE 功能。
常见操作
让我们通过几个示例来展示如何操作 JSON。
首先，创建一个带有 INTEGER 和 JSON 列的表：
CREATE TABLE songs ( id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON );
向空表添加数据，可以使用 JSON_ARRAY 创建一个 JSON 数组：
INSERT INTO songs VALUES(id, JSON_ARRAY( 'Zero to Hero', 'Space Riders with No Names', 'Ghost', 'Bit of Good (Bit of Bad)', 'Watch me shine', 'We\'re Here', 'The Darkness inside', 'No Guts No Glory', 'All for One', 'Solar Skies' ));
如何确认它是一个数组？可以使用 JSON_TYPE 来获取类型：
SELECT JSON_TYPE(songs) FROM songs;
输出：
+------------------+ | json_type(songs) | +------------------+ | ARRAY | +------------------+
通过 JSON_EXTRACT 可以从数组中提取某个元素，例如提取数组中的第四个元素：
SELECT JSON_EXTRACT(songs, '$[3]') FROM songs;
输出：
+-----------------------------+ | json_extract(songs, '$[3]') | +-----------------------------+ | "Ghost" | +-----------------------------+
您还可以使用 -> 操作符，它是 JSON_EXTRACT 的简便符号：
SELECT songs->'$[3]' FROM songs;
输出：
+-----------------+ | songs -> '$[3]' | +-----------------+ | "Ghost" | +-----------------+
如果需要去除结果的双引号，可以使用 ->>，它是 JSON_UNQUOTE(JSON_EXTRACT()) 的简写：
SELECT songs->>'$[3]' FROM songs;
输出：
+------------------+ | songs ->> '$[3]' | +------------------+ | Ghost | +------------------+
要向 JSON 数组中添加数据，可以使用 JSON_ARRAY_APPEND 或 JSON_ARRAY_INSERT 进行更新：
UPDATE songs SET songs = JSON_ARRAY_APPEND(songs, '$', "One last song"); UPDATE songs SET songs = JSON_ARRAY_INSERT(songs, '$[0]', "First song");
有关所有 JSON 函数的详细使用方法，请参考 MySQL 的 JSON 数据类型文档 和 JSON 函数参考。
总结
JSON 数据类型为 MySQL 提供了灵活的数据存储方式，适合于不符合严格关系型数据库设计的场景。虽然 JSON 具有一定的局限性，比如存储空间较大和性能影响，但其灵活性在某些场景中非常有用。通过多样的 JSON 函数，您可以方便地创建、操作和查询 JSON 数据，从而更高效地实现复杂的数据管理需求。
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
