MySQL 的原生 JSON 操作

MySQL 从 5.7 版本开始添加了 JSON 的原生操作，字段类型添加了一个 JSON 类型，可对存入的 JSON 数据进行增删改查操作。

创建表

mysql> CREATE TABLE `test_json` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `json` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

插入整个 JSON 文档

mysql> INSERT INTO `test_json` (`json`) VALUES ("{"name": "Tom", "age": 18}");

结果：

+----+------------------------------+
| id | json                         |
+----+------------------------------+
|  1 | {"age": 18, "name": "Tom"}   |
+----+------------------------------+

新增 JSON 字段

mysql> UPDATE `test_json` SET `json` = JSON_INSERT(`json`, "$.sex", "M");

结果：

+----+------------------------------------------+
| id | json                                     |
+----+------------------------------------------+
|  1 | {"age": 18, "sex": "M", "name": "Tom"}   |
+----+------------------------------------------+

删除 JSON 字段

mysql> UPDATE `test_json` SET `json` = JSON_REMOVE(`json`, "$.sex");

结果：

+----+------------------------------+
| id | json                         |
+----+------------------------------+
|  1 | {"age": 18, "name": "Tom"}   |
+----+------------------------------+

修改 JSON 字段

mysql> UPDATE `test_json` SET `json` = JSON_REPLACE(`json`, "$.name", "Timmy");

结果：

+----+------------------------------+
| id | json                         |
+----+------------------------------+
|  1 | {"age": 18, "name": "Timmy"} |
+----+------------------------------+

根据 JSON 字段查询

mysql> SELECT * FROM `test_json` WHERE `json`->"$.name" = "Timmy";
+----+------------------------------+
| id | json                         |
+----+------------------------------+
|  1 | {"age": 18, "name": "Timmy"} |
+----+------------------------------+
1 row in set

解析查询出来的 JSON 数据

mysql> SELECT
    JSON_EXTRACT(`json`, "$.name") AS `name`,
    JSON_EXTRACT(`json`, "$.age") AS `age`
FROM `test_json`;
+---------+-----+
| name    | age |
+---------+-----+
| "Timmy" | 18  |
+---------+-----+
1 row in set

查询 JSON 字段的所有键

mysql> SELECT JSON_KEYS(`json`) FROM `test_json`;
+-------------------+
| JSON_KEYS(`json`) |
+-------------------+
| ["age", "name"]   |
+-------------------+
1 row in set

原文链接：http://www.fullstack.pub/doc/25