MySQL 5.7 使用原生JSON类型

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

1数据在键值对中，
2数据由逗号分隔，
3花括号保存对象，
4方括号保存数组。

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

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

如何在MySQL中使用JSON类型：

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

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

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

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

也可以使用JSON_OBJECT()函数：

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

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

1mysql> SELECT lastlogininfo FROM user WHERE name = 'lucy';
2+------------------------------------------------------------------------+
3| lastlogininfo  |
4+------------------------------------------------------------------------+
5| {"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列->'$.键'

1mysql>  SELECT * FROM user WHERE lastlogininfo ->'$.time' > '2015-10-02';
2+-----------------------------------------------------------------------+
3| id | name | lastlogininfo|
4+-----------------------------------------------------------------------+
5|  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列 , '$.键')

1mysql>  SELECT * FROM user WHERE JSON_EXTRACT(lastlogininfo,'$.time') > '2015-10-02';
2+-----------------------------------------------------------------------+
3| id | name | lastlogininfo|
4+-----------------------------------------------------------------------+
5|  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类型的比较规则,从最高优先级到最低优先级。显示在一行的类型则是具有相同的优先级。

1BLOB
2BIT
3OPAQUE
4DATETIME
5TIME
6DATE
7BOOLEAN
8ARRAY
9OBJECT
10STRING
11INTEGER, DOUBLE
12NULL

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

1mysql> SELECT JSON_TYPE(lastlogininfo->'$.ip') FROM user;
2+----------------------------------+
3| JSON_TYPE(lastlogininfo->'$.ip') |
4+----------------------------------+
5| STRING   |
6| STRING   |
7+----------------------------------+
82 rows in set (0.00 sec)

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

创建虚拟列：

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

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

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

这只是一个简单的JSON类型例子，Mysql还提供了许多对JSON类型处理的函数，可以从MySQL的官方网站查看帮助文档：
http://dev.mysql.com/doc/refman/5.7/en/json.html

MySQL 5.7 使用原生JSON类型

评论区

相关推荐