json在mysql是string类型_MySQL JSON类型

JSON值的局部更新

在MySQL8.0中,优化器可以执行JSON列的局部就地更新,而不用删除旧文档再将整个新文档写入该列。局部更新的条件:

正在更新的列被声明为JSON;

该UPDATE语句使用任一的三个函数 JSON_SET(), JSON_REPLACE()或 JSON_REMOVE()更新列;

输入列和目标列必须是同一列;

所有更改都使用新值替换现有数组或对象值,并且不向父对象或数组添加任何新元素;

新值不能大于旧值;

创建JSON值

JSON数组包含在 字符[和]字符中,其中为一个由逗号分隔的值列表:

["abc", 10, null, true, false]

JSON对象包含在字符{和}字符中,其中为一组由逗号分隔的键值对,键必须是字符串:

{"k1": "value", "k2": 10}

在JSON数组和JSON对象的值中允许嵌套:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]

{"k1": "value", "k2":[10, 20]}

下例中向创建一个只有一个JSON列的表格t_json,并向其中添加JSON值:

mysql> CREATE TABLE t_json (jdoc JSON) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK,0 rows affected, 1 warning (0.73sec)

mysql> INSERT INTO t_json VALUES('[1,2]');

Query OK,1 row affected (0.17sec

mysql> INSERT INTO t_json VALUES('{"key1":"value1","key2":"value2"}');

Query OK,1 row affected (0.27sec)

mysql> INSERT INTO t_json VALUES('"HELLO"');

Query OK,1 row affected (0.20 sec)

若添加的值为非JSON格式,则报错:

mysql> INSERT INTO t_json VALUES("HELLO");

ERROR3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 't_json.jdoc'.

查看t_json:

mysql> SELECT * FROMt_json;+--------------------------------------+

| jdoc |

+--------------------------------------+

| [1, 2] |

| {"key1": "value1", "key2": "value2"} |

| "HELLO" |

+--------------------------------------+

3 rows in set (0.00 sec)

JSON_TYPE()函数尝试将传入的值其解析为JSON值。如果值有效,则返回值的JSON类型,否则产生错误:

mysql> SELECT JSON_TYPE('["a","b",true,13]');+--------------------------------+

| JSON_TYPE('["a","b",true,13]') |

+--------------------------------+

| ARRAY |

+--------------------------------+

1 row in set (0.04sec)

mysql> SELECT JSON_TYPE('[a,"b",true,13]'); //注意 a

ERROR3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 1.

JSON_ARRAY()接收传入的值列表(可以为空),返回包含这些值的JSON数组:

mysql> SELECT JSON_ARRAY('ab',false,13);+---------------------------+

| JSON_ARRAY('ab',false,13) |

+---------------------------+

| ["ab", false, 13] |

+---------------------------+

1 row in set (0.00sec)

mysql> SELECTJSON_ARRAY();+--------------+

| JSON_ARRAY() |

+--------------+

| [] |

+--------------+

1 row in set (0.00 sec)

JSON_OBJECT() 接收传入的键值对列表(可以为空),并返回包含这些键值对的JSON对象:

mysql> SELECT JSON_OBJECT('key1','a','key2','b');+------------------------------------+

| JSON_OBJECT('key1','a','key2','b') |

+------------------------------------+

| {"key1": "a", "key2": "b"} |

+------------------------------------+

1 row in set (0.03 sec)

如果传入的参数不能组成键值对,则报错:

mysql> SELECT JSON_OBJECT('key1','value1','key2');

ERROR1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'

JSON_MERGE_PRESERVE() 获取两个或多个JSON文档并返回组合结果:

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');+-----------------------------------------------------+

| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |

+-----------------------------------------------------+

| ["a", 1, {"key": "value"}] |

+-----------------------------------------------------+

1 row in set (0.03 sec)

因此我们也可以使用以上三种方法向表中添加JSON值,可以一定程度地避免输入格式错误:

mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array'));

Query OK,1 row affected (0.19sec)

mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello'));

Query OK,1 row affected (0.09sec)

mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2)));

Query OK,1 row affected (0.14sec)

mysql> SELECT * FROMt_json;+--------------------------------------+

| jdoc |

+--------------------------------------+

| [1, 2] |

| {"key1": "value1", "key2": "value2"} |

| "HELLO" |

| ["json_array"] |

| {"key": "hello"} |

| [{"key": "hello"}, 1, 2] |

+--------------------------------------+

6 rows in set (0.00 sec)

JSON值的规范化,合并和自动包装

解析字符串并发现字符串是有效的JSON文档时,它在被解析时也会被规范化。对于重复的键(key),后面的值(value)会覆盖前面的值。如下:

mysql> SELECT JSON_OBJECT('x',1,'y',2,'x','a','x','b');+------------------------------------------+

| JSON_OBJECT('x',1,'y',2,'x','a','x','b') |

