请选择 进入手机版 | 继续访问电脑版

MySQL 5.7 使用原生JSON类型的例子

发表于 2016-05-23 16:02 显示全部楼层 24 1260

本帖最后由 陈惠 于 2016-9-20 10:34 编辑

首先回顾一下JSON的语法规则:

数据在键值对中,
数据由逗号分隔,
花括号保存对象,
方括号保存数组。


按照最简单的形式,可以用下面的JSON表示:

{"name": "Brett", "email": "brett@xxx.com"}


如何在MySQL中使用JSON类型:

新建user表,设置lastlogininfo列为JSON类型。

mysql> CREATE TABLE user(id INT PRIMARY KEY, name VARCHAR(20) , lastlogininfo JSON);
Query OK, 0 rows affected (0.27 sec)


向user表插入普通数据与json数据。mysql会对插入的数据进行JSON格式检查,确保其符合JSON格式,若插的是不合法的数据,会出现Invalid JSON text错误。

mysql> INSERT INTO user VALUES(1 ,"lucy",'{"time":"2015-01-01 13:00:00","ip":"
192.168.1.1","result":"fail"}');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO user VALUES(2 ,"bobo",'{"time":"2015-10-07 06:44:00","ip":"
192.168.1.0","result":"success"}');
Query OK, 1 row affected (0.04 sec)


也可以使用JSON_OBJECT()函数:

mysql> INSERT INTO user VALUES(1 ,"lucy",JSON_OBJECT("time",NOW(),"ip","
192.168.1.1","result","fail"));
Query OK, 1 row affected (0.00 sec)


查询name为'lucy'的最后登陆信息。

mysql> SELECT lastlogininfo FROM user WHERE name = 'lucy';
+------------------------------------------------------------------------+
| lastlogininfo  |
+------------------------------------------------------------------------+
| {"ip": "192.168.1.1", "time": "2015-01-01 13:00:00", "result": "fail"} |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)


查询最后登陆时间在2015-10-02后的用户。JSON数据使用->操作符,其表达式为:该json列->'$.键'与JSON_EXTRACT(json列 , '$.键')等效使用。如果传入的不是一个有效的键,则返回Empty set。该表达式可以用于SELECT查询列表 ,WHERE/HAVING , ORDER/GROUP BY中,但它不能用于设置值。

表达式 : json列->'$.键'

mysql>  SELECT * FROM user WHERE lastlogininfo ->'$.time' > '2015-10-02';
+-----------------------------------------------------------------------+
| id | name | lastlogininfo|
+-----------------------------------------------------------------------+
|  2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)


等价于 :JSON_EXTRACT(json列 , '$.键')

mysql>  SELECT * FROM user WHERE JSON_EXTRACT(lastlogininfo,'$.time') > '2015-10-02';
+-----------------------------------------------------------------------+
| id | name | lastlogininfo|
+-----------------------------------------------------------------------+
|  2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)


比较JSON值采用两个级别。第一级是基于JSON类型的比较。如果类型不同,则取决于哪种类型具有更高的优先级。如果是相同的JSON类型,则是第二级,使用该类型的规则来比较。

下面的列表显示了JSON类型的比较规则,从最高优先级到最低优先级。显示在一行的类型则是具有相同的优先级。

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL


使用JSON_TYPE()函数返回指定属性对应的类型名称:

mysql> SELECT JSON_TYPE(lastlogininfo->'$.ip') FROM user;
+----------------------------------+
| JSON_TYPE(lastlogininfo->'$.ip') |
+----------------------------------+
| STRING   |
| STRING   |
+----------------------------------+
2 rows in set (0.00 sec)


值得一提的是,可以通过虚拟列对JSON类型的指定属性进行快速查询。

创建虚拟列:

mysql> ALTER TABLE user ADD lastloginresult VARCHAR(15)
    -> GENERATED ALWAYS AS (lastlogininfo->'$.result') VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


使用时和普通类型的列查询是一样的:

mysql> SELECT lastloginresult FROM user WHERE name='lucy';
+-----------------+
| lastloginresult |
+-----------------+
| "fail"  |
+-----------------+
1 row in set (0.00 sec)


这只是一个简单的JSON类型例子,Mysql还提供了许多对JSON类型处理的函数,可以从MySQL的官方网站查看帮助文档:

http://dev.mysql.com/doc/refman/5.7/en/json.html


回复 使用道具
举报
p出我世界

发表于 02-10 04:36 显示全部楼层

祝愿小码哥越来越红火!!红红火火恍恍惚惚!

回复 支持 反对 使用道具
举报
东北兰蔻

发表于 02-10 00:47 显示全部楼层

以诚感人者,人亦诚而应。一点回复,敬请笑纳!

回复 支持 反对 使用道具
举报
pcatzj

发表于 02-09 22:41 显示全部楼层

前排支持!!

回复 支持 反对 使用道具
举报
Viper

发表于 02-09 21:02 显示全部楼层

前排支持!!

回复 支持 反对 使用道具
举报
大道至简

发表于 02-09 20:30 显示全部楼层

666

回复 支持 反对 使用道具
举报
TigerCY

发表于 02-09 14:26 显示全部楼层

别人说是金子总要发光的,我就来看看。

回复 支持 反对 使用道具
举报
loverol

发表于 02-09 05:33 显示全部楼层

回复 支持 反对 使用道具
举报
寻找皮卡丘

发表于 02-08 22:14 显示全部楼层

回复 支持 反对 使用道具
举报
陈小呆

发表于 02-08 19:33 显示全部楼层

前排支持!!

回复 支持 反对 使用道具
举报
123下一页

发表新文章
陈惠

小码哥名师团队

0

学分

243

学币

252

积分

小码哥名师团队

Rank: 5Rank: 5

积分
252
Ta的主页 发消息
精华帖排行榜

精彩推荐

  • 关注小码哥教育