前言
- 环境:
- mysql Ver 8.0.29 for Win64 on x86_64
- 最近被mysql的json操作函数坑了一把,顺便记录下之前折腾的一些东西。
在MySql中使用
声明json类型的字段:
create table test(
uids json not null default ('[]')
);
- 注意:
- 默认值需要使用小括号包裹的符合json格式的字符串,如果不使用小括号()则会得到如下错误:
- ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'uids' can't have a default value
- 最外面使用引号使用 单引号' 或 双引号" 都是可以的,但最好是最外面使用一种,里面的json字符串使用一种,例如:
default ('[{"name":"coolight"}, {"name","洛天依"}]')
或
default ("[{'name':'coolight'}, {'name','洛天依'}]")
- 如果json由程序合成而来,尤其需要注意防止sql注入
Mysql中常用的操作json函数
- 贴个外链:Mysql JSON 函数参考,里面有很多函数的信息,这里我们只拿一些后面会用到的出来说。
- JSON_length ( json )
- 返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度
- 我们这里只用它来获取JSON数组的元素个数
- JSON_search ( json, one_or_all, string )
- 搜索json中第一次one或全部all和给定字符串string匹配的路径
- 后面我们会使用很多函数参数 path,实际上,我们很多时候并不知道想修改的值在json中的路径,尤其是在json是一个数组的时候,因此我们可以使用这个函数,搜索想修改的元素的路径,然后传给其他函数去操作。
- JSON_unquote ( json )
- JSON_contains ( json1, json2, path )
- 检查 json1 中指定路径 path 位置是否包含了 json2
- 在比较两个数组元素相同,仅允许元素顺序不同时,可以使用:
JSON_LENGTH(json1)=JSON_LENGTH(json2) and JSON_CONTAINS(json1, json2)
// 例如:
update test set enable=true where JSON_LENGTH(json1)=JSON_LENGTH(json2) and JSON_CONTAINS(json1, json2);
- JSON_array_append ( json, path, value )
- 对json中指定路径path所在的数组添加元素value
- 路径中 $ 表示 根,然后访问数组内容用 [下标],访问对象内容用 .值名称
// 示例:在数组末尾追加元素
SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);
// 得到 [1,2,3,4]
// 示例:对数组添加一个元素,如果已经存在则不添加
// ["1000000", "1000001", "1000002"]
update test set uids=json_array_append(uids, '$','1000001') where id=777 and JSON_CONTAINS(uids,JSON_ARRAY('1000001'))=0;
// 没有添加,注意where ... and 后面 使用JSON_CONTAINS判断了是否包含准备添加的元素
- JSON_remove ( json, path0 [, path1, ... ] )
- 删除json中指定路径 path 的元素
- 请注意,传入多个path时,它将从左往右依次删除,
- 因此,执行:JSON_remove( "[0,1,2,3,4,5,6]", "$[0]", "$[2]" )
- 先删除元素0,得到 [1,2,3,4,5,6]
- 然后删除当前下标为2的元素,即3,得到 [1,2,4,5,6]
- 示例:
- 删除一个没有重复元素的字符串数组中的一个元素
- 注意这里uids是一个 id的字符串数组
- JSON_SEARCH的第三个参数需要是字符串类型
// ["1000000", "1000001", "1000002"]
update test set uids=json_remove(uids, JSON_UNQUOTE(JSON_SEARCH(uids, 'one','1000001'))) where id=777;
// ["1000000", "1000002"]
- 注意,数组中字符串类型和数值类型是不一样的,例如:
- ['1','2','3'] 和 [1,2,3] 是不同的。
- 这在使用上面的那些函数去判断/添加/删除时尤其需要注意,否则可能出现添加使用的是数值类型,而查找/删除时使用元素的数值类型去搞,结果一直行不通,而两者的差别仅仅是传入元素时是否加了引号。(我之前就是这样的......)
- 如果你是程序合成sql,可以自己添加或去除元素的引号来控制,而如果你使用了mysql的触发器,则可以使用 CAST 函数来转换,例如:
create TRIGGER auto_reset after insert on test for each row
BEGIN
update test set uids=json_array_append(uids, '$', CAST(NEW.uid as char)) where id=NEW.id;
END;
狗的食物过敏
为什么当你回家时猫会抓东西?
猫可以吃火腿吗?
什么年龄的猫可以生小猫?
Management Of Overdose priligy amazon uk
狗害怕的迹象是什么?
为什么猫躺在他们的垃圾箱里?
如何阻止狗和小狗的破坏性咀嚼
对猫来说太冷有多冷?
猫在野外吃什么?
如何牵引训练拉动的狗
一群猫叫什么?
狗的湿粮的利弊
猫可以吃松鼠吗?
Hi there to all, for the reason that I am genuinely keen of reading this website’s post to be updated on a regular basis. It carries pleasant stuff.
狗的皮肤癌
为什么猫把耳朵放回去?
猫舔你的鼻子是什么意思?
绝育母猫叫什么?
如何判断你的猫是否在做噩梦
为什么猫如此灵活?
猫能长多久?
缅因猫能活多久?
猫可以吃黄金糖浆吗?
8 种不同类型的狗项圈
For more, visit acc buy stromectol australia Thus, it might be expected that the presence of CTCs should be associated with a worse outcome for all patients in the SUCCESS A study, regardless of the method for CTC detection
猫多久放一次屁?
关于猫,圣经怎么说?
俄罗斯蓝猫有黄色的眼睛吗?
如何在爆发时保护您的狗免受钩端螺旋体病的侵害
拥抱宠物角落趋势的 4 种方法
我的猫被黄蜂蜇了。
çok bilgilendirici bir yazı olmuş ellerinize sağlık teşekkür ederim
波斯猫有多少种?
hocam gayet açıklayıcı bir yazı olmuş elinize emeğinize sağlık.
为什么猫这么懒?
猫可以吃辣椒豆吗?
狗的交流和绥靖手势