MySQL 5.7的原生JSON数据类型使用介绍

2016-01-26 11:07:53

作者：朱智武，新炬网络高级技术专家。

InnoDB/MyISAM/CSV存储引擎均支持JSON

笔者经过测试，InnoDB/MyISAM/CSV三个存储引擎支持JSON。

InnoDB
mysql>create table t ( id int, data json, primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

MyISAM
mysql>create table t ( id int, data json) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

CSV
mysql>create table t (id int not null, data json not null) engine=csv;
Query OK, 0 rows affected (0.00 sec)

MEMORY存储引擎使用JSON数据类型会报错，错误显示JSON数据类型的实现与BLOB/TEXT有一定关联。

mysql>create table t ( id int, data json, primary key(id)) engine=memory;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

创建JSON

创建包含JSON字段的表

mysql>create table t ( id int, data json, primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

插入包含合法JSON值的一条记录

mysql>insert into t values (1,'{"type":"fruit","name":"apple"}');
Query OK, 1 row affected (0.00 sec)

如果JSON值非法，会报错

mysql>insert into t values (1,'{"type":"fruit","name":"apple"})');
ERROR 3140 (22032): Invalid JSON text: "The document root must not follow by other values." at position 31 in value (or column) '{"type":"fruit","name":"apple"}}'.
mysql>insert into t values (1,'{"type":"fruit","name":"apple",}');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 31 in value (or column) '{"type":"fruit","name":"apple",}'.

使用SELECT语句可以将该JSON值查询出来

mysql>select * from t;
+----+-------------------------------------+
| id | data |
+----+-------------------------------------+
| 1 | {"name": "apple", "type": "fruit"} |
+----+-------------------------------------+

JSON值不仅仅可以以字符串表达，还可以通过函数生成，比如JSON_ARRAY()将一系列值组合成一个JSON数组，JSON_OBJECT()将键值对组合成一个JSON对象，JSON_MERGE()将两个或更多的JSON文档进行合并。

JSON_ARRAY('apple','orange') => ["apple", "orange"]
JSON_OBJECT('name','apple','type','fruit') => {"name": "apple", "type": "fruit"}
JSON_MERGE('["orange"]', '{"name":"apple"}') => ["orange", {"name": "apple"}]

查询JSON

JSON的路径表达式选择出JSON文档中的给定值。如以下的例子，我们使用JSON_EXTRACT()函数读取JSON文档键为name的值。

mysql>select json_extract('{"name": "apple", "type": "fruit"}','$.name');
+-------------------------------------------------------------+
| json_extract('{"name": "apple", "type": "fruit"}','$.name') |
+-------------------------------------------------------------+
| "apple" |
+-------------------------------------------------------------+

路径表达式以$字符开始，$字符表示该JSON文档，之后是路径选择器，包括点号和[N]，其中点号对应JSON对象，而[N]对应JSON数组（数组从0开始编号）。路径表达式中还可以包含*和**匹配符。如果JSON文档中不存在该路径，那么返回值会是NULL。

路径表达式可以内联使用，比如以下的例子。

mysql>select * from t where data->'$.name'= 'apple';
+----+------------------------------------+
| id | data |
+----+------------------------------------+
| 1 | {"name": "apple", "type": "fruit"} |
+----+------------------------------------+

事实上，data->'$.name'即是json_extract(data,'$.name')的同义语法。如果查看执行计划，上面的SELECT语句走的是全表扫描。

执行以下的SQL语句创建data->'$.name'的生成字段（生成字段，即Generated Column，也是MySQL 5.7的新特性），并创建相应的索引。

alter table t add column name varchar(20) as (json_unquote(data->'$.name')) virtual, add key k_name(name);

再次查看该语句的执行计划，可以使用上索引。

修改JSON

MySQL有一些函数，可以修改JSON文档，并返回修改后的新JSON文档。路径表达式指出文档中的哪部分需要修改。这些函数比如JSON_INSERT()、JSON_REPLACE()、JSON_SET()和JSON_REMOVE()。我们以以下的JSON文档为例。

mysql> set @json='["apple", {"attr": [50, true], "name": "orange"}]';

JSON_INSERT()可以添加新值，但它不会替换已存在的值。

mysql> select json_insert(@json, '$[1].attr[0]', 2, '$[2]', "pear");
+-----------------------------------------------------------+
| json_insert(@json, '$[1].attr[0]', 2, '$[2]', "pear") |
+-----------------------------------------------------------+
| ["apple", {"attr": [50, true], "name": "orange"}, "pear"] |
+-----------------------------------------------------------+

JSON_REPLACE()替换已有的值，但忽略新值，即新值不会添加到文档中。

mysql> select json_replace(@json, '$[1].attr[0]', 2, '$[2]', "pear");
+--------------------------------------------------------+
| json_replace(@json, '$[1].attr[0]', 2, '$[2]', "pear") |
+--------------------------------------------------------+
| ["apple", {"attr": [2, true], "name": "orange"}] |
+--------------------------------------------------------+

JSON_SET()替换已有路径的值，添加未有路径的值。

mysql> select json_set(@json, '$[1].attr[0]', 2, '$[2]', "pear");
+----------------------------------------------------------+
| json_set(@json, '$[1].attr[0]', 2, '$[2]', "pear") |
+----------------------------------------------------------+
| ["apple", {"attr": [2, true], "name": "orange"}, "pear"] |
+----------------------------------------------------------+

JSON_REMOVE()移除JSON文档中给定的一个或多个路径，如果路径不存在的话，函数会忽略该路径。

mysql> select json_remove(@json, '$[1].attr[0]', '$[2]');
+-----------------------------------------------+
| json_remove(@json, '$[1].attr[0]', '$[2]') |
+-----------------------------------------------+
| ["apple", {"attr": [true], "name": "orange"}] |
+-----------------------------------------------+

结语

MongoDB是主要支持JSON的数据库，而MySQL增加了对流行的JSON的支持，扩展了MySQL的应用领域。到底是使用MySQL+MongoDB更好呢？还是单单使用MySQL更好呢？在不同的场景中可能有不同的答案。此外，很多人将MySQL与PostgreSQL作对比，很多文章列出了PostgreSQL比MySQL强的地方，对JSON的支持是其中一项，现在，MySQL将这弥补上了。

上一篇：MYSQL存储引擎介绍及选择
下一篇：Web前端技术框架介绍