MySQL中的JSON

Valineliu

已于 2022-08-27 20:55:40 修改

阅读量8.5k

点赞数 4

分类专栏： mysql 文章标签： mysql json database

于 2022-08-25 22:52:44 首次发布

本文链接：https://blog.csdn.net/u012877472/article/details/126534123

版权

从5.7.8开始，MySQL开始支持JSON类型，用于存储JSON数据。

JSON类型的加入模糊了关系型数据库与NoSQL之间的界限，给日常开发也带来了很大的便利。

这篇文章主要介绍一下MySQL中JSON类型的使用，主要参考MySQL手册：https://dev.mysql.com/doc/refman/8.0/en/

1. 为什么要用JSON

自从MySQL添加对JSON的支持之后，一些表结构变更的操作就变得简单了一些。

1.1 JSON的使用场景

虽然关系型数据库一直很有效，但是面对需求的不断变化，文档型数据库更加灵活方便。

MySQL支持JSON之后，模糊了关系型与文档型数据库之间的界限。

在开发过程中经常会遇见下面几种情况：

表中仅仅小部分数据需要新添加的字段；
当这个新添加的字段很有可能只是临时使用后续会废弃的时候；
当后面还不知道要新添加什么字段但大概率要添加的时候。

这些时候，使用一个JSON进行存储比较合适，不用更改表结构，非常方便。

1.2 字符串还是JSON类型

在还不支持JSON的MySQL 5.7版本之前，没有选择只能使用一个字符串类型存储JSON数据了。

但是如果数据库支持JSON类型，那么就还是使用JSON类型吧。

JSON类型相比与使用字符串存储JSON数据有如下的好处：

可以对存储的JSON数据自动校验，不合法的JSON数据插入时会报错；
优化的存储结构。JSON类型将数据转化为内部结构进行存储，使得可以对JSON类型数据进行搜索与局部变更；而对于字符串来说，需要全部取出来再更新。

2. JSON的增删改查

这里将简单介绍一下JSON类型的使用，主要是增删改查等操作。

MySQL中使用utf8mb4字符集以及utf8mb4_bin字符序来处理JSON中的字符串，因此JSON中的字符串时大小写敏感的。

2.1 创建JSON列

创建一个JSON类型的列很简单：

CREATE TABLE videos (
	id int NOT NULL AUTO_INCREMENT,
	ext json NOT NULL,
	PRIMARY KEY (id)
);

我们构建了一个表videos，里面有一个JSON类型的ext字段，用于存储一些扩展数据。

2.2 插入JSON值

和其它类型一样，使用INSERT来插入数据：

INSERT INTO videos
VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}'),
	(2, '{"vid": "vid2", "tags":[], "title": "title2", "logo": false}'),
	(3, '{"vid": "vid3", "title": "title3"}');

来看一下现在表里的数据：

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {
   "vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {
   "vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {
   "vid": "vid3", "title": "title3"}                                          |
+----+-----------------------------------------------------------------------------+

每一个ext都是一个JSON数据。

2.3 校验JSON

使用JSON类型的一个好处就是MySQL可以自动检查数据的有效性，避免插入非法的JSON数据。

2.3.1 JSON合法性校验

首先需要校验一个值是否是一个合法的JSON，否则插入会失败：

mysql> insert into videos values (1, '{');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'videos.ext'.

同时还可以使用JSON_VALID()函数查看一个JSON值是否合法：

mysql> select json_valid('{');
+-----------------+
| json_valid('{') |
+-----------------+
|               0 |
+-----------------+

mysql> select json_valid('{"vid": "vid1"}');
+-------------------------------+
| json_valid('{"vid": "vid1"}') |
+-------------------------------+
|                             1 |
+-------------------------------+

2.3.2 JSON模式校验

如果更进一步，除了值是否是合法JSON外，还需要校验模式，比如JSON值要包含某些字段等。

这时可以定义一个模式（schema），然后使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()函数来校验。

JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()两个函数是8.0.17版本引入的，5.7版本还没有。

定义一个模式：

{
   
	"id": "schema_for_videos",
	"$schema": "http://json-schema.org/draft-04/schema#",
	"description": "Schema for the table videos",
	"type": "object",
	"properties": {
   
		"vid": {
   
			"type": "string"
		},
		"tags": {
   
			"type": "array"
		},
		"logo": {
   
			"type": "boolean"
		},
		"title": {
   
			"type": "string"
		}
	},
	"required": ["title", "tags"]
}