+------------------------------------------+

| {"x": "b", "y": 2} |

+------------------------------------------+

1 row in set (0.07 sec)

这种“覆盖”在向JSON列添加值时也会发生。

在MySQL8.0.3之前的版本中,与此相反,对于被重复的键,它的第一个值会被保留,后添加的值则会被抛弃。

合并JSON值

MySQL8.0.3及更高版本中,有两种合并函数:JSON_MERGE_PRESERVE()和 JSON_MERGE_PATCH()。下面具讨论它们的区别。

合并数组:

mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]');+-------------------------------------------------------------------------+

| JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]') |

+-------------------------------------------------------------------------+

| [true, false] |

+-------------------------------------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]');+----------------------------------------------------------------------------+

| JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]') |

+----------------------------------------------------------------------------+

| [1, 2, "a", "b", "c", 1, 2, true, false] |

+----------------------------------------------------------------------------+

1 row in set (0.00 sec)

合并数组时,JSON_MERGE_PRESERVE只保留最后传入的数组参数,而JSON_MERGE_PRESERVE则按传入顺序将数组参数连接。

合并对象

mysql> SELECT JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}');+------------------------------------------------------------------------------+

| JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') |

+------------------------------------------------------------------------------+

| {"a": 4, "b": 2, "c": 5, "d": 3} |

+------------------------------------------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_MERGE_PRESERVE('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}');+---------------------------------------------------------------------------------+

| JSON_MERGE_PRESERVE('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') |

+---------------------------------------------------------------------------------+

| {"a": [3, 4], "b": 2, "c": [3, 5], "d": 3} |

+---------------------------------------------------------------------------------+

1 row in set (0.00 sec)

合并对象时,对于重复键,JSON_MERGE_PRESERVE只保留最后传入的键值,而JSON_MERGE_PRESERVE重复键的所有值保留为数组。

搜索和修改JSON值

在了解搜索和修改JSON值之前,先来看看JSON的路径语法。

路径语法

.keyName:JSON对象中键名为keyName的值;

对于不合法的键名(如有空格),在路径引用中必须用双引号"将键名括起来,例,."key name";

[index]:JSON数组中索引为index的值,JSON数组的索引同样从0开始;

[index1 to index2]:JSON数组中从index1到index2的值的集合;

.*: JSON对象中的所有value;

[*]: JSON数组中的所有值;

prefix**suffix: 以prefix开头并以suffix结尾的路径;

**.keyName为多个路径,如对于JSON对象'{"a": {"b": 1}, "c": {"b": 2}}','$**.b'指路径$.a.b和$.c.b;

不存在的路径返回结果为NULL;

前导$字符表示当前正在使用的JSON文档

例子:对于数组[3, {"a": [5, 6], "b": 10}, [99, 100]]

$[1]为{"a": [5, 6], "b": 10}。

[1].a为[5, 6]。

$[1].a[1]为 6。

$[1].b为 10。

$[2][0]为 99。

搜索

JSON_EXTRACT提取JSON值,直接看例子:

JSON对象

mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name');+--------------------------------------------------------+

| JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name') |

+--------------------------------------------------------+

| "Taylor" |

+--------------------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*');+-----------------------------------------------------+

| JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*') |

+-----------------------------------------------------+

| [29, "Taylor"] |

+-----------------------------------------------------+

1 row in set (0.00 sec)

嵌套查询:

SELECT JSON_EXTRACT(JSON_EXTRACT('{"char1": {"total": 4, "consumed": 1}, "char2": {"total": 8, "consumed": 1}}', '$.char1'), '$.total');

4

JSON数组

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

| JSON_EXTRACT('["a", "b", "c"]', '$[1]') |

+-----------------------------------------+

| "b" |

+-----------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]');+----------------------------------------------+

| JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]') |

+----------------------------------------------+

| ["b", "c"] |

+----------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]');+-----------------------------------------+

| JSON_EXTRACT('["a", "b", "c"]', '$[*]') |

+-----------------------------------------+

| ["a", "b", "c"] |

+-----------------------------------------+

1 row in set (0.00 sec)

修改

JSON_REPLACE 替换值(只替换已经存在的旧值)

JSON_SET 设置值(替换旧值,并插入不存在的新值)

JSON_INSERT 插入值(插入新值,但不替换已经存在的旧值)

JSON_REMOVE 删除JSON数据,删除指定值后的JSON文档

JSON_REPLACE与JSON_SET的区别:

//旧值存在

mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere');+----------------------------------------------------------------+

| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |

+----------------------------------------------------------------+

| {"id": 29, "name": "Mere"} |

+----------------------------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere");+------------------------------------------------------------+

| JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |

+------------------------------------------------------------+

| {"id": 29, "name": "Mere"} |

+------------------------------------------------------------+

1 row in set (0.00sec)//旧值不存在

mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');+---------------------------------------------------------------+

| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |

+---------------------------------------------------------------+

| {"id": 29, "name": "Taylor"} |

+---------------------------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');+-----------------------------------------------------------+

| JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |

+-----------------------------------------------------------+

| {"id": 29, "cat": "Mere", "name": "Taylor"} |

+-----------------------------------------------------------+

1 row in set (0.00 sec)

JSON_INSERT和JSON_SET:

//旧值存在

mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4);+-------------------------------------+

| JSON_INSERT('[1, 2, 3]', '$[1]', 4) |

+-------------------------------------+

| [1, 2, 3] |

+-------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4);+----------------------------------+

| JSON_SET('[1, 2, 3]', '$[1]', 4) |

+----------------------------------+

| [1, 4, 3] |

+----------------------------------+

1 row in set (0.00sec)//旧值不存在

mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4);+-------------------------------------+

| JSON_INSERT('[1, 2, 3]', '$[4]', 4) |

+-------------------------------------+

| [1, 2, 3, 4] |

+-------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4);+----------------------------------+

| JSON_SET('[1, 2, 3]', '$[4]', 4) |

+----------------------------------+

| [1, 2, 3, 4] |

+----------------------------------+

1 row in set (0.00 sec)

JSON_REMOVE:

mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[1]');+----------------------------------+

| JSON_REMOVE('[1, 2, 3]', '$[1]') |

+----------------------------------+

| [1, 3] |

+----------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[4]');+----------------------------------+

| JSON_REMOVE('[1, 2, 3]', '$[4]') |

+----------------------------------+

| [1, 2, 3] |

+----------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name');+-------------------------------------------------------+

| JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name') |

+-------------------------------------------------------+

| {"id": 29} |

+-------------------------------------------------------+

1 row in set (0.00 sec)

JSON值的比较和排序

可以使用=,,>=,<>,!=,和 <=>对JSON值进行比较。

JSON值的比较先比较值的类型。如果类型不同,则直接 返回类型的优先级的比较结果;如果类型相同,再进行值的内容的比较。

JSON中值的类型的优先级从高到低为:

BLOBBITOPAQUEDATETIMETIME

DATE

BOOLEAN

ARRAY

OBJECT

STRINGINTEGER, DOUBLE

NULL

OPAQUE值是不属于其他类型的值。

JSON值的内容的比较规则(因类型不同而有差别):

BLOB

比较两个值的前N个字节,其中N为较短的值的字节数。如果前N个字节相同,则较短的值较小。BIT和OPAQUE与BLOB的规则相同。

DATETIME

较早时间点的值较小。如果两个值分别为 MySQL DATETIME and TIMESTAMP类型且表示的是相同的时间点,则这两个值相等。

TIME

较少的是时间值较小。

DATE

较早的日期值较小。

ARRAY

较短的数组较小。

如果两个数组长度相同,且相同索引处的值相同,则两个数组相等。

对于不行等的数组,它们的大小顺序由两数组中第一个不同的元素决定。

例子:

[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]

BOOLEAN

false 

OBJECT

具有完全相同的键值对的两对象相等。如

{"a": 1, "b": 2} = {"b": 2, "a": 1}

STRING

与BLOB比较规则相似。区分大小写。

如:

"A"

INTEGER, DOUBLE

如果进行INTEGER列和DOUBLE列的比较,则integer数会被转为double数,即精确值转为近似值,再进行比较;

如果查询比较包含数字的两个JSON列,则无法预先知道数字是INTEGER还是DOUBLE,比较时会将近似值转为精确值,在进行比较。

INTEGER比较

9223372036854775805 < 9223372036854775806 < 9223372036854775807

DOUBLE比较

9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

任何JSON值与NULL比较的结果为UNKOWN

JSON值与非JSON值比较时,非JSON值会被转为JSON值

JSON值和非JSON值转换

转换规则为:

other typeCAST(other type AS JSON)CAST(JSON AS other type)

JSON

没变化

没变化

utf8字符类型(utf8mb4,utf8,ascii)

字符串被解析为JSON值

JSON值被序列化为utf8mb4字符串

其他字符类型

其他字符编码被隐式转换为utf8mb4,并按utf8字符类型进行处理

JSON值被序列化为utf8mb4字符串,然后再被转换为其他字符编码。结果可能没有意义。

NULL

结果为JSON类型的NULL值

不适用

Geometry类型

ST_AsGeoJSON()将Geometry值转换为JSON文档

非法操作。解决办法: 将CAST(JSON AS other type)的结果传递给CHAR)ST_GeomFromGeoJSON()

所有其他类型

转换结果是由单个标量值组成的JSON文档

如果JSON文档由目标类型的单个标量值组成,并且标量值可以强制转换为目标类型,则成功转换。否则,返回NULL 并发出警告。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值