mysql8.0有JSON类型吗 mysql8 json类型

如何创建JSON类型?

字符串表示

在MySQL中可以使用字符串表示JSON类型，合法的JSON字符串可以自动解析为JSON对象。可以使用JSON_VALID()来验证是否合法, 用JSON_TYPE来判断对象类型。

SET @j = '["abc", 10, null, true, false]';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1, ARRAY

SET @j = '{"k1": "value", "k2": 10}';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1, OBJECT

select JSON_VALID('"k2": 10')
-- 0

SET @j = '10';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1,INTEGER

SET @j = '"STR"';
select JSON_VALID(@j), JSON_TYPE(@j);
-- 1,STRING

select JSON_VALID('STR')
-- 0

注意STRING类型的值必须是在双引号内, 如果字符类型的值里面还包含双引号，可以转义或者使用JSON_QUOTE() 函数：

select '"Hello \\"Tom\\""', JSON_QUOTE('Hello "Tom"')
-- "Hello \"Tom\"", "Hello \"Tom\""

JSON_OBJECT([key, val[, key, val] …])

select JSON_OBJECT('name','test','age',12)
-- {"age": 12, "name": "test"}

SELECT JSON_OBJECT();
-- {}

注意JSON对象的Key必须是字符串，如果为JSON_OBJECT提供了非字符串的key，会转成STRING:

select JSON_OBJECT('name','test',45,12)
-- {"45": 12, "name": "test"}

select JSON_OBJECT("name","test",now(),12);
-- {"name": "test", "2022-04-19 13:24:23": 12}

select  JSON_OBJECT("name","test",JSON_OBJECT('address','shanghai'),12);
-- {"name": "test", "{\"address\": \"shanghai\"}": 12}

JSON_ARRAY([val[, val] …])

SELECT JSON_ARRAY(1, 'abc','"ASD"',NULL, TRUE,FALSE, CURTIME());
-- [1, "abc", "\"ASD\"", null, true, false, "13:31:14.000000"]

SELECT JSON_ARRAY();
-- []

JSON_QUOTE(string)

用来获取STRING类型的值

select JSON_QUOTE('[1, 2, 3]'),JSON_QUOTE('hi');
-- "[1, 2, 3]", "hi"

CAST(value AS JSON)

select CAST('12' AS JSON),CAST('"12"' AS JSON),CAST(now() as JSON)
-- 12,"12","2022-04-19 14:00:56.000000"

聚合函数JSON_ARRAYAGG和JSON_OBJECTAGG

select JSON_ARRAYAGG('123'),JSON_OBJECTAGG('name','sdf')
-- ["123"],{"name": "sdf"}

JSON_ARRAYAGG和JSON_OBJECTAGG更强大的功能体现在聚合一组表数据上，比如官网的例子：

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
     > FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

如何访问JSON类型?

查询JSON类型时，会转成utf8mb4字符集（校对规则utf8mb4_bin）的字符串

set @j = JSON_OBJECT('name','test','age',12);
select @j, CHARSET(@j), COLLATION(@j);
-- {"age": 12, "name": "test"},utf8mb4,utf8mb4_bin

JSON_EXTRACT

可以使用-> 操作或者 ->>操作来访问，->>比->多了一步JSON_UNQUOTE

create table t_json(
	jdoc json
);
insert into t_json(jdoc) values(JSON_OBJECT('name','hello'));
select jdoc -> '$.name',jdoc->>'$.name' from t_json ;
-- "hello",hello

-> 等价于JSON_EXTRACT而->>等价于JSON_UNQUOTE(JSON_EXTRACT()), 但是函数可以用来获取多个值：

set @j = JSON_OBJECT('name','test','age',12);
select JSON_EXTRACT(@j,'$.name') from t_json;
-- "test"
select JSON_EXTRACT(@j,'$.name','$.age') from t_json;
-- ["test", 12]

set @j = JSON_ARRAY('12',12,'SDF');
select JSON_EXTRACT(@j,'$[0]') from t_json;
-- "12"
select JSON_EXTRACT(@j,'$[1]','$[0]') from t_json;
-- [12, "12"]

