MySQL 数据类型：JSON
Mike Stojan [hudson译]
2022年9月23号
概述
JavaScript Object Notation（JSON）是一种轻量级的基于文本的文件格式，类似于YAML或XML，可以简化数据交换。它由Douglas Crockford在21世纪初发明，随着基于文档（也称为NoSQL）的数据库的兴起而变得越来越流行。
JSON支持字符串、数字、布尔值、对象和数组以及空值。一个简单JSON示例如下，它包含键值对、对象“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在2015年年中通过5.7.8版本实现了对JSON数据类型的基本支持，并一直在添加改进和新功能。七年后，MySQL现在支持众多SQL函数来处理JSON文档，它提供了自动内容验证，允许部分就地更新，并使用二进制存储格式来提高性能。
何时使用JSON
关系数据库遵循预定的结构，并强调数据的内聚性和完整性。为了实现这一点，它的数据类型和格式以及数据大小都是通过模式严格保证。
JSON数据类型对这种模式的严格性来说有点反模式。它允许您突破它，在需要时获得灵活性。只要您了解下一节中描述的权衡，它就会证明是有用的。
以下示例中将数据存储为JSON文档可能有好处：
- 应用程序或服务器的输出日志
- 要存储Rest API响应
- 存储配置数据
- 一组具有可变属性的实体
您还可以在关系数据库设计中使用JSON文档来分解跨越多个表的复杂关系。这个过程称为非规范化，这是另一种关系数据库反模式。然而，在某些情况下，根据您的用例和应用程序设计，它能够提高性能。
注意事项
JSON数据类型提供的灵活性附带了一些需要注意的地方。
最值得注意的是，您需要考虑到JSON文档通常需要更多存储容量。在MySQL中，它们的存储占用空间类似于LONGBLOB或LONGTEXT数据类型。但是，由于二进制编码以及添加的元数据和字典，会有一些开销，这些都是为了加快数据库读取速度。一个很好的经验法则是，与LONGBLOB或LONGTEXT列相比，存储在JSON中的字符串需要大约4到10个字节的额外存储空间。
如果您想优化数据库模式以提高存储效率，最好使用MySQL更传统的数据类型（CHAR、VARCHAR、 INT等），因为它们都比JSON可能具有的存储效率更高。
另一个需要注意的是性能影响。与其他二进制格式类似，JSON文档不能直接索引。考虑存储在JSON文档中的可变数据量，这意味着查询JSON列通常会使用更多的缓冲空间并返回更大的结果集，从而导致更多的数据交换。
虽然存储在MySQL中的JSON文档最多可达1GB，但理论上，建议将JSON文档的大小保持在几MB。在PlanetScale上，我们支持高达67MB的JSON文档。
JSON函数
MySQL附带了一组健壮的JSON函数，使您能够创建、更新、读取或验证JSON文档。PlanetScale支持除“JSON_TABLE`”之外的所有JSON函数。
常见操作
让我们一起来看看几个例子。
首先，我们创建一个带有INTEGER和JSON列的表。
CREATE TABLE songs (id int AUTO_INCREMENT PRIMARY KEY NOT NULL, songs JSON);
空表需要数据，所以让我们使用JSON_ARRAY添加一些数据。
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。在下面的示例中，我们从数组中提取第四个元素。
blog-mysql-json/main> SELECT JSON_EXTRACT(songs, '$[3]') FROM songs;
+-----------------------------+
| json_extract(songs, '$[3]') |
+-----------------------------+
| "Ghost" |
+-----------------------------+
我们还可以使用->，它是与JSON_EXTRACT等价的运算符。
blog-mysql-json/main> SELECT songs->'$[3]' FROM songs;
+-----------------+
| songs -> '$[3]' |
+-----------------+
| "Ghost" |
+-----------------+
如果需要未加引号的结果，可以使用->>，它是JSON_UNQUOTE(JSON_EXTRACT())的缩写。
blog-mysql-json/main> 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");
