本文探讨了MySQL 9.1中JSON实现的细节，延续了之前文章《SQL vs NoSQL: The Differences》中关于SQL和NoSQL数据库界限模糊的论点，两者都在互相借鉴特性。MySQL 5.7 InnoDB数据库和PostgreSQL 9.2 都直接支持在单个字段中存储JSON文档类型。
需要注意的是，任何数据库都能将JSON文档作为单个字符串blob存储。然而，MySQL和PostgreSQL支持将验证后的JSON数据存储为真正的键值对，而不是简单的字符串。
关键要点
在MySQL JSON列中存储JSON文档并不意味着你应该这样做
规范化是一种用于优化数据库结构的技术。第一范式(1NF)规则规定每一列都应该保存单个值——而存储多值JSON文档显然违反了这一规则。
如果你的数据有明确的关系型数据需求，请使用合适的单值字段。JSON应该作为最后手段谨慎使用。JSON值字段不能直接索引，因此避免在经常更新或搜索的列上使用它们。
对从JSON数据派生的生成列进行函数索引，允许你索引JSON对象的某些部分，从而提高查询性能。
也就是说，对于稀疏数据或自定义属性，JSON有一些很好的用例。
创建带有JSON数据类型列的表
考虑一家销售书籍的商店。所有书籍都有ID、ISBN、标题、出版商、页数和其他明确的关系型数据。
现在，如果你想为每本书添加任意数量的类别标签。你可以在SQL中使用以下方法实现：
这种方法有效，但对于一个次要功能来说，它过于繁琐，需要付出相当大的努力。因此，你可以在MySQL数据库的book表中为标签定义一个MySQL JSON字段：
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文档中的特定元素。这些生成的列可以是虚拟的或存储的，并作为辅助索引进行索引。
ALTER TABLE book ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')), ADD INDEX idx_first_tag (first_tag);
添加JSON数据
可以在INSERT或UPDATE语句中传入整个JSON文档，从而可以轻松地将JSON移动到MySQL进行存储和操作。
例如，我们的书籍标签可以作为数组（在字符串内）传递：
INSERT INTO `book` (`title`, `tags`) VALUES ( 'ECMAScript 2015: A SitePoint Anthology', '["JavaScript", "ES2015", "JSON"]' );
也可以使用以下函数创建JSON：
JSON_TYPE()函数允许你检查JSON值的类型。它应该返回OBJECT、ARRAY、标量类型（INTEGER、BOOLEAN等）、NULL或错误。例如：
-- 返回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：
SELECT JSON_TYPE('[1, 2, "abc"]');
-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');
-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');尝试插入无效的JSON文档将引发错误，并且不会插入/更新整个记录。
在MySQL JSON列中搜索JSON文档
使用JSON_CONTAINS()函数等MySQL JSON函数，可以检查JSON文档是否包含特定值。找到匹配项时返回1。例如：
-- 所有带有“JavaScript”标签的书籍： SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');
JSON_SEARCH()函数返回JSON文档中值的路径。没有匹配项时返回NULL。
你还可以通过传递“one”和“all”标志以及搜索字符串来指定是否需要查找所有匹配项或单个匹配项（其中%与任意数量的字符匹配，_与LIKE一样匹配一个字符）。例如：
-- 所有标签以“Java”开头的书籍： SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;
JSON_TABLE()函数将JSON数据转换为关系格式，以便更容易查询：
SELECT *
FROM JSON_TABLE(
'[{"tag": "SQL"}, {"tag": "JSON"}]',
'$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;JSON路径
使用JSON_EXTRACT()函数的MySQL JSON查询可以根据指定的路径从JSON文档中检索特定值。
CREATE TABLE `book` ( `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(200) NOT NULL, `tags` JSON DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB;
所有路径定义都以$开头，后跟其他选择器：
以下示例指的是以下JSON文档：
ALTER TABLE book ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')), ADD INDEX idx_first_tag (first_tag);
路径示例：
你可以使用JSON提取MySQL函数有效地从你的book表中提取名称和第一个标签：
INSERT INTO `book` (`title`, `tags`) VALUES ( 'ECMAScript 2015: A SitePoint Anthology', '["JavaScript", "ES2015", "JSON"]' );
对于更复杂的示例，假设你有一个包含JSON配置文件数据的用户表。例如：
| id | name | profile |
|---|---|---|
| 1 | Craig | { "email": ["craig@email1.com", "craig@email2.com"], "twitter": "@craigbuckler" } |
| 2 | SitePoint | { "email": [], "twitter": "@sitepointdotcom" } |
你可以使用JSON路径提取Twitter名称。例如：
CREATE TABLE `book` ( `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(200) NOT NULL, `tags` JSON DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB;
你可以在WHERE子句中使用JSON路径，只返回有Twitter帐户的用户：
ALTER TABLE book ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')), ADD INDEX idx_first_tag (first_tag);
修改JSON文档的一部分
有几个MySQL函数可以使用路径表示法修改JSON文档的一部分。这些函数包括：
例如，如果你想为任何已经具有“JavaScript”标签的书籍添加“technical”标签，可以使用JSON_MERGE_PATCH()函数：
INSERT INTO `book` (`title`, `tags`) VALUES ( 'ECMAScript 2015: A SitePoint Anthology', '["JavaScript", "ES2015", "JSON"]' );
更多信息
MySQL文档提供了关于MySQL JSON数据类型和相关JSON函数的详细信息。
再次提醒，除非绝对必要，否则不要使用JSON。你可以在MySQL中模拟整个面向文档的NoSQL数据库，但这会抵消SQL的许多好处，你不如直接切换到真正的NoSQL系统！
也就是说，对于SQL应用程序中更模糊的数据需求，JSON数据类型可能会节省一些工作量。
关于在MySQL中使用JSON数据的常见问题
你可以在MySQL中使用JSON吗？
MySQL通过提供JSON数据类型来支持JSON，该类型用于在列中存储JSON格式的数据。从MySQL 5.7.8开始，你可以创建带有JSON列的表，允许你使用SQL插入、更新和查询JSON数据。MySQL提供了一系列JSON函数来处理这些列中的JSON数据，从而可以提取、修改和操作JSON数据。
此外，你可以在SQL查询中使用JSON数据，在需要时使用JSON_TABLE等函数将其转换为关系数据。但是，重要的是要理解，MySQL从根本上来说是一个关系型数据库，其JSON数据类型支持旨在促进在关系上下文中处理JSON数据，而不是成为一个成熟的NoSQL JSON数据库。
如上文所述，仅仅因为你可以存储JSON，并不意味着你应该这样做：规范化是一种用于优化数据库结构的技术。第一范式(1NF)规则规定每一列都应该保存单个值——而存储多值JSON文档违反了这一规则。
在MySQL中存储JSON是否可以？
在以下情况下，在MySQL中存储JSON是可以的：
但是，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？
你应该在以下情况下在MySQL中使用JSON：
但是，避免在以下情况下使用JSON：
如何在MySQL中存储JSON数据？
要在MySQL中存储JSON数据，你有两个主要选项。首先，你可以使用MySQL中引入的JSON数据类型来创建一个带有JSON列的表。此方法为JSON数据提供结构化存储和更好的查询性能。
或者，你可以在常规VARCHAR或TEXT列中将JSON数据存储为文本。当主要需要存储和检索JSON数据而无需复杂的数据库操作时，此方法适用。
如何在MySQL中索引JSON数据？
虽然你不能直接索引JSON列，但MySQL允许你对从JSON值派生的生成列创建函数索引。
例如，要索引JSON数组的第一个元素：
CREATE TABLE `book` ( `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(200) NOT NULL, `tags` JSON DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB;
此方法提高了对经常访问的JSON路径的查询性能。
对于JSON数据，你应该使用MySQL还是NoSQL数据库？
这取决于你的项目需求：
MySQL的JSON支持非常适合混合工作负载，但不能完全替代专门用于文档存储的NoSQL数据库。
如何从MySQL JSON字段中提取特定值？
要从MySQL JSON字段中提取特定值，请使用JSON_EXTRACT()函数或简写->运算符。
ALTER TABLE book ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')), ADD INDEX idx_first_tag (first_tag);
如何查询和过滤MySQL JSON字段中的数据？
要查询和过滤存储在MySQL JSON字段中的数据，可以使用JSON_CONTAINS()和JSON_SEARCH()等函数。你还可以使用JSON_EXTRACT()检索特定值以进行进一步过滤。
INSERT INTO `book` (`title`, `tags`) VALUES ( 'ECMAScript 2015: A SitePoint Anthology', '["JavaScript", "ES2015", "JSON"]' );
以上是如何在MySQL数据库中使用JSON数据字段的详细内容。更多信息请关注PHP中文网其他相关文章！
