浅谈MYSQL与JSON相关的操作

本文最后更新于2020年8月2日,可能由于时间等因素导致内容失效,请自行辨别或联系作者。

Mysql中保存JSON格式的数据,要么使用text或varchar类型,通过“读取-反序列化-修改-序列化-回写”等操作;要么使用Mysql 5.7提供的Json类型。目前公司使用的是用text类型保存,原以为如果需要往里面新增内容的话,只能够通过程序实现的,直到上次迭代,看到同事写了一个SQL,就轻轻松松搞定了,才了解到,MYSQL有函数可以操作,故学习整理一波。

假设有下面这么一张表:

CREATE TABLE `settings` (
  `setting_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `id` int NOT NULL AUTO_INCREMENT,
  `setting_json` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入

插入可以直接拼接成字符串,或者用MYSQL提供的函数。

-- 直接使用 字符串 插入
INSERT INTO settings (setting_text) VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}')

-- 使用JSON函数插入
INSERT INTO settings (setting_json) VALUES(JSON_OBJECT("opending","Sicilian","variations",JSON_ARRAY('pelikan','dragon','najdorf')))

JSON_OBJECT

  • 根据参数列表创建JSON对象
  • 按照参数的排列,第一个是key,第二个是value,第三个是key,以此类推。参数个数必须是双数
  • 参数列表可以为空,得到空JSON:{}
  • key不能是null。否则报错。value可以是null

JSON_ARRAY

  • 根据参数值创建JSON数组,每个参数都是数组中的一个元素。

修改

用来修改的函数非常多,这里先总结以下六种。

JSON_INSERT(json_doc, path, val[, path, val] …)

  • 往JSON中插入value值
  • 如果已经存在,则不会再插入/覆盖
UPDATE settings SET setting_text = JSON_INSERT(setting_text, '$.open', TRUE) WHERE id = 1

JSON_SET(json_doc, path, val[, path, val] …)

  • 修改JSON的值,或插入值
UPDATE settings SET setting_text = JSON_SET(setting_text, '$.open', FALSE) WHERE id = 1

JSON_REPLACE(json_doc, path, val[, path, val] …)

  • 修改JSON的某个value值
UPDATE settings SET setting_text = JSON_REPLACE(setting_text, '$.open', TRUE) WHERE id = 1

UPDATE settings SET setting_text = JSON_REPLACE(setting_text, '$.variations', CAST('["nihao","haha"]' AS JSON)) WHERE id = 1

JSON_REMOVE(json_doc, path[, path] …)

  • 移除JSON的某个value值
UPDATE settings SET setting_text = JSON_REMOVE(setting_text, '$.open') WHERE id = 1

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

  • 向JSON数组中追加元素,如果对应位置是单个元素,则和新元素一起封装成数组。
UPDATE settings SET setting_text = JSON_ARRAY_APPEND(setting_text, '$.variations', 'word') WHERE id = 1

UPDATE settings SET setting_text = JSON_ARRAY_APPEND(setting_text, '$.otherWelcome', CAST('{"type": 1, "content": "欢迎"}' AS JSON)) WHERE id = 6

 

 

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

  • 在数组的指定位置追加元素,原位置的元素和后面的元素依次向后移一位
  • 指定位置超过数组上限,则添加在数组最后位置
UPDATE settings SET setting_text = JSON_ARRAY_INSERT(setting_text, '$.variations[0]', 'XIXI') WHERE id = 1

UPDATE settings SET setting_text = JSON_ARRAY_INSERT(setting_text, '$.variations[100]', '6666') WHERE id = 1

查找

与查找相关的函数,就只针对JSON格式的字段了。

JSON_CONTAINS(target, candidate[, path])

  • 检查 候选json字符串(candidate) 是否包含在目标JSON中(target) 。如果包含,结果为1;如果不包含,结果为0
  • target是目标元素,candidate是候选元素,path是路径表达式,如果path有值,则目标元素需要先经过路径表达式的处理再参与判断
  • 候选JSON和目标JSON都得是JSON格式的字符串,单个元素也得加引号,数字要写成这样:”1″,字符串要引号外面再套引号,写成:’ “abc” ‘

 

SELECT JSON_CONTAINS(setting_json, '"Sicilian"','$.opending') FROM settings WHERE id = 4

SELECT * FROM settings WHERE JSON_CONTAINS(setting_json, '"Sicilian"','$.opending')

JSON_EXTRACT(json_doc, path[, path] …)

  • 获取Json中指定的值
  • 如果key中含有特殊字符,那么需要双引号将key引起来,并使用转移符
SELECT * FROM settings WHERE JSON_EXTRACT(setting_json, '$.opending')

SELECT * FROM settings WHERE JSON_EXTRACT(setting_json, '$."a\'status"')
SELECT * FROM settings WHERE JSON_EXTRACT(setting_json, '$."open ing"')

参考

MySQL之JSON数据类型操作示例

MySQL8.0新特性学习笔记(五):JSON格式简介和JSON函数详解

 

为TA充电
共{{data.count}}人
人已赞赏
Java基础编程语言

优化代码中的if-else

2020-7-12 18:55:40

Java编程语言

图仓——一个集成图片上传、分发、管理的图片仓库

2020-11-14 23:31:26

2 条回复 A文章作者 M管理员
  1. 丶Jayce

    学习了💪

个人中心
今日签到
有新私信 私信列表
搜索