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""}]}"

?

總結

以上是生活随笔為你收集整理的Mysql 5.7 json的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。