2017-04-19 回答
json (javascriptobject notation) 是一种轻量级的数据交换格式,主要用于传送数据。json采用了独立于语言的文本格式,类似xml,但是比xml简单,易读并且易编写。对机器来说易于解析和生成,并且会减少网络带宽的传输。由于json格式可以解耦javascript客户端应用与restful服务器端的方法调用,因而在互联网应用中被大量使用。
json的格式非常简单:名称/键值。之前mysql版本里面要实现这样的存储,要么用varchar要么用text大文本。 mysql5.7发布后,专门设计了json数据类型以及关于这种类型的检索以及其他函数解析。我们先看看mysql老版本的json存取。
示例表结构:
create table json_test(
id int,
person_desc text
)engine innodb;
我们来插入一条记录:
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格式的话,只能把这条记录取出来交个应用程序,由应用程序来解析。如此一来,json又和特定的应用程序耦合在一起,其便利性的优势大打折扣。
现在到了mysql5.7,可以支持对json进行属性的解析,我们重新修改下表结构:
alter table json_test modify person_desc json;
先看看插入的这行json数据有哪些key:
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把对应的值拿出来:
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"}
3 rows in set (0.00 sec)
现在来把详细的值罗列出来:
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对应的所有对象删掉。
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,发现已经被删除掉了。
mysql> select json_contains_path(person_desc,'all','$.authors')as authors_exists from json_test\g
*************************** 1. row***************************
authors_exists: 0
1 row in set (0.00 sec)
总结下,虽然mysql5.7开始支持json数据类型,但是我建议如果要使用的话,最好是把这样的值取出来,然后在应用程序段来计算。毕竟数据库是用来处理结构化数据的,大量的未预先定义schema的json解析,会拖累数据库的性能。