目录
前言
- 环境:
- 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;
https://ciprofloxacin.tech/# cipro
lisinopril 20g zestril cost price lisinopril tablets india
buy cytotec online: buy cytotec in usa – buy cytotec over the counter
http://lisinopril.network/# lisinopril generic drug
nolvadex pct [url=https://nolvadex.life/#]tamoxifen brand name[/url] tamoxifen generic
https://lisinopril.network/# lisinopril generic
buy cipro online canada [url=http://ciprofloxacin.tech/#]ciprofloxacin over the counter[/url] buy generic ciprofloxacin
cost propecia for sale: cheap propecia without insurance – cost generic propecia no prescription
How a few years the urdolls doll can be employed depends on ドール エロthe situation of use and care. Sex dolls that are frequently used and moved are more liable to have on and tear.
buying propecia without prescription order cheap propecia pills cost cheap propecia without prescription
http://ciprofloxacin.tech/# cipro
http://lisinopril.network/# zestril 5mg price in india
tamoxifen breast cancer prevention [url=https://nolvadex.life/#]tamoxifen alternatives[/url] is nolvadex legal
https://nolvadex.life/# tamoxifen and antidepressants
cipro online no prescription in the usa: cipro generic – cipro for sale
п»їcipro generic cipro 500mg best prices antibiotics cipro
tamoxifen medication: tamoxifen rash pictures – where to buy nolvadex
http://lisinopril.network/# lisinopril 12.5
buy cipro [url=https://ciprofloxacin.tech/#]ciprofloxacin generic[/url] buy cipro cheap
cost of lisinopril in mexico [url=https://lisinopril.network/#]lisinopril sale[/url] lisinopril cheap brand
http://lisinopril.network/# lisinopril 20 mg price in india
http://cytotec.club/# buy cytotec online
http://lisinopril.network/# best generic lisinopril
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