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

热门技术勘探工
382 阅读3分钟

我的文章《SQL与NoSQL。差异》中指出，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。

这将是可行的，但对于一个小功能来说，这是很麻烦的，也是相当大的努力。因此，你可以在你的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语句中传递。例如，我们的图书标签可以作为一个数组（在一个字符串内）传递。

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()函数，它可以创建对象。例如：JSON_OBJECT()函数，创建对象。

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

  • JSON_QUOTE() 函数，将一个字符串引为JSON值。例如：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文档将引发一个错误，整个记录将不会被插入/更新。

继续阅读如何在MySQL数据库中使用JSON数据字段onSitePoint.

avatar
文章
阅读
粉丝