字段含义：

id: 模式的唯一ID；
$schema: JSON模式校验的标准，应该是这个值保持不变；
description: 模式的描述；
type: 根元素的类型，MySQL中JSON的根元素还可以是数组（array）；
properties: JSON元素的列表，每一个元素都应该描述出来，里面列出了对应的类型；
required: 必要的元素。

在MySQL中定义一个变量：

mysql> set @schema = '{"id":"schema_for_videos","$schema":"http://json-schema.org/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"title":{"type":"string"},"tags":{"type":"array"}},"required":["title","tags"]}';
Query OK, 0 rows affected (0.04 sec)

这样就可以使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()校验一个JSON是否满足要求了：

mysql> select json_schema_valid(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      1 |
+--------+

mysql> select json_schema_validation_report(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+-----------------+
| valid?          |
+-----------------+
| {
   "valid": true} |
+-----------------+

JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()的区别就是后者可以给出不满足要求的地方：

mysql> select json_schema_valid(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      0 |
+--------+

mysql> select json_schema_validation_report(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?'\G
*************************** 1. row ***************************
valid?: {
   "valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}

当然，这两个函数的第二个参数要是一个合法的JSON，不然MySQL会报错：

mysql> select json_schema_valid(@schema, '{') as 'valid?';ERROR 3141 (22032): Invalid JSON text in argument 2 to function json_schema_valid: "Missing a name for object member." at position 1.

我们还可以将这个模式添加到表的定义上，这样插入数据就可以使用这个模式进行校验了：

ALTER TABLE videos
ADD CONSTRAINT CHECK (JSON_SCHEMA_VALID('{"id":"schema_for_videos","$schema":"http://json-schema.org/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"vid":{"type":"string"},"tags":{"type":"array"},"logo":{"type":"bool"},"title":{"type":"string"}},"required":["title","tags"]}', ext));

当然如果表里已经有数据了且不符合这个校验模式，MySQL会报错：

ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

应该修改原来的数据以满足要求后再添加校验。

添加之后，新增的数据就会进行校验：

mysql> INSERT INTO videos VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}');Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO videos VALUES (2, '{"vid": "vid2", "title": "title2"}');ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

2.4 JSON的格式化

使用JSON_PRETTY()函数进行美化输出：

mysql> select json_pretty(ext) from videos\G
*************************** 1. row ***************************
json_pretty(ext): {
   
  "vid": "vid1",
  "logo": true,
  "tags": [
    "news",
    "china"
  ],
  "title": "title1"
}

2.5 获取JSON元素

JSON字段优于JSON字符串的一点就是JSON字段可以直接获取内部的元素而不用获取整个文档。

MySQL中支持使用JSON_EXTRACT()函数以及->，->>操作符来获取JSON内部的元素：

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.a') as a;
+------+
| a    |
+------+
| 9    |
+------+
1 row in set (0.04 sec)

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.b') as b;
+-----------+
| b         |
+-----------+
| [1, 2, 3] |
+-----------+
1 row in set (0.04 sec)

mysql> select json_extract('{"a": 9, "b":[1,2,3]}', '$.b[1]') as 'b[1]';
+------+
| b[1] |
+------+
| 2    |
+------+
1 row in set (0.04 sec)

使用->：

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {
   "vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {
   "vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {
   "vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> select ext->'$.title' from videos;
+----------------+
| ext->'$.title' |
+----------------+
| "title1"       |
| "title2"       |
| "title3"       |
+----------------+
3 rows in set (0.04 sec)

->就是JSON_EXTRACT()函数的别名。

使用JSON_UNQUOTE()函数去掉引号：

mysql> select json_unquote(ext->'$.title'</

最低0.47元/天解锁文章

Valineliu

关注

4
点赞
踩
27

收藏

觉得还不错? 一键收藏
打赏
2
评论
MySQL中的JSON

从5.7.8开始，MySQL开始支持JSON类型，用于存储JSON数据。JSON类型的加入模糊了关系型数据库与NoSQL之间的界限，给日常开发也带来了很大的便利。这篇文章主要介绍一下MySQL中JSON类型的使用，主要参考MySQL手册：https://dev.mysql.com/doc/refman/8.0/en/......
复制链接

扫一扫