MySQL 5.7 JSON 简介

从 MySQL 5.7.8 版本起,MySQL 开始支持原生 JSON 格式。

1. 存储 JSON 数据

首先我们要把字段类型设置为 json,这样存储的时候才会检查插入的 JSON 数据是否符合规范,不符合规范会报错。至于如何生成 JSON 数据,我们可以使用 PHP 的 json_encode() 方法,也可以使用 MySQL 的 JSON_ARRAY() 和 JSON_OBJECT() 方法。

create table json_data(json_data json);
insert into json_data(json_data) values ('{"name":"Victor Tang","phone":"13866001386","email":"tanghengzhi@live.com"}');
insert into json_data(json_data) values (JSON_OBJECT("name", "Victor Tang", "phone", "13866001386", "email", "tanghengzhi@live.com"));

2. 查询 JSON 数据

查询 JSON 数据里面的值不再需要使用 LIKE % % 这种 trick 的解决方法。现在可以使用 JSON_EXTRACT(column, path) 和 从 MySQL 5.7.9 版本开始支持的 column->path 两种查询语法。当然前提是查询的字段类型必须设置为 json。

select JSON_TYPE(json_data) from json_data;

select json_data->"$.email" from json_data;

select JSON_EXTRACT(json_data, "$.email") from json_data;

3. 修改 JSON 数据

修改 JSON 数据也不需要先把数据取出来,使用 PHP 的 json_decode() 方法解码,修改,然后再用 json_decode() 方法编码,最后更新数据库。MySQL 5.7 提供了 JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE() 等一系列方法。几个方法的区别是:JSON_SET() 支持插入和更新,JSON_INSERT() 只支持插入,JSON_REPLACE() 只支持更新。JSON_REMOVE() 是删除 JSON 数据中的指定内容,没什么好说的。

select JSON_SET(json_data, "$.country", "China", "$.phone", "138****1386") from json_data;

select JSON_INSERT(json_data, "$.country", "China", "$.phone", "138****1386") from json_data;

select JSON_REPLACE(json_data, "$.country", "China", "$.phone", "138****1386") from json_data;

select JSON_REMOVE(json_data, "$.country", "$.phone") from json_data;

4. 已知的问题

综上所述,MySQL 5.7 对于 JSON 的支持已经很完善了,达到了生产可用的水平。但是有一些已知的问题,使用的时候还是需要注意一下。

SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');

在 MySQL 5.7 中执行以上 SQL 语句,返回结果是 {“key1”: 1, “key2”: “abc”}。

JSON.stringify({'key1': 1, 'key2': 'abc', 'key1': 'def'});

而在你的 Chrome 浏览器中执行以上代码,返回结果是 {“key1″:”def”,”key2″:”abc”}。

MySQL 给出的官方解释是:

This “first key wins” handling of duplicate keys is not consistent with RFC 7159. This is a known issue in MySQL 5.7, which is fixed in MySQL 8.0. (Bug #86866, Bug #26369555)

所以在使用 JSON_OBJECT() 方法之前要保证你的 JSON 对象没有重复的 key,如果有请先进行去重操作。


参考:

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

 435 total views,  2 views today