MYSQL JSON型的使用(增删改查)

支持的版本：MySQL5.7以上

表生成

CREATE TABLE `json_users` (`col` JSON);

插入数据

INSERT INTO `json_users`
VALUES
('{"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}'),
('{"name": "yamada", "gender": 2, "options": {"x": 300}}'),
('{"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}');

查询

SELECT * FROM `json_users`;

 

抽出指定数据

object的情况 ->"$.key" 的形式抽出
list的情况 ->"$[]" 的形式抽出

SELECT `col`->"$.name" FROM `json_users`;

结果含有双引号

如果想要得到没有双引号的结果则用JSON_UNQUOTE函数

SELECT JSON_UNQUOTE(`col`->"$.name") FROM `json_users`;

 

WHERE语句的使用

SELECT * FROM `json_users` WHERE `col`->"$.options.x" = 100;

GROUP BY语句的使用 

算出各个性别的人数

SELECT `col`->"$.gender" as gender, COUNT(*) FROM `json_users` GROUP BY gender;

 生成JSON数据的函数

JSON_ARRAY

( 生成 JSONlist)

格式：JSON_ARRAY([val[, val] ...])

例子：SELECT JSON_ARRAY(1, 2, 100, 'tanaka', Null);

JSON_OBJECT

格式：JSON_OBJECT([key, val[, key, val] ...])

例子：SELECT JSON_OBJECT('name', 'tanaka', 'options', JSON_OBJECT('x', 100, 'y', 200));

 JSON数据检索函数

JSON_CONTAINS
( 判断是否包含指定值 )

格式：JSON_CONTAINS(target, candidate[, path])

例子：SELECT `col`, JSON_CONTAINS(`col`, '2', '$.gender') FROM `json_users`;

找到返回1

JSON_CONTAINS_PATH
( 确定是否包含指定路径 )

格式：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

例1包含其中一个：SELECT `col`, JSON_CONTAINS_PATH(`col`, 'one', '$.options.y', '$.options.z') FROM `json_users`;

找到返回1

 例2全匹配：SELECT `col`, JSON_CONTAINS_PATH(`col`, 'all', '$.options.y', '$.options.z') FROM `json_users`;

找到返回1

JSON_EXTRACT
( 抽出指定路径的数据 )

格式：JSON_EXTRACT(json_doc, path[, path] ...)

例返回第一个匹配的路径：SELECT `col`, JSON_EXTRACT(`col`, '$.name') FROM `json_users`;

例2返回所有匹配的路径：SELECT `col`, JSON_EXTRACT(`col`, '$.options.y') FROM `json_users`;

 

运算符 

 例子：SELECT `col`, `col`->"$.name" FROM `json_users`;

JSON_KEYS
( 只抽出key值 )

格式：JSON_KEYS(json_doc[, path])

例子：SELECT `col`, JSON_KEYS(`col`) FROM `json_users`;

 

JSON_SEARCH
( 搜索含有指定值的路径 )

以路径为参数的JSONSET和JSON也可以和REMOVE等一起活用。

格式：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

例1返回第一个匹配的路径：SELECT `col`, JSON_SEARCH(`col`, 'one', "yama%") FROM `json_users`;

例子2返回所有匹配的路径：SELECT `col`, JSON_SEARCH(`col`, 'all', "yama%") FROM `json_users`;

 

 *注意：无法查找数值

 如果是字符串数组，则可以查找，但如果是数字数组则无法查找。

SELECT JSON_SEARCH('["1", "2", "3"]', 'one', '2') 

 

SELECT JSON_SEARCH('[1, 2, 3]', 'one', 2);