[mysql] JSON数据操作

/ 20,263评论 / 83159阅读 / 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. RickyAlage说道:

    canadian king pharmacy [url=http://canadaph24.pro/#]Large Selection of Medications from Canada[/url] onlinepharmaciescanada com

  2. StevenSen说道:

    online shopping pharmacy india: Generic Medicine India to USA – online pharmacy india

  3. Michaelsew说道:

    https://indiaph24.store/# india pharmacy

  4. Marcelcrolf说道:

    https://canadaph24.pro/# reliable canadian pharmacy

  5. RickyAlage说道:

    online pharmacy india [url=http://indiaph24.store/#]top 10 pharmacies in india[/url] buy medicines online in india

  6. Marcelcrolf说道:

    https://canadaph24.pro/# global pharmacy canada

  7. RickyAlage说道:

    indian pharmacy online [url=https://indiaph24.store/#]indian pharmacy fast delivery[/url] world pharmacy india

  8. Michaelsew说道:

    https://indiaph24.store/# indian pharmacy online

  9. Marcelcrolf说道:

    https://canadaph24.pro/# canadian pharmacy 1 internet online drugstore

  10. StevenSen说道:

    india pharmacy: indian pharmacy fast delivery – online pharmacy india

  11. RickyAlage说道:

    indian pharmacy paypal [url=http://indiaph24.store/#]Cheapest online pharmacy[/url] top 10 online pharmacy in india

  12. Marcelcrolf说道:

    http://indiaph24.store/# reputable indian pharmacies

  13. RickyAlage说道:

    online shopping pharmacy india [url=https://indiaph24.store/#]Cheapest online pharmacy[/url] п»їlegitimate online pharmacies india

  14. Marcelcrolf说道:

    http://mexicoph24.life/# mexico drug stores pharmacies

  15. You can find little exploration in the motivations or life of dollエロ 人形 homeowners.3 But should you go through the way in which the abstracts are created in what few papers exist

  16. RickyAlage说道:

    canadian drugstore online [url=http://canadaph24.pro/#]Large Selection of Medications from Canada[/url] canada drugs online reviews

  17. Michaelsew说道:

    https://indiaph24.store/# online shopping pharmacy india

  18. Marcelcrolf说道:

    http://indiaph24.store/# cheapest online pharmacy india

  19. RickyAlage说道:

    canadian pharmacy victoza [url=http://canadaph24.pro/#]Prescription Drugs from Canada[/url] reputable canadian online pharmacies

  20. StevenSen说道:

    online canadian pharmacy: Licensed Canadian Pharmacy – best canadian pharmacy online

  21. Marcelcrolf说道:

    https://mexicoph24.life/# mexican online pharmacies prescription drugs

  22. RickyAlage说道:

    reliable canadian pharmacy [url=https://canadaph24.pro/#]canadian pharmacies[/url] onlinecanadianpharmacy 24

  23. Michaelsew说道:

    http://mexicoph24.life/# reputable mexican pharmacies online

  24. Marcelcrolf说道:

    https://canadaph24.pro/# legit canadian pharmacy

  25. RickyAlage说道:

    purple pharmacy mexico price list [url=http://mexicoph24.life/#]cheapest mexico drugs[/url] mexican border pharmacies shipping to usa

  26. StevenSen说道:

    best online pharmacy india: buy medicines from India – top online pharmacy india

  27. LarryGok说道:

    PBN sites
    We shall build a system of self-owned blog network sites!

    Pros of our self-owned blog network:

    We execute everything so google doesn’t comprehend that this A private blog network!!!

    1- We acquire domains from distinct registrars

    2- The primary site is hosted on a VPS hosting (Virtual Private Server is high-speed hosting)

    3- The remaining sites are on separate hostings

    4- We assign a individual Google ID to each site with confirmation in Google Search Console.

    5- We make websites on WP, we don’t utilise plugins with assistance from which Trojans penetrate and through which pages on your websites are created.

    6- We do not repeat templates and utilize only individual text and pictures

    We never work with website design; the client, if wanted, can then edit the websites to suit his wishes

  28. Marcelcrolf说道:

    http://mexicoph24.life/# mexican online pharmacies prescription drugs

  29. RickyAlage说道:

    mexico pharmacies prescription drugs [url=https://mexicoph24.life/#]mexico pharmacy[/url] medicine in mexico pharmacies

  30. Michaelsew说道:

    https://indiaph24.store/# online pharmacy india

  31. edctoto adalah situs online gacor terpercaya yang menyediakan taruhan togel terbaik dan berbagai permainan slot gacor dengan rtp tertinggi hari ini.

  32. Marcelcrolf说道:

    https://indiaph24.store/# india pharmacy

  33. RickyAlage说道:

    п»їlegitimate online pharmacies india [url=https://indiaph24.store/#]buy medicines from India[/url] mail order pharmacy india

  34. Marcelcrolf说道:

    http://mexicoph24.life/# mexico pharmacy

  35. Michaelsew说道:

    https://indiaph24.store/# best online pharmacy india

  36. Marcelcrolf说道:

    https://indiaph24.store/# reputable indian online pharmacy

  37. RickyAlage说道:

    best india pharmacy [url=http://indiaph24.store/#]indian pharmacy[/url] online pharmacy india

  38. LarryGok说道:

    PBN sites
    We will create a network of private blog network sites!

    Merits of our privately-owned blog network:

    WE DO everything so google does not grasp that this is A PBN network!!!

    1- We buy domain names from various registrars

    2- The primary site is hosted on a VPS hosting (Virtual Private Server is rapid hosting)

    3- Other sites are on distinct hostings

    4- We attribute a separate Google account to each site with verification in Search Console.

    5- We design websites on WP, we don’t use plugins with assistance from which malware penetrate and through which pages on your websites are produced.

    6- We never duplicate templates and utilise only exclusive text and pictures

    We don’t work with website design; the client, if desired, can then edit the websites to suit his wishes

  39. Marcelcrolf说道:

    https://indiaph24.store/# indian pharmacies safe

  40. RickyAlage说道:

    canadian pharmacy online ship to usa [url=https://canadaph24.pro/#]legitimate canadian online pharmacies[/url] global pharmacy canada

发表回复

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