MySQL5.7对json的操作

场景：

Mysql中需要存储用户的相关证件信息，即存储图片信息， 数量不定。这时候通常采用的做法：

1、定义多个存储字段，限制上传图片数量。

2、定义一个存储字段，多个图片用分隔符隔开，做为字符串存储。

3、独立定义附件表，用来单独存储图片。

1、2都不是很建议，但是具体还是要看业务咯。这里介绍另一种方式，即直接存储json.

 

MySQL5.7开始支持Json类型

在MySQL5.7.8中，MySQL支持由RFC 7159定义的本地JSON数据类型，它支持对JSON(JavaScript对象标记)文档中的数据进行有效访问。

MySQL5.7.9开始可以使用column->path来做为JSON_EXTRACT(column, path)的快捷使用。->的左边为json数据列名，右边为json数据的某个路径表达式。如：t.attr -> '$.id'。

常用函数

CREATE TABLE `demo-boot`.`Untitled`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `license` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

JSON_OBJECT：创建json对象

JSON_ARRAY：创建json数组

insert into t_json_test (license) values (JSON_OBJECT("name", "身份证", "value", "http://images.com/1.png"));   -- 插入json对象
insert into t_json_test (license) values (JSON_ARRAY("name", "身份证", "value", "http://images.com/1.png", "name", "驾照", "value", "http://images.com/2.png"));   -- 插入json数组
insert into t_json_test (license) values (JSON_ARRAY(JSON_OBJECT("name", "身份证", "value", "http://images.com/1.png"), JSON_OBJECT("name", "驾照", "value", "http://images.com/2.png")));  -- 插入json对象数组

SQL执行对应结果：

column -> path查询（注意这里只能操作json对象，对于json数组无效）：

select license -> '$.value' as license_img from t_json_test where id=8

column ->> path，等同于json_unquote，去除json字符串的引号：

select license ->> '$.value' as license_img from t_json_test where id=8

如果是json数组，可以采用column -> '$[index].prop'的方式：

select license ->> '$[0].name' as license_img from t_json_test where id=10  -- '$[*].name'代表全部

JSON_SET，通过key修改value

select JSON_SET(license, '$[0].name', '护照') from t_json_test

JSON_INSERT，新增一个不存在的key。

update t_json_test set license=JSON_INSERT(license, '$[0].phone', '13333333333') -- 如果是对象，使用'$.phone'

上述列出一些常用的MySQL操作json的方法。

更多json操作函数参考：https://dev.mysql.com/doc/refman/5.7/en/json-functions.html