mysql awk mysql awk json

转载

mob64ca1402d47a 2023-08-11 22:56:05

文章标签 mysql awk mysql中json类型 json JSON 数据 文章分类 MySQL 数据库

环境

我这里使用的是 mysql5.7.27 版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.27 |
+-----------+
1 row in set (0.00 sec)

介绍

在 MySQL5.7中专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析，在次之前，我们通常使varchar或text数据类型存储JSON格式数据。

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

创建

这里我使用 Navicat 来创建一个带有json类型的字段(如果你的Navicat中没有json类型，那么你可能需要升级到最新版)

mysql awk mysql awk json_json

CREATE TABLE `test` (
`id` int(255) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '示例表id值',
`json_demo` json NULL COMMENT '示例的json类型',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
注意 json 类型没有默认值，也不可以添加索引(但是可以变相的添加虚拟键的索引，后面讲)
新增
新增一条数据和普通的新增没有太大的区别(这里一定要是json类型的格式)
INSERT INTO `test`(`json_demo`) VALUES ('["0","2"]');
INSERT INTO `test`(`json_demo`) VALUES ('{"key1":"value1","key2":"value2"}');
-- 使用JSON_ARRAY函数插入一条数组类型数据
INSERT INTO `test`(`json_demo`) VALUES(JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()));
-- 使用JSON_OBJECT函数插入一条json对象数据
INSERT INTO t_json(id,sname,info) VALUES(JSON_OBJECT("age", 20, "time", now()));
如果我要在一条json的最后新增一条数据呢,这里需要使用到函数json_array_append
update `test` set `json_demo` = json_array_append(`json_demo`,'$',json_object("age", 23)) where id=3;
JSON_INSERT插入值(插入新值，但不替换已经存在的旧值)
update `test` set `json_demo` = json_insert(`json_demo`, '$.arge','Mere') where id = 3
删除
这里在json数组中删除一条json数据需要用到函数json_remove
--$[0] 这里是表示删除json_demo这一串数组中下标为0的json对象数据
update `test` set `json_demo` = json_remove(`json_demo`,'$[0]') where id = 3
--如果要删除json对象中的某个键 将$[0]改成你要删除的键就好 比如我要删除数组[0]中的key为age的
update `test` set `json_demo` = json_remove(`json_demo`,'$[0].age') where id = 3
--如果你是json对象的话就将[0]去掉就好 $.age 就是删除json对象中key为 age 的
update `test` set `json_demo` = json_remove(`json_demo`,'$.age') where id = 3
当然如果你要删除整个大的json 也可以使用常规语句 delete
修改
JSON_REPLACE 替换值(只替换已经存在的旧值，如果替换的值不存在则不做任何改动)
JSON_SET 设置值(替换旧值，如果不存在则插入不存在的新值)
update `test` set `json_demo` = json_replace(`json_demo`, '$[0].now','Mere') where id = 3
update `test` set `json_demo` = json_set(`json_demo`, '$[0].nows','Mere') where id = 3
查找
最普通的查看
mysql> SELECT * FROM test;
+----+-------------------+
| id | json_demo |
+----+-------------------+
| 3 | [{"now": "Mere"}] |
+----+-------------------+
1 row in set (0.07 sec)
JSON_TYPE()函数尝试将传入的值解析为JSON值
mysql> SELECT JSON_TYPE('{"now": "Mere"}');
+------------------------------+
| JSON_TYPE('{"now": "Mere"}') |
+------------------------------+
| OBJECT |
+------------------------------+
1 row in set (0.08 sec)
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.06 sec)
JSON_EXTRACT(json列, '$.键')查询
mysql> select id,json_extract(json_demo, '$[0].now') as name from test;
+----+--------+
| id | name |
+----+--------+
| 3 | "Mere" |
+----+--------+
1 row in set (0.06 sec)
查询还有种写法 json列->'$.键'
mysql> select `notice`->'$[0]' from home;
+---------------------------------------------+
| `notice`->'$[0]' |
+---------------------------------------------+
| {"title": "666", "content": "
666
\n"} | 
 
+---------------------------------------------+
1 row in set (0.66 sec)
JSON_CONTAINS 指定数据是否存在
JSON_CONTAINS_PATH 指定路径是否存在
JSON_EXTRACT 查找所有指定数据
JSON_KEYS 查找所有指定键值
JSON_SEARCH 查找所有指定值的位置
....

索引

现在MySQL不支持对JSON列进行索引，官网文档的说明是：

JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.(无法索引JSON列。您可以通过在从JSON列中提取标量值的生成列上创建索引来解决此限制。)

虽然不支持直接在JSON列上建索引，但MySQL规定，可以首先使用路径表达式对JSON文档中的标量值建立虚拟列，然后在虚拟列上建立索引。这样用户可以使用表达式对自己感兴趣的键值建立索引。

创建索引:

--在表 test 中创建一个name虚拟列设置规则

alter table `test` add name varchar(20) generated always as (`json_demo`->'$.name') virtual;

mysql awk mysql awk json_mysql awk_02