Mysql 5.7 json

JSON是一种轻量级的数据交换格式，采用了独立于语言的文本格式，类似XML，但是比XML简单，易读并且易编写。对机器来说易于解析和生成，并且会减少网络带宽的传输。
    JSON的格式非常简单：名称/键值。之前MySQL版本里面要实现这样的存储，要么用VARCHAR要么用TEXT大文本。 MySQL5.7发布后，专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析。 我们先看看MySQL老版本的JSON存取。

The space required to store a JSON document is roughly the same as for LONGBLOB or LONGTEXT; see Section 12.8, “Data Type Storage Requirements”, for more information. It is important to keep in mind that the size of any JSON document stored in a JSON column is limited to the value of the max_allowed_packet system variable. (When the server is manipulating a JSON value internally in memory, it can be larger than this; the limit applies when the server stores it.)

user表：

'CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `lastlogininfo` json DEFAULT NULL,
  `memberlist` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'

插入数据：

id,name,lastlogininfo,memberlist
1,lucy,"{""ip"": ""192.168.1.2"", ""time"": ""2015-01-01 13:00:00"", ""result"": ""fail""}","{""applist"": [{""appid"": ""11""}, {""appid"": ""22""}]}"
2,vv,"{""ip"": ""192.168.1.3"", ""time"": ""2015-01-01 13:00:00"", ""result"": ""fail""}","{""applist"": [{""appid"": ""123213""}, {""appid"": ""4444""}]}"
9,dd,"{""ip"": ""192.168.1.4"", ""time"": ""2015-01-01 13:00:00"", ""result"": ""fail""}",NULL
10,sdf,"[""sd232"", ""sdsssss""]",NULL

查询memberlist中包含appid:123213的：

select * from user where JSON_CONTAINS(json_extract(memberlist,'$.applist[*]'),'{"appid":"123213"')=1

结果为：

2,vv,"{""ip"": ""192.168.1.1"", ""time"": ""2015-01-01 13:00:00"", ""result"": ""fail""}","{""applist"": [{""appid"": ""123213""}, {""appid"": ""4444""}]}"