开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【原创】MySQL5.7 JSON类型使用介绍

简介:
+关注继续查看

JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写。对机器来说易于解析和生成,并且会减少网络带宽的传输。

JSON的格式非常简单:名称/键值。之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本。 MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析。 我们先看看MySQL老版本的JSON存取。


示例表结构:

1
2
3
4
CREATE TABLE json_test(
id INT,
person_desc TEXT
)ENGINE INNODB;

我们来插入一条记录:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
INSERT INTO json_test VALUES (1,'{
    "programmers": [{
        "firstName": "Brett",
        "lastName": "McLaughlin",
        "email": "aaaa"
    }, {
        "firstName": "Jason",
        "lastName": "Hunter",
        "email": "bbbb"
    }, {
        "firstName": "Elliotte",
        "lastName": "Harold",
        "email": "cccc"
    }],
    "authors": [{
        "firstName": "Isaac",
        "lastName": "Asimov",
        "genre": "sciencefiction"
    }, {
        "firstName": "Tad",
        "lastName": "Williams",
        "genre": "fantasy"
    }, {
        "firstName": "Frank",
        "lastName": "Peretti",
        "genre": "christianfiction"
    }],
    "musicians": [{
        "firstName": "Eric",
        "lastName": "Clapton",
        "instrument": "guitar"
    }, {
        "firstName": "Sergei",
        "lastName": "Rachmaninoff",
        "instrument": "piano"
    }]
}');


那一般我们遇到这样来存储JSON格式的话,只能把这条记录取出来交个应用程序,有应用程序来解析。



现在到了MySQL5.7,我们重新修改下表结构:

1
ALTER TABLE json_test MODIFY person_desc json;


先看看插入的这行JSON数据有哪些KEY:

1
2
3
4
5
mysql> SELECT id,json_keys(person_desc) as "keys" FROM json_test\G
*************************** 1. row ***************************
  id: 1
keys: ["authors""musicians""programmers"]
1 row in set (0.00 sec)


我们可以看到,里面有三个KEY,分别为authors,musicians,programmers。那现在找一个KEY把对应的值拿出来:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT json_extract(AUTHORS,'$.lastName[0]'AS 'name', AUTHORS FROM
    -> (
    -> SELECT id,json_extract(person_desc,'$.authors[0][0]'AS "authors" FROM json_test
    -> UNION ALL
    -> SELECT id,json_extract(person_desc,'$.authors[1][0]'AS "authors" FROM json_test
    -> UNION ALL
    -> SELECT id,json_extract(person_desc,'$.authors[2][0]'AS "authors" FROM json_test
    -> ) AS T1
    -> ORDER BY NAME DESC\G
*************************** 1. row ***************************
   name"Williams"
AUTHORS: {"genre""fantasy""lastName""Williams""firstName""Tad"}
*************************** 2. row ***************************
   name"Peretti"
AUTHORS: {"genre""christianfiction""lastName""Peretti""firstName""Frank"}
*************************** 3. row ***************************
   name"Asimov"
AUTHORS: {"genre""sciencefiction""lastName""Asimov""firstName""Isaac"}
rows in set (0.00 sec)



现在来把详细的值罗列出来:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT
    -> json_extract(AUTHORS,'$.firstName[0]'AS "firstname",
    -> json_extract(AUTHORS,'$.lastName[0]'AS "lastname",
    -> json_extract(AUTHORS,'$.genre[0]'AS "genre"
    -> FROM
    -> (
    -> SELECT id,json_extract(person_desc,'$.authors[0]'AS "authors" FROM json
_test
    -> ) AS T\G
*************************** 1. row ***************************
firstname: "Isaac"
 lastname: "Asimov"
    genre: "sciencefiction"
1 row in set (0.00 sec)


我们进一步来演示把authors 这个KEY对应的所有对象删掉。

1
2
3
4
mysql> UPDATE json_test
    -> SET person_desc = json_remove(person_desc,'$.authors')\G
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查找下对应的KEY,发现已经被删除掉了。

1
2
3
4
5
mysql> SELECT json_contains_path(person_desc,'all','$.authors'as authors_exist
FROM json_test\G
*************************** 1. row ***************************
authors_exists: 0
1 row in set (0.00 sec)



总结下, 虽然MySQL5.7 开始支持JSON数据类型,但是我建议如果要使用的话,最好是把这样的值取出来,然后在应用程序段来计算,毕竟数据库是用来处理简单数据的。




本文转自 david_yeung 51CTO博客,原文链接:http://blog.51cto.com/yueliangdao0608/1711454,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
postman测试post请求参数为json类型
postman测试post请求参数为json类型
0 0
fastadmin设置默认返回json类型
fastadmin设置默认返回json类型
0 0
开发指南—数据类型—Json类型
PolarDB-X支持JSON类型。 与MySQL不同,PolarDB-X支持的JSON类型暂不支持作为分区键。 详细信息请参见MySQL Json类型。
0 0
SpringMVC五种类型参数传递和json数据传递参数
SpringMVC五种类型参数传递和json数据传递参数
0 0
[SpringMVC]请求与响应③(JSON数据传输参数、日期类型参数传递)
请求与响应③(JSON数据传输参数、日期类型参数传递)
0 0
MySQL Json类型的一个坑
MySQL中JSON类型有个隐藏的坑,底层会对json结构重排序后保存,目的是为了优化搜索效率;但是针对结构不能改变的业务来说,就是一个大坑
0 0
【MySQL】 json类型的增删改查
【MySQL】 json类型的增删改查
0 0
Newtonsoft.Json C#Json序列化和反序列化工具的使用、类型方法大全
Newtonsoft.Json C#Json序列化和反序列化工具的使用、类型方法大全
0 0
【mysql】JSON 类型
【mysql】JSON 类型
0 0
Python字符串和json类型的相互转换实例演示,python字符串转json、json转字符串
Python字符串和json类型的相互转换实例演示,python字符串转json、json转字符串
0 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载