[转帖]MySQL 5.7的原生JSON数据类型使用介绍_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1688 | 回复: 0   主题: [转帖]MySQL 5.7的原生JSON数据类型使用介绍        上一篇   下一篇 
qq_QzQdecide
注册用户
等级:新兵
经验:16
发帖:2
精华:0
注册:2016-11-24
状态:离线
发送短消息息给qq_QzQdecide 加好友    发送短消息息给qq_QzQdecide 发消息
发表于: IP:您无权察看 2019-8-29 9:53:27 | [全部帖] [楼主帖] 楼主

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

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将这弥补上了。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论