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

2021-07-31

我的文章鈥SQL与NoSQL的区别他指出，SQL和NoSQL数据库之间的界限越来越模糊，每个阵营都采用对方的特性。MySQL5.7 InnoDB数据库和PostgreSQL 9.2都直接支持单个字段中的JSON文档类型。在本文中，我们将更详细地研究MySQL 8.0json实现。

请注意，任何数据库都将JSON文档作为单个字符串blob接受。然而，MySQL和PostgreSQL支持以实际的键/值对而不是基本字符串来验证JSON数据。

因为你可以存储JSON鈥

…你不应该这么做

规范化是一种用于优化数据库结构的技术。第一个normalform（1NF）规则规定每一列都应该保存一个值，这显然是通过存储多值JSON文档来打破的。

如果您有明确的关系数据需求，请使用适当的单值字段。JSON应该作为最后的手段谨慎使用。JSON值字段不能被索引，所以避免在定期更新或搜索的列上使用它。此外，支持JSON的客户机应用程序较少，而且技术也较新，因此它的稳定性可能不如其他类型。

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

使用JSON字段创建表

考虑一家卖书的商店。所有书籍都有一个ID、ISBN、标题、出版商、页数和其他清晰的关系数据。假设您想为每本书添加任意数量的类别标记。您可以在SQL中使用：

一标签表，该表用唯一的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文档插入或更新声明。例如，我们的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（）函数如果JSON有效，则返回1，否则返回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'找到第一个匹配项，或者'全部'查找所有匹配项，以及一个搜索字符串（其中%匹配任意数量的字符和 _以相同的方式匹配一个字符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退货 one
$.c退货 [3, 4]
$.c[1]退货 four
$.d.e退货 five
$**.e退货 [5]

在查询中提取JSON路径

你可以提取你的名字和第一个标签书使用查询的表：

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

对于更复杂的示例，假设您有一个用户包含JSON配置文件数据的表。例如：

身份证件	名称	轮廓
one	克雷格	{电子邮件}:[“ craig@email1.com " craig@email2.com ]，“推特：@craigbuckler”]
two	站点点	[电子邮件：[]，“twitter:@sitepointdotcom”]

您可以使用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”标记的书添加“technical”标记：

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