×

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

作者:Terry2021.03.31来源:Web前端之家浏览:8602评论:1
关键词:mysqlsql

500.jpg

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概要文件数据的用户表。例如:

ID姓名轮廓
1terry{“电子邮件”:[“ craig@email1.com”,“ craig@email2.com”],“ twitter”:“ @ craigbuckler”}
2SitePoint{“电子邮件”:[],“ 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”标签的图书中:

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应用程序中更加晦涩难懂的数据需求节省精力。

您的支持是我们创作的动力!
温馨提示:本文作者系Terry ,经Web前端之家编辑修改或补充,转载请注明出处和本文链接:
https://jiangweishan.com/article/mysql20210331a1.html

网友评论文明上网理性发言 已有1人参与

发表评论:

评论列表