对数组来说，有个比较有意思的属性last,最后元素的索引：

set @j = JSON_ARRAY('12',12,'SDF');
select JSON_EXTRACT(@j,'$[last]') from t_json;
-- "SDF"
select JSON_EXTRACT(@j,'$[last-1]','$[last-2]') from t_json;
-- [12, "12"]

JSON_VALUE

与JSON_EXTRACT不同的是，JSON_EXTRACT获取到的是值还是一个JSON类型但是JSON_VALUE还会将值转成相应数据库类型：

set @j = JSON_ARRAY('12',12,'SDF');
select JSON_VALUE(@j,'$[last]'),JSON_EXTRACT(@j,'$[last]') from t_json;
-- SDF,"SDF"
select JSON_VALUE(@j,'$[0]'),JSON_EXTRACT(@j,'$[0]') from t_json;
-- 12,"12"
select JSON_VALUE(@j,'$[1]'),JSON_EXTRACT(@j,'$[1]') from t_json;
-- 12,12

JSON_SEARCH

MySQL提供很多的函数以各种访问JSON类型，JSON_SEARCH在其中也非常重要的，可以用来获取访问路径。他有两种模式one获取匹配的第一个路径，all获取所有匹配到的路径：

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'one', 'abc');
-- "$[0]"
SELECT JSON_SEARCH(@j, 'all', 'abc');
-- ["$[0]", "$[2].x"]

也可以限定搜索范围：

SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
-- "$[2].x"

还可以模糊匹配：

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j, 'all', '%bc%');
-- ["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j, 'all', '%bc_');
-- "$[3].y"

如何更新JSON类型的值？

JSON_SET()

修改已存在的或者添加新的：

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- ["a", {"b": [1, false]}, [10, 20, 2]]

需要注意一下异常情况，比如不存在c这个数组元素，后面的数组索引4不存在：

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].c[0]', 1, '$[2][4]', 2);
-- ["a", {"b": [true, false]}, [10, 20, 2]]
-- 可以看到c[0]没有更新，数组元素2加到了最后

但是可以添加新的key，也可以添加数组元素，比如：

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].c', 'hello', '$[1].d', JSON_ARRAY(3));
-- ["a", {"b": [true, false], "c": "hello", "d": [3]}, [10, 20]]

JSON_INSERT()

只会添加新的值，不会修改现有元素：

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

JSON_REPLACE()

只会修改现有元素，忽略新增的值或元素：

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2, '$[0]', JSON_ARRAY(1,2));
-- [[1, 2], {"b": [1, false]}, [10, 20]]

JSON_REMOVE()

SET @j = '["a", {"b": [1, 2,3]}, [10, 20]]';
SELECT JSON_REMOVE(@j, '$[2][3]', '$[1].b[0]', '$[1].b[0]');
-- ["a", {"b": [3]}, [10, 20]]

这里注意b中的元素被移除了两个

关于JSON类型的in-place 更新

JSON类型的部分元素更新，满足以下条件，优化器可以使用in-place更新，而不是删除旧文件并将新文件完整地写入：

• 列是JSON
• UPDATE 语句使用JSON_SET()、 JSON_REPLACE()或 JSON_REMOVE()进行更新
• 修改的目标列和函数的输入列是同一列
• 必须是修改现有值或元素，不能添加新的
• 新值的所占大小不能超过旧的值

如何对JSON类型使用索引?

JSON列，和其他二进制类型的列一样，不能直接建立索引。
但是可以通过Generated Column对JSON中的元素建立索引：

CREATE TABLE t_json (
	jdoc JSON,
	g varchar(30) GENERATED ALWAYS AS (jdoc->>"$.name"),
 	INDEX i (g)
);

insert into t_json(jdoc)
values('{"name":"abc","age":1}'),
('{"name":"bcd","age":2}'),
('{"name":"def","age":3}'),
('{"name":"efg","age":4}'),
('{"name":"hij","age":5}');

SELECT * FROM t_json where g = 'abc';

查询可以使用索引。这里需要注意抽取值的操作，字符串最好使用->>, 而不是->。