目录
前言
- 环境:
- mysql Ver 8.0.29 for Win64 on x86_64
- 最近被mysql的json操作函数坑了一把,顺便记录下之前折腾的一些东西。
在MySql中使用
声明json类型的字段:
- sql示例:
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字符串使用一种,例如:
- 默认值需要使用小括号包裹的符合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字符串的引号去掉
- 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;
cheap propecia without insurance: order propecia for sale – cost of cheap propecia without a prescription
order famvir 250mg generic – cheap valaciclovir 500mg valaciclovir 500mg cost
digoxin usa – purchase lasix generic order furosemide 40mg pill
http://canadaph24.pro/# canadian pharmacy ed medications
http://mexicoph24.life/# pharmacies in mexico that ship to usa
top online pharmacy india https://indiaph24.store/# online shopping pharmacy india
world pharmacy india
top online pharmacy india Cheapest online pharmacy indian pharmacy paypal
https://canadaph24.pro/# canadian pharmacy near me
india pharmacy [url=http://indiaph24.store/#]buy medicines from India[/url] cheapest online pharmacy india
снять квартиру в Минске на сутки https://sutki24.by/ .
mexican online pharmacies prescription drugs: cheapest mexico drugs – п»їbest mexican online pharmacies
http://mexicoph24.life/# buying from online mexican pharmacy
online pharmacy india https://indiaph24.store/# Online medicine order
top 10 online pharmacy in india
world pharmacy india: buy medicines from India – india pharmacy mail order
mexican drugstore online: Mexican Pharmacy Online – п»їbest mexican online pharmacies
http://indiaph24.store/# india pharmacy
indian pharmacy paypal [url=http://indiaph24.store/#]Generic Medicine India to USA[/url] indian pharmacies safe
https://mexicoph24.life/# reputable mexican pharmacies online
https://indiaph24.store/# india online pharmacy
mexico pharmacy [url=http://mexicoph24.life/#]mexico pharmacy[/url] pharmacies in mexico that ship to usa
http://indiaph24.store/# best india pharmacy
mexico pharmacy [url=https://mexicoph24.life/#]mexico pharmacy[/url] mexican pharmacy
buying prescription drugs in mexico: mexico pharmacy – medication from mexico pharmacy
http://canadaph24.pro/# online canadian pharmacy
cheapest online pharmacy india [url=http://indiaph24.store/#]indian pharmacy fast delivery[/url] india online pharmacy
http://indiaph24.store/# buy medicines online in india
http://indiaph24.store/# top 10 pharmacies in india
mexico drug stores pharmacies [url=http://mexicoph24.life/#]Online Pharmacies in Mexico[/url] mexico pharmacies prescription drugs
https://mexicoph24.life/# mexico drug stores pharmacies