1关于作者
王伟,京东商城资深MySQL DBA,多年游戏、电商行业MySQL运维经验,致力于MySQL自动化、自助化运维,专注mysql数据库架构、调优、运维以及zabbix监控等技术。
文章结构 MySQL 5.7 json部分内容较大。 给拆分成三篇来发: 第一篇: 介绍及json读取 第二篇: json写操作 第三篇: 索引以及generated字段+总结
2第一标题
1、JSON_ARRAY_APPEND数组类的追加
这个用法主要是处理JSON字段中值数组型的数据,主要是追加值,不是标准数组类型的,这个函数会自动转化为数组形式,把之前的值作为数组的第一个元素然后进行追加,有批量追加方法:
mysql> SET @j = '["a", ["b", "c"], "d"]';
在字段中第二个数值的数组中追加一个元素1:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"] |
+----------------------------------+
在字段中第一个数值的数组中追加一个元素2,如果不是数组形式的单个值会改变其为数组并追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"] |
+----------------------------------+
在字段中第二个数值的第一个数组中追加一个元素3,如果不是数组形式的单个值会改变其为数组并追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"] |
+-------------------------------------+
在字段中key为”b”的value数组中追加一个元素”x”:
mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
+------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.b', 'x') |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
在字段中key为”c”的value数组中追加一个元素”y”:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.c', 'y') |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+
如果JSON字段为标准的JSON类型,使用这个函数则会把其变化数组形式,并追加值:
mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z"] |
+---------------------------------+
如果JSON字段为标准的JSON类型,批量追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z','$', 'w');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z", "w"] |
+---------------------------------+
2、JSON_ARRAY_INSERT数组类的插入
这个用法主要是处理JSON字段中值数组型的数据,主要是插入,不是标准数组类型的,这个函数会自动转化为数组形式,把之前的值作为数组的第一个元素然后进行追加:
mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
在数组第1个值后面插入字符’x’ 元素:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]] |
在数组第1个值前面插入字符’x’ 元素:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["x","a", {"b": [1, 2]}, [3, 4]] |
+------------------------------------+
在数组第100个值后面插入字符’x’ 元素,如果超长,则在最后一个值后面插入:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"] |
+--------------------------------------+
在数组第1个值中key为’b’的value数组第1个值前面入字符元素’x’:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]] |
+-----------------------------------------+
在数组第2个值后面数组第1个值后面入字符元素’y’:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]] |
+---------------------------------------+
数组元素的批量插入:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]] |
+----------------------------------------------------+
3、JSON_INSERT标准JSON值的插入
如果这个key已经存在,则不会去覆盖旧值,不存在的插入:
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]"} |
+----------------------------------------------------+
4、JSON_MERGE合并操作
JSON_MERGE这个主要用于合并:如果两个是数组的,则合并为一个数组;如果两个是JSON的,则合并为一个JSON,如果两个JSON有相同的key,则value合并为数组;如果是两个单值的,则合并为一个数组;如果是一个数组,一个是JSON,则合并为一个数组:
mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
+---------------------------------------+
| JSON_MERGE('[1, 2]', '[true, false]') |
+---------------------------------------+
| [1, 2, true, false] |
+---------------------------------------+
mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
+-------------------------------------------+
| JSON_MERGE('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------+
| {"id": 47, "name": "x"} |
+-------------------------------------------+
mysql> SELECT JSON_MERGE('{"id": "x"}', '{"id": 47}');
+-------------------------------------------+
| JSON_MERGE('{"id": "x"}', '{"id": 47}') |
+-------------------------------------------+
| {"id": ["x",47]} |
+-------------------------------------------+
mysql> SELECT JSON_MERGE('1', 'true');
+-------------------------+
| JSON_MERGE('1', 'true') |
+-------------------------+
| [1, true] |
+-------------------------+
mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
+------------------------------------+
| JSON_MERGE('[1, 2]', '{"id": 47}') |
+------------------------------------+
| [1, 2, {"id": 47}] |
+------------------------------------+
5、JSON_REMOVE删除操作
JSON_REMOVE删除操作,这个比较简单,删除字段中某个值,数组的或是JSON格式的都可以:
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"] |
+-------------------------+
mysql> SET @j = '{"name": "x","id": 47}';
mysql> SELECT JSON_REMOVE(@j, '$.id');
+-------------------------+
| JSON_REMOVE(@j, '$.id') |
+-------------------------+
| {"name": "x"} |
+-------------------------+
mysql> SELECT JSON_REMOVE(@j, '$.id', '$.name');
+-----------------------------------+
| JSON_REMOVE(@j, '$.id', '$.name') |
+-----------------------------------+
| {} |
+-----------------------------------+
mysql> SET @j = '{"name": "x","id": [47,48]}';
mysql> SELECT JSON_REMOVE(@j, '$.id[1]');
+-------------------------+
| JSON_REMOVE(@j, '$.id') |
+-------------------------+
| {"id": [47],"name": "x"} |
+-------------------------+
6、JSON_REPLACE操作
JSON_REPLACE替换操作,有则替换,无则忽略。
mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}
+-----------------------------------------------------+
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99);
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 99]} |
+-----------------------------------------------------+
7、JSON_SET操作
JSON_SET操作为更新和插入操作,已经存在的去更新;不存在则插入,以下是JSON_SET、JSON_INSERT、JSON_REPLACE执行结果的区别:
文章结构 MySQL 5.7 json部分内容较大。 给拆分成三篇来发: 第一篇: 介绍及json读取 第二篇: json写操作 第三篇: 索引以及generated字段+总结
本文分享自 MySQLBeginner 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!