【MySQL】JSON 数据类型

从 MySQL5.7.8 开始，支持字段使用 JSON 数据类型
JSON 数据类型（官网）
JSON 函数目录（官网）

开始

创建表和字段

CREATE TABLE t1 (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `data` JSON,
  PRIMARY KEY (`id`)
);

JSON 类型支持“JSON对象”和“JSON数组”👇

JSON 对象：{"k1": "value", "k2": 10}
JSON 数组：["abc", 10, null, true, false]

JSON 数组和 JSON 对象允许互相嵌套👇

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

插入数据示例👇
INSERT INTO t1(data) VALUES('{"key1": "value1", "key2": "value2"}');
INSERT INTO t1(data) VALUES('["a", 1, "2015-07-27 09:43:47.000000"] ');

JSON 赋值给变量

JSON 值可以赋值给变量，但不是 JSON 类型，而是转换为字符串SET @j = JSON_OBJECT('key', 'value');
转换的字符串具有字符集“utf8mb4”，排序规则“utf8mb4_bin”。执行SELECT CHARSET(@j),COLLATION(@j)可查看
utf8mb4_bin 是二进制排序规则，所以 JSON 值区分大小写。null、true、false在 JSON 中必须是小写字母

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null');
+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') |
+--------------------+--------------------+
|                  1 |                  0 |
+--------------------+--------------------+

创建 JSON

JSON_ARRAY([val[, val] ...]) 创建 JSON 数据👇

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
+---------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
+---------------------------------------------+
| [1, "abc", null, true, "10:47:25.000000"]   |
+---------------------------------------------+

JSON_OBJECT([key, val[, key, val] ...]) 创建 JSON 对象👇

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+

JSON_QUOTE(string) 生成 JSON 字符串文字👇

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"'), JSON_QUOTE('[1, 2, 3]'); 
+--------------------+----------------------+-------------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') | JSON_QUOTE('[1, 2, 3]') |
+--------------------+----------------------+-------------------------+
| "null"             | "\"null\""           | "[1, 2, 3]"             |
+--------------------+----------------------+-------------------------+

合并 JSON

JSON_MERGE(json_doc, json_doc[, json_doc] ...)👇

-- 合并多个数组
mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false]                       |
+-----------------------------------------------------+

-- 合并多个对象
mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3}                      |
+----------------------------------------------------+

-- 合并多个值
mysql> SELECT JSON_MERGE('1', '2');
+----------------------+
| JSON_MERGE('1', '2') |
+----------------------+
| [1, 2]               |
+----------------------+

-- 合并数组和对象
mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"a": "x", "b": "y"}]                 |
+------------------------------------------------+

若 JSON 数据中的 key 相同，也会合并数据

mysql> INSERT INTO t1(`data`) VALUES
>     ('{"x": 17, "x": "red"}'),
>     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+-----------+
| c1        |
+-----------+
| {"x": 17} |
| {"x": 17} |
+-----------+

搜索 JSON

JSON_EXTRACT(json_doc, path[, path] ...)👇

-- 根据索引，查找 value
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 20                                         |
+--------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
+----------------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
+----------------------------------------------------+
| [20, 10]                                           |
+----------------------------------------------------+
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
+-----------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
+-----------------------------------------------+
| [30, 40]                                      |
+-----------------------------------------------+

-- 根据 key，查找 value
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

JSON_KEYS(json_doc[, path])👇

-- 未指定 path，默认返回第一层的 keys
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+

-- 指定 path，返回指定节点下的 keys
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) 返回 JSON 文档中匹配字符串的路径👇

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

-- one 表示只匹配第一个
mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+-------------------------------+
| "$[0]"                        |
+-------------------------------+

-- all 表示匹配所有
mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
+-------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

-- search_str 可以只用 "%" 和 "_" 通配符
mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
+-------------------------------+
| JSON_SEARCH(@j, 'all', '%a%') |
+-------------------------------+
| ["$[0]", "$[2].x"]            |
+-------------------------------+

-- escape_char：指定转义字符，必须是常量（为空或一个字符）。参数值为 NULL 或 不存在时，默认 "\"
-- 按照格式，必须要指定转义字符，才可以使用 path，path 即指定匹配的路径
mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
+-------------------------------------------+
| JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
+-------------------------------------------+
| "$[3].y"                                  |
+-------------------------------------------+

