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;
mysql> INSERT INTO `test_json` (`json`) VALUES ('{"name": "Tom", "age": 18}');
结果:
+----+------------------------------+
| id | json |
+----+------------------------------+
| 1 | {"age": 18, "name": "Tom"} |
+----+------------------------------+
mysql> UPDATE `test_json` SET `json` = JSON_INSERT(`json`, '$.sex', 'M');
结果:
+----+------------------------------------------+
| id | json |
+----+------------------------------------------+
| 1 | {"age": 18, "sex": "M", "name": "Tom"} |
+----+------------------------------------------+
mysql> UPDATE `test_json` SET `json` = JSON_REMOVE(`json`, '$.sex');
结果:
+----+------------------------------+
| id | json |
+----+------------------------------+
| 1 | {"age": 18, "name": "Tom"} |
+----+------------------------------+
mysql> UPDATE `test_json` SET `json` = JSON_REPLACE(`json`, '$.name', 'Timmy');
结果:
+----+------------------------------+
| id | json |
+----+------------------------------+
| 1 | {"age": 18, "name": "Timmy"} |
+----+------------------------------+
mysql> SELECT * FROM `test_json` WHERE `json`->'$.name' = 'Timmy';
+----+------------------------------+
| id | json |
+----+------------------------------+
| 1 | {"age": 18, "name": "Timmy"} |
+----+------------------------------+
1 row in set
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
mysql> SELECT JSON_KEYS(`json`) FROM `test_json`;
+-------------------+
| JSON_KEYS(`json`) |
+-------------------+
| ["age", "name"] |
+-------------------+
1 row in set