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相关操作的一些基本函数:

MySQL 之 json 数据操作

 

MySQL 之 json 数据操作

上一篇:js获取select标签选中的值及文本


下一篇:[JavaScript] Cookie,localStorage,sessionStorage概述