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

SQL和NoSQL数据库之间的界线变得越来越模糊，每个阵营都采用了彼此之间的功能。MySQL 5.7+ InnoDB数据库和PostgreSQL 9.2+都直接在单个字段中支持JSON文档类型。在本文中，我们将更详细地研究MySQL 8.0 JSON实现。

请注意，任何数据库都将JSON文档作为单个字符串Blob接受。但是，MySQL和PostgreSQL支持使用实键/值对而不是基本字符串的经过验证的JSON数据。

只是因为您可以存储JSON……

……它不符合您的要求。

规范化是一种用于优化数据库结构的技术。第一范式（1NF）规则规定，每一列都应包含一个值-通过存储多值JSON文档可以明显地打破该值。

如果您对关系数据有明确的要求，请使用适当的单值字段。JSON应该尽量不要使用。JSON值字段无法建立索引，因此请避免在定期更新或搜索的列上使用它。另外，更少的客户端应用程序支持JSON，并且该技术是较新的，因此它的稳定性可能不如其他类型。

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

创建带有JSON字段的表

考虑一家卖书的商店。所有书籍都有ID，ISBN，书名，出版商，页数和其他清晰的关系数据。假设您要向每本书添加任意数量的类别标签。您可以使用以下命令在SQL中实现此目的：

1. 一个标签，其存储每个标签名称与一个唯一的ID表，和

2. 一个带有多对多记录的tagmap表，将记录ID从ID映射到tag ID

它可以工作，但是对于次要功能来说却很麻烦且需要付出很大的努力。因此，您可以在MySQL数据库的书本表中定义标签JSON字段：

CREATE TABLE `book` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)) ENGINE=INNODB;

请注意，JSON列不能具有默认值，不能用作主键，不能用作外键或具有索引。您可以在生成的虚拟列上创建二级索引，但是如果需要索引，则将值保留在单独的字段中会更容易，更实用。

添加JSON数据

整个JSON文档可以在INSERT或UPDATE语句中传递。例如，我们的book标签可以作为数组（在字符串内）传递：

INSERT INTO `book` (`title`, `tags`)VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]');

JSON也可以使用以下命令创建：

• JSON_ARRAY()函数，该函数创建数组。例如：

  -- returns [1, 2, "abc"]:SELECT JSON_ARRAY(1, 2, 'abc');

• JSON_OBJECT()函数，该函数创建对象。例如：

  -- returns {"a": 1, "b": 2}:SELECT JSON_OBJECT('a', 1, 'b', 2);

• JSON_QUOTE()函数，该函数将字符串引用为JSON值。例如：

• -- returns "[1, 2, \"abc\"]":SELECT JSON_QUOTE('[1, 2, "abc"]');

• 或者你可以(CAST anyValue AS JSON)。

该JSON_TYPE()函数允许你检查JSON值类型。它应该返回OBJECT，ARRAY，标量类型（INTEGER，BOOLEAN等），NULL或错误。例如：

-- returns ARRAY:SELECT JSON_TYPE('[1, 2, "abc"]');-- returns OBJECT:SELECT JSON_TYPE('{"a": 1, "b": 2}');-- returns an error:SELECT JSON_TYPE('{"a": 1, "b": 2');

所述JSON_VALID()函数返回1，如果JSON是有效的，否则为0：

-- returns 1:SELECT JSON_TYPE('[1, 2, "abc"]');-- returns 1:SELECT JSON_TYPE('{"a": 1, "b": 2}');-- returns 0:SELECT JSON_TYPE('{"a": 1, "b": 2');

尝试插入无效的JSON文档将引发错误，并且整个记录将不会被插入/更新。

搜索JSON数据

该JSON_CONTAINS()函数接受JSON文件被搜索，另一个比较反对。找到匹配项时返回1。例如：

-- all books with the 'JavaScript' tag:SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

类似的JSON_SEARCH()函数将路径返回给定的匹配项，如果没有匹配项，则返回NULL。它已传递了要搜索的JSON文档，'one'以查找第一个匹配项或'all'所有匹配项，以及一个搜索字符串（其中%匹配任意数量的字符，并_以与相同的方式匹配一个字符LIKE）。例如：

-- all books with tags starting 'Java':SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON路径

JSON路径以值为目标，可用于提取或修改JSON文档的一部分。所述JSON_EXTRACT()函数提取一个或多个值演示此：

-- returns "SitePoint":SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

所有路径定义均以$后跟其他选择器开头：

• 句号后跟一个名称，例如 $.website

• [N] 其中N是零索引数组中的位置

• 所述.[*]通配符计算一个对象的所有成员

• 所述[*]通配符计算一个阵列的所有成员

• 在prefix**suffix通配符的取值为与命名前缀和结束与后缀名为开头的所有路径

以下示例引用以下JSON文档：

{  "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路径

您可以使用查询来提取书表的名称和第一个标签：

SELECT
  title, tags->"$[0]" AS `tag1`FROM `book`;

对于更复杂的示例，假设您有一个带有JSON概要文件数据的用户表。例如：

您可以使用JSON路径提取Twitter名称。例如：

SELECT
  name, profile->"$.twitter" AS `twitter`FROM `user`;

您可以在WHERE子句中使用JSON路径，仅返回具有Twitter帐户的用户：

SELECT
  name, profile->"$.twitter" AS `twitter`FROM `user`WHERE
  profile->"$.twitter" IS NOT NULL;

修改JSON文档的一部分

有几种MySQL函数可以使用路径符号来修改JSON文档的各个部分。这些包括：

• JSON_SET(doc, path, val[, path, val]...)：在文档中插入或更新数据

• JSON_INSERT(doc, path, val[, path, val]...)：将数据插入文档

• JSON_REPLACE(doc, path, val[, path, val]...)：替换文档中的数据

• JSON_MERGE(doc, doc[, doc]...)：合并两个或多个文档

• JSON_ARRAY_APPEND(doc, path, val[, path, val]...)：将值附加到数组的末尾

• JSON_ARRAY_INSERT(doc, path, val[, path, val]...)：在文档中插入一个数组

• JSON_REMOVE(doc, path[, path]...)：从文档中删除数据

因此，您可以将“技术”标签添加到任何已经具有“ JavaScript”标签的图书中：

UPDATE `book`
  SET tags = JSON_MERGE(tags, '["technical"]')WHERE
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

更多信息

MySQL手册提供了有关JSON数据类型和关联的JSON函数的更多信息。

同样，我强烈建议您除非绝对必要，否则不要使用JSON。您可以在MySQL中模拟整个面向文档的NoSQL数据库，但这会否定SQL的许多优点，并且您最好切换到真正的NoSQL系统！就是说，JSON数据类型可以为SQL应用程序中更加晦涩难懂的数据需求节省精力。