[mysql] JSON数据操作

/ 37,024评论 / 148635阅读 / 0点赞

前言

在MySql中使用

声明json类型的字段

create table test(
    uids json not null default ('[]')
);
default ('[{"name":"coolight"}, {"name","洛天依"}]')
或
default ("[{'name':'coolight'}, {'name','洛天依'}]")

Mysql中常用的操作json函数

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);
// 示例:在数组末尾追加元素
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判断了是否包含准备添加的元素
// ["1000000", "1000001", "1000002"]
update test set uids=json_remove(uids, JSON_UNQUOTE(JSON_SEARCH(uids, 'one','1000001'))) where id=777;
// ["1000000", "1000002"]
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;
  1. porn videos说道:

    Right away I am going away to do my breakfast, afterward having my breakfast coming yet again to read
    further news.

  2. Tylerreunk说道:

    https://levitrav.store/# buy Levitra over the counter

  3. Tylerreunk说道:

    http://kamagra.win/# Kamagra 100mg price

  4. Jamesexite说道:

    Generic Viagra online: Cheap Viagra 100mg – Viagra online price

  5. Elmersoalf说道:

    buy cenforce order cenforce Purchase Cenforce Online

  6. Charlesvox说道:

    http://levitrav.store/# buy Levitra over the counter

  7. Elmersoalf说道:

    Generic Cialis without a doctor prescription [url=https://cialist.pro/#]Generic Tadalafil 20mg price[/url] buy cialis pill

  8. there was a car. It was a Honda Civic and the reason he was giving it to me was it was hot.” Younger guys who steal. “

  9. Charlesvox说道:

    https://cialist.pro/# Buy Tadalafil 20mg

  10. Jamesexite说道:

    cenforce.pro: cenforce.pro – buy cenforce

  11. Georgefrege说道:

    Buy Tadalafil 10mg: cialist.pro – Buy Cialis online

  12. ラブドール说道:

    Several of Irontech’s configurations are all silicone,ラブドール 中古 while others supply our chosen mix: a silicone head on a TPE human body.

  13. All of our dolls can be totally customised and upgradedえろ 人形 just the best way you prefer it. We could supply any doll you want,

  14. DavidBiolo说道:

    http://levitrav.store/# Levitra 10 mg buy online

  15. Elmersoalf说道:

    buy viagra here viagra canada Viagra Tablet price

  16. Jamesexite说道:

    Cialis 20mg price in USA: buy cialis overseas – Generic Tadalafil 20mg price

  17. Georgefrege说道:

    Order Viagra 50 mg online: Buy Viagra online cheap – best price for viagra 100mg

  18. Elmersoalf说道:

    Generic Viagra online [url=http://viagras.online/#]Buy generic 100mg Viagra online[/url] Cheap generic Viagra

  19. Fpjhxs说道:

    cost nitroglycerin – purchase catapres valsartan 80mg us

  20. DavidBiolo说道:

    https://viagras.online/# sildenafil 50 mg price

  21. Charlesvox说道:

    http://levitrav.store/# Levitra 20 mg for sale

  22. Charlesvox说道:

    https://levitrav.store/# Generic Levitra 20mg

  23. Jamesexite说道:

    Vardenafil buy online: Levitra generic price – Vardenafil online prescription

  24. CharlesMag说道:

    https://lisinopril.network/# lisinopril 20 tablet
    does tamoxifen cause menopause [url=https://nolvadex.life/#]tamoxifen cyp2d6[/url] tamoxifen brand name

  25. RandallGuesE说道:

    lisinopril 20mg tablets cost: lisinopril 5 mg price – lisinopril 10 mg best price

  26. Davidfrify说道:

    cost propecia prices cost of propecia without a prescription get propecia tablets

  27. Davidfrify说道:

    cost generic propecia without dr prescription [url=http://finasteride.store/#]cost of generic propecia without dr prescription[/url] order cheap propecia prices

  28. RandallGuesE说道:

    buy misoprostol over the counter: buy cytotec over the counter – buy cytotec

  29. WilliamRen说道:

    https://lisinopril.network/# buy lisinopril online uk

  30. WilliamRen说道:

    https://finasteride.store/# cost generic propecia for sale

  31. Davidfrify说道:

    Misoprostol 200 mg buy online buy cytotec over the counter buy cytotec pills

  32. RandallGuesE说道:

    buy ciprofloxacin: purchase cipro – buy cipro

  33. RonaldSwobe说道:

    https://nolvadex.life/# tamoxifen dosage

  34. Davidfrify说道:

    order cheap propecia no prescription [url=https://finasteride.store/#]cost generic propecia without insurance[/url] get propecia

  35. RonaldSwobe说道:

    https://cytotec.club/# cytotec online

  36. RandallGuesE说道:

    nolvadex price: tamoxifen and osteoporosis – tamoxifen for breast cancer prevention

  37. WilliamRen说道:

    http://nolvadex.life/# tamoxifen for gynecomastia reviews

  38. Davidfrify说道:

    tamoxifen nolvadex 20mg tamoxifen and ovarian cancer

  39. Gbooue说道:

    lopressor ca – olmesartan 20mg for sale buy adalat

  40. leading to a discussion about the impact of ラブドール sexthese standards on individual self-esteem and body image.

  41. RandallGuesE说道:

    tamoxifen and grapefruit: tamoxifen joint pain – nolvadex gynecomastia

  42. However, as I hit the world of college, オナドールI was a thousand miles plus an ocean away from my mother’s watchful eye and protection.

  43. CharlesMag说道:

    http://ciprofloxacin.tech/# buy cipro cheap
    tamoxifen blood clots [url=https://nolvadex.life/#]tamoxifen hip pain[/url] tamoxifen cancer

  44. RandallGuesE说道:

    where can i buy cipro online: ciprofloxacin order online – buy ciprofloxacin over the counter

  45. WilliamRen说道:

    http://cytotec.club/# cytotec online

  46. WilliamRen说道:

    http://nolvadex.life/# tamoxifen alternatives

  47. Davidfrify说道:

    cytotec buy online usa cytotec pills buy online order cytotec online

  48. Free sex说道:

    He is good at eating pickles and telling women about his emotional problems.

回复 Georgefrege 取消回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注