插入/修改/删除 JSON

JSON_INSERT(json_doc, path, val[, path, val] ...)👇

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+

-- 上方的"[true, false]"为字符串，若要转为 JSON，需要用到 CAST 函数
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
+------------------------------------------------------------------+
| JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) |
+------------------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": [true, false]}                        |
+------------------------------------------------------------------+

JSON_REPLACE(json_doc, path, val[, path, val] ...) 替换JSON文档中的现有值并返回结果👇

-- 文档中存在则替换，不存在忽略
mysql> SELECT JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+---------------------------------------------------------------------------+
| JSON_REPLACE('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+---------------------------------------------------------------------------+
| {"a": 10, "b": [2, 3]}                                                    |
+---------------------------------------------------------------------------+

JSON_SET(json_doc, path, val[, path, val] ...) 在JSON文档中插入或更新数据并返回结果👇

-- JSON_SET = JSON_INSERT + JSON_REPLACE
mysql> SELECT JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------------------------+
| JSON_SET('{ "a": 1, "b": [2, 3]}', '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}                          |
+-----------------------------------------------------------------------+

JSON_REMOVE(json_doc, path[, path] ...) 删除并返回结果👇

mysql> SELECT JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1]');
+-----------------------------------------------+
| JSON_REMOVE('["a", ["b", "c"], "d"]', '$[1]') |
+-----------------------------------------------+
| ["a", "d"]                                    |
+-----------------------------------------------+

其他 JSON 函数

JSON_TYPE(json_val) 判断值的类型👇

mysql> SELECT JSON_TYPE('["a", "b", 1]'), 
    -> JSON_TYPE('{"key1": "value1", "key2": "value2"}'),
    -> JSON_TYPE('"hello"');
+----------------------------+---------------------------------------------------+----------------------+
| JSON_TYPE('["a", "b", 1]') | JSON_TYPE('{"key1": "value1", "key2": "value2"}') | JSON_TYPE('"hello"') |
+----------------------------+---------------------------------------------------+----------------------+
| ARRAY                      | OBJECT                                            | STRING               |
+----------------------------+---------------------------------------------------+----------------------+

JSON_LENGTH(json_doc[, path]) 返回 JSON 文档的长度👇

-- 未指定 path，默认返回第一层的长度
mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
|                               3 |
+---------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+

-- 指定 path，返回指定节点下的长度
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

JSON_VALID(val) 校验JSON值是否有效👇

mysql> SELECT JSON_VALID('{"a": 1}'), JSON_VALID('hello'), JSON_VALID('"hello"');
+------------------------+---------------------+-----------------------+
| JSON_VALID('{"a": 1}') | JSON_VALID('hello') | JSON_VALID('"hello"') |
+------------------------+---------------------+-----------------------+
|                      1 |                   0 |                     1 |
+------------------------+---------------------+-----------------------+

JSON 新运算符

插入数据INSERT INTO t1(data) VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
可以根据 key 查找匹配项SELECT data->"$.mascot" FROM t1，用到了"->"列路径运算符

-- 这种写法会与每条数据都匹配查询一次，未找到的返回 null
mysql> SELECT data->"$.mascot" FROM t1;
+---------------------------------------------+
| data->"$.mascot"                            |
+---------------------------------------------+
| NULL                                        |
| NULL                                        |
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+

-- 使用子查询解决上面问题，必须要把子查询命名为派生表【也可以使用 JSON_EXTRACT 函数】
mysql> SELECT data->"$.mascot" FROM (SELECT data FROM t1 WHERE `id` = 3) AS tt;
+---------------------------------------------+
| data->"$.mascot"                            |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+

使用"->>"内联路径运算符

mysql> SELECT data->>"$.mascot" FROM t1;
+-----------------------------------------+
| data->>"$.mascot"                       |
+-----------------------------------------+
| NULL                                    |
| NULL                                    |
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

posted @ 2023-02-18 20:35 洗衣机o 阅读(185) 评论(0) 收藏举报

刷新页面返回顶部

🏠

【一名Java开发】 master👉https://xuyj1111.github.io/