在mysql中使用json数据类型

发表于
You are my today and all of my tomorrows.

In my article SQL vs NoSQL: The Differences, I mentioned the line between SQL and NoSQL databases has become increasingly blurred with each camp adopting features from the other. MySQL 5.7 InnoDB and PostgreSQL 9.4 databases both directly support JSON document types in a single field. In this article, we’ll examine MySQL’s JSON implementation in more detail.

(PostgreSQL supported JSON before version 9.4 and any database will accept JSON documents as a single string blob. However, MySQL and PostgreSQL now directly support validated JSON data in real key/value pairs rather than a basic string.)

Just Because You Can Store JSON …

… it doesn’t follow you should.

Normalization is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is broken by storing multi-value JSON documents.

If you have clear relational data requirements, use appropriate single-value fields. JSON should be used sparingly as a last resort. JSON value fields cannot be indexed, so avoid using it on columns which are updated or searched regularly. In addition, fewer client applications support JSON, and the technology is newer and possibly less stable than other types.

That said, there are good JSON use-cases for sparsely-populated data or custom attributes.

Create a Table with a JSON Field

Consider a shop selling books. A book table will have an ID, ISBN, title, publisher, number of pages and other relational data which applies to all books. Presume we want to add any number of category tags to any book. We could achieve this in SQL using:

  • a tag table which stored each tag name against a unique ID, and
  • a tagmap table with many-to-many records mapping book IDs to tag IDs

It’ll work, but it’s cumbersome and considerable effort for a minor feature. Therefore, we’ll define a tags JSON field in our MySQL database’s book table:

1
2
3
4
5
6
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;

Note that JSON columns cannot have a default value, be used as a primary key, be referenced as a foreign key or have an index. You can create secondary indexes on generated virtual columns, but it’s possibly easier to retain an indexed value in a separate field.

Adding JSON Data

Whole JSON documents can be passed in INSERT or UPDATE statements. For example, our book tags can be passed as an array:

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

JSON can also be created with the:

  • JSON_ARRAY() function which creates arrays, e.g.

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

  • JSON_OBJECT() function which creates objects, e.g.

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

  • JSON_MERGE() function to merge documents, e.g.

    1
    2
    -- returns ["a", 1, {"key": "value"}]:
    SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

  • or you can (CAST anyValue AS JSON).

The JSON_TYPE() function allows you to check JSON value types. It should return OBJECT, ARRAY or an error, e.g.

1
2
3
4
5
6
7
8
-- 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');

Similarly, the JSON_VALID() function returns 1 when the JSON is valid:

1
2
3
4
5
6
7
8
-- 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');

Attempting to insert an invalid JSON document will raise an error, and the whole record will not be inserted/updated.

Searching JSON Data

The JSON_CONTAINS() function accepts the JSON document being searched and another to compare against. It returns 1 when a match is found, e.g.

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

The similar JSON_SEARCH() function returns the path to the given match or NULL when there’s no match. It is passed the JSON document being searched, ‘one’ to find the first match or ‘all’ to find all matches, and a search string, e.g.

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

JSON Paths

A JSON path targets values and can be used to extract or modify parts of a document. The JSON_EXTRACT() function demonstrates this by extracting one or more values:

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

https://www.sitepoint.com/use-json-data-fields-mysql-databases/