MySQL 之 json 数据操作

背景：

开发一个自定义模板的需求，针对数据库设计，我想到了json，庆幸使用的MySQL版本为5.7，因为MySQL正是从5.7版本开始引入的json数据结构。

 

数据库操作：

新建数据库表之json数据结构，和其它数据结构区别不大，就是把数据类型变成了json，如下示例：

CREATE TABLE `t_task_order_template` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `json_data` json DEFAULT NULL COMMENT 'json数据,模板字段详情(组件类型type、字段名称name、字段key、提示文字hint、是否必填mandatory、选项值option[组件类型是单选或者多选时存在]、日期格式format[组件类型为日期型时存在])',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='表';

新建表后，需插入json数据格式的数据。数据示例：

[{"key": "key1", "hint": "", "name": "姓名", "type": "1", "format": "", "option": "", "mandatory": "1"}, {"key": "key2", "hint": "", "name": "年龄", "type": "2", "mandatory": ""}, {"key": "key3", "hint": "", "name": "性别", "type": "1", "mandatory": ""}]

上面的数据是个json数组，查询json数组条件的方法如下，通过 JSON_CONTAINS 函数，获取数组中name值为“姓名”的结果集

SELECT * FROM `t` where JSON_CONTAINS(json_data,JSON_OBJECT('name', "姓名"));

查完了json数组，再看json数据格式。数据示例：

{"key1": "小王", "key2": "22", "key3": "男"}

可通过如下两种方法对数据查询，查询key1值为“小王”的数据

SELECT * FROM `t` where json_data -> '$.key1'= '小王';
SELECT * FROM `t` where json_extract(json_data,'$.key1') = '小王';

看完查询，再看新增和更新，都是json_set函数，第一条语句将json结构中key1值更新为“小方”，第二条语句则增加key4值22

UPDATE t SET json_data = json_set(json_data,'$.key1','小方');
UPDATE t SET json_data = json_set(json_data,'$.key4','22');

看完新增和更新，再看删除，删除刚刚新增的key4

UPDATE t SET json_data = json_remove(json_data,'$.key4');

如果是对json数组进行修改操作，语法如下，增加对应[index]即可：

UPDATE t SET json_data = json_set(json_data,'$[0].hint','22')

 

最后看一下json相关操作的一些基本函数：