Kaip naudoti JSON duomenų laukus „MySQL“ duomenų bazėse – „SitePoint“.

Mano straipsnyje „SQL vs NoSQL: skirtumai“ pažymėjo, kad riba tarp SQL ir NoSQL duomenų bazių vis labiau neryški, kiekviena stovykla perima kitas funkcijas. „MySQL 5.7+ InnoDB“ duomenų bazės ir „PostgreSQL 9.2“ ir naujesnės versijos viename lauke tiesiogiai palaiko JSON dokumentų tipus. Šiame straipsnyje bus išsamiau išnagrinėtas MySQL 9.1 JSON diegimas.

Atminkite, kad bet kuri duomenų bazė priims JSON dokumentus kaip vienos eilutės bloką. Tačiau „MySQL“ ir „PostgreSQL“ palaiko patvirtintus JSON duomenis realiomis raktų / reikšmių poromis, o ne pagrindine eilute.

Key Takeaways

• JSON dokumentų tipai tiesiogiai palaikomi „MySQL 5.7+ InnoDB“ duomenų bazėse ir „PostgreSQL 9.2+“, tačiau juos reikia naudoti protingai dėl tiesioginio indeksavimo apribojimų.
• JSON geriausiai tinka retai užpildytiems duomenims, pasirinktiniams atributams, hierarchinėms struktūroms ir atvejams, kuriems reikia lankstumo. Jis neturėtų pakeisti normalizuotų stulpelių, skirtų dažnai užklausoms arba indeksuojamiems duomenims.
• „MySQL“ siūlo įvairias funkcijas, skirtas kurti, patvirtinti, ieškoti ir modifikuoti JSON objektus. Tai apima JSON_ARRAY(), JSON_OBJECT(), JSON_QUOTE(), JSON_TYPE(), JSON_VALID(), JSON_CONTAINS(), JSON_SEARCH(), ir functions like JSON_SET() and JSON_MERGE_PATCH() JSON dokumentams atnaujinti naudojant kelio žymėjimą.
• „MySQL 9.1“ palaiko funkcinį sugeneruotų stulpelių, gautų iš JSON duomenų, indeksavimą, leidžiantį efektyviai pateikti konkrečių JSON elementų užklausas.
• Nors MySQL palaiko JSON, ji išlieka reliacinė duomenų bazė. Per didelis JSON naudojimas gali paneigti SQL pranašumus.

Tiesiog todėl, kad galite saugoti JSON dokumentus MySQL JSON stulpeliuose…

… tai nereiškia, kad turėtumėte.

Normalizavimas yra metodas, naudojamas duomenų bazės struktūrai optimizuoti. Pirmosios normalios formos (1NF) taisyklė reglamentuoja, kad kiekviename stulpelyje turi būti viena reikšmė, kuri aiškiai sulaužoma išsaugant kelių reikšmių JSON dokumentus.

Jei turite aiškius reliacinių duomenų reikalavimus, naudokite atitinkamus vienos reikšmės laukus. JSON turėtų būti naudojamas taupiai kaip paskutinė priemonė. JSON vertės laukų negalima indeksuoti tiesiogiai, todėl nenaudokite jų stulpeliuose, kurie atnaujinami arba reguliariai ieškoma.

Funkcinis sugeneruotų stulpelių, gautų iš JSON, indeksavimas leidžia indeksuoti JSON objekto dalis ir taip pagerinti užklausos našumą.

Be to, yra gerų JSON naudojimo atvejų retai užpildytiems duomenims arba pasirinktiniams atributams.

Sukurkite lentelę su JSON duomenų tipo stulpeliu

Apsvarstykite parduotuvę, kurioje parduodamos knygos. Visos knygos turi ID, ISBN, pavadinimą, leidėją, puslapių skaičių ir kitus aiškius santykinius duomenis.

Dabar, jei norite prie kiekvienos knygos pridėti bet kokį kategorijų žymų skaičių. Tai galite pasiekti naudodami SQL naudodami:

1. A žyma lentelė, kurioje saugomas kiekvienos žymos pavadinimas su unikaliu ID ir
2. A žymų žemėlapis lentelė su daugybe įrašų, susiejančių knygų ID su žymų ID

Tai veiks, bet tai yra sudėtinga ir reikalauja daug pastangų dėl nedidelės funkcijos. Todėl galite apibrėžti MySQL JSON lauką žymoms savo MySQL duomenų bazėje knyga lentelė:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

MySQL JSON stulpeliai negali turėti numatytosios vertės, negali būti naudojami kaip pirminis raktas, negali būti naudojami kaip išorinis raktas arba turi tiesioginius indeksus.

Tačiau su MySQL 9.1 galite kurti funkciniai indeksai įjungta sugeneruotų stulpelių gautas iš JSON duomenų, leidžiančių indeksuoti konkrečius elementus JSON dokumente. Šie sugeneruoti stulpeliai gali būti virtualūs arba saugomi ir indeksuojami kaip antriniai indeksai.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$(0)')),
ADD INDEX idx_first_tag (first_tag);

Pridedami JSON duomenys

Galima perduoti visus JSON dokumentus Įterpti arba ATNAUJINTI teiginius, kad būtų lengva perkelti JSON į MySQL saugojimui ir manipuliavimui.

Pavyzdžiui, mūsų knygų žymos gali būti perduodamos kaip masyvas (eilutės viduje):

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '("JavaScript", "ES2015", "JSON")'
);

JSON taip pat galima sukurti naudojant šiuos:

• JSON_ARRAY() funkcija, kuri sukuria masyvus. Pavyzdžiui:-- returns (1, 2, "abc"): SELECT JSON_ARRAY(1, 2, 'abc');
• JSON_OBJECT() funkcija, kuri sukuria objektus. Pavyzdžiui:-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
• JSON_QUOTE() funkcija, kuri nurodo eilutę kaip JSON reikšmę. Pavyzdžiui:-- returns "(1, 2, \"abc\")": SELECT JSON_QUOTE('(1, 2, "abc")');
• Funkcija CAST (anyValue AS JSON), kuri pateikia reikšmę kaip JSON tipą, kad būtų galima patikrinti:SELECT CAST('{"a": 1, "b": 2}' AS JSON);

Funkcija JSON_TYPE() leidžia patikrinti JSON reikšmių tipus. Jis turėtų grąžinti OBJEKTAS, MASĖLIS, skaliarinį tipą (INTEGER, BOOLEAN ir kt.), NULL arba klaidą. Pavyzdžiui:

SELECT JSON_TYPE('(1, 2, "abc")');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

Funkcija JSON_VALID() grąžina 1, jei JSON galioja, arba 0 kitu atveju:

SELECT JSON_TYPE('(1, 2, "abc")');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

Bandant įterpti netinkamą JSON dokumentą, atsiras klaida ir visas įrašas nebus įdėtas / atnaujintas.

JSON dokumentų paieška MySQL JSON stulpelyje

Naudodami JSON MySQL funkcijas, pvz., JSON_CONTAINS() funkciją, galite patikrinti, ar JSON dokumente yra konkreti reikšmė. Grąžina 1, kai randama atitiktis. Pavyzdžiui:

SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '("JavaScript")');

Funkcija JSON_SEARCH() grąžina kelią į reikšmę JSON dokumente. Jis grąžina NULL, kai nėra atitikties.

Taip pat galite nurodyti, ar reikia rasti visas ar pavienius atitikmenis, paleisdami žymeles „vienas“ ir „visi“ šalia paieškos eilutės (kur % atitinka bet kokį simbolių skaičių, o _ atitinka vieną simbolį identiškai LIKE). Pavyzdžiui:

SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

Funkcija JSON_TABLE() paverčia JSON duomenis į reliacinį formatą, kad būtų lengviau pateikti užklausas:

SELECT * 
FROM JSON_TABLE(
    '({"tag": "SQL"}, {"tag": "JSON"})', 
    '$(*)' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

JSON keliai

MySQL JSON užklausa naudojant funkciją JSON_EXTRACT() gali gauti konkrečias reikšmes iš JSON dokumento pagal nurodytą kelią.

SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

Visi kelio apibrėžimai prasideda simboliu $, po kurio seka kiti parinkikliai:

• Taškas, po kurio nurodomas pavadinimas, pvz., $.website
• (N) kur N yra padėtis nulinio indekso masyve
• .
• pakaitos simbolis įvertina visus objekto narius
• The

pakaitos simbolis įvertina visus masyvo narius

{
  "a": ,
  "b": 2,
  "c": (, ),
  "d": {
    "e": ,
    "f": 6
  }
}

Priešdėlis** priesaga pakaitos simbolis įvertina visus kelius, kurie prasideda pavadintu priešdėliu ir baigiasi pavadintu priesaga

• Šie pavyzdžiai nurodo šį JSON dokumentą:
• Kelių pavyzdžiai:
• $.a grąžina 1
• $.c grąža (3, 4)
• $.c(1) grąžina 4

$.de grąžina 5

SELECT
  title, tags->"$(0)" AS `tag1`
FROM `book`;

$**.e grąžinimas (5) Galite naudoti JSON išskleidimo MySQL funkciją, kad galėtumėte efektyviai ištraukti pavadinimą ir pirmąją žymą iš savo knygų lentelės: Sudėtingesniam pavyzdžiui tarkime, kad turite a

SitePoint

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

{ „el. paštas“: (), „twitter“: „@sitepointdotcom“ }

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

„Twitter“ pavadinimą galite išgauti naudodami JSON kelią. Pavyzdžiui:

Galite naudoti JSON kelią WHERE sąlygoje, kad grąžintumėte tik vartotojus, turinčius „Twitter“ paskyrą:

• JSON dokumento dalies keitimasYra keletas MySQL funkcijų, kurios modifikuoja JSON dokumento dalis naudojant kelio žymėjimą. Tai apima:UPDATE book SET tags = JSON_SET(tags, '$(0)', 'Updated Tag');
• JSON_SET(doc, kelias, val(, kelias, val)…): įterpia arba atnaujina duomenis dokumente.UPDATE book SET tags = JSON_INSERT(tags, '$(0)', 'New Tag');
• JSON_INSERT(doc, kelias, val(, kelias, val)…): įterpia duomenis į dokumentą neperrašant esamų reikšmių.UPDATE book SET tags = JSON_REPLACE(tags, '$(0)', 'Replaced Tag');
• JSON_REPLACE(doc, kelias, val(, kelias, val)…): pakeičia duomenis dokumente.UPDATE book SET tags = JSON_MERGE_PATCH(tags, '("technical")') WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;
• JSON_MERGE_PATCH(doc, doc(, doc)…): sujungia du ar daugiau JSON dokumentų, pakeičiant esamus raktus vertėmis iš vėlesnių dokumentų.UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');
• JSON_ARRAY_APPEND(doc, kelias, val(, kelias, val)…): prideda reikšmes prie masyvo pabaigos. JSON_ARRAY_INSERT(doc, kelias, val(, kelias, val)…)UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$(0)', 'Inserted Tag');
• :Įterpia reikšmes konkrečioje JSON masyvo vietoje.UPDATE book SET tags = JSON_REMOVE(tags, '$(1)');
• JSON_REMOVE(dokumentas, kelias (, kelias)…): pašalina duomenis iš dokumento.SELECT JSON_PRETTY('{"name": "SitePoint", "tags": ("MySQL", "JSON")}');

JSON_PRETTY(val)

UPDATE book
SET tags = JSON_MERGE_PATCH(tags, '("technical")')
WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

: gražiai spausdina JSON dokumentus, kad būtų geriau skaitoma.

Pavyzdžiui, jei norite pridėti „techninę“ žymą prie bet kurios knygos, kuri jau turi „JavaScript“ žymą, galite naudoti JSON_MERGE_PATCH() funkciją:

Daugiau informacijos

MySQL dokumentacijoje pateikiama išsami informacija apie MySQL JSON duomenų tipą ir susijusias JSON funkcijas.

Dar kartą raginu nenaudoti JSON, nebent tai absoliučiai būtina. Galite mėgdžioti visą į dokumentus orientuotą NoSQL duomenų bazę MySQL, tačiau tai paneigtų daugelį SQL pranašumų, o jūs taip pat galite pereiti prie tikros NoSQL sistemos!

Be to, JSON duomenų tipai gali sutaupyti pastangų, kad SQL programoje būtų taikomi neaiškesni duomenų reikalavimai.

DUK apie darbą su JSON duomenimis MySQL

Ar galite naudoti JSON „MySQL“?

„MySQL“ palaiko JSON, siūlydamas JSON duomenų tipą, skirtą JSON formatuotų duomenų saugojimui stulpeliuose. Pradedant nuo MySQL 5.7.8, galite kurti lenteles su JSON stulpeliais, leidžiančias įterpti, atnaujinti ir pateikti JSON duomenis naudojant SQL. „MySQL“ teikia daugybę JSON funkcijų, skirtų darbui su JSON duomenimis šiuose stulpeliuose ir leidžia išgauti, modifikuoti ir manipuliuoti.

Be to, SQL užklausose galite naudoti JSON duomenis, prireikus konvertuodami juos į reliacinius duomenis naudodami tokias funkcijas kaip JSON_TABLE. Tačiau svarbu suprasti, kad „MySQL“ iš esmės yra reliacinė duomenų bazė, o jos JSON duomenų tipo palaikymas yra skirtas palengvinti darbą su JSON duomenimis reliaciniame kontekste, o ne būti visaverte NoSQL JSON duomenų baze.

Kaip nurodyta aukščiau esančiame straipsnyje, vien todėl, kad galite saugoti JSON, nereiškia, kad turėtumėte: Normalizavimas yra duomenų bazės struktūros optimizavimo metodas. Pirmosios normalios formos (1NF) taisyklė reglamentuoja, kad kiekviename stulpelyje turi būti viena reikšmė, kuri sulaužoma išsaugant kelių reikšmių JSON dokumentus.

• Ar tinka JSON saugoti „MySQL“? Gerai saugoti JSON „MySQL“ tokiais atvejais kaip:
• Pusiau struktūrizuoti arba dinaminiai duomenys kuri netinka griežtoje schemoje.
• Pasirinktiniai atributai kur reliacinis dizainas būtų neefektyvus.

Integracija su JSON pagrindu veikiančiomis API naudingiems kroviniams ar rąstams laikyti. Tačiau JSON turėtų

ne

pakeisti normalizuotą reliacinę saugyklą struktūriniams ir dažnai užklausamiems duomenims. Nors „MySQL 9.1“ pagerina JSON funkcijas naudodami tokias funkcijas kaip funkciniai indeksai ir JSON_TABLE, JSON operacijos vis tiek gali sukelti didelių duomenų rinkinių ar sudėtingų užklausų papildomų išlaidų.

Kaip naudoti JSON „MySQL“ užklausoje?

JSON galite naudoti MySQL užklausose naudodami MySQL JSON funkcijas. Šios funkcijos leidžia išgauti JSON duomenis, saugomus JSON stulpeliuose arba JSON suformatuotose duomenų bazės eilutėse, juos apdoroti ir pateikti užklausas. Jei norite pasiekti JSON duomenis JSON stulpelyje, naudokite operatorių -> ir kelią į norimą JSON elementą.

JSON funkcijos, pvz., JSON_EXTRACT, JSON_SET ir JSON_OBJECTAGG, leidžia filtruoti, keisti, kaupti ir dirbti su JSON duomenimis. Taip pat galite filtruoti eilutes pagal JSON reikšmes naudodami WHERE sąlygą. MySQL JSON galimybės suteikia universalų būdą sąveikauti ir manipuliuoti JSON objektu tiesiogiai duomenų bazės užklausose.

1. Kada naudoti JSON MySQL?Turėtumėte naudoti JSON „MySQL“ šiais atvejais:
2. Pusiau struktūrizuoti duomenys: naudokite JSON, kai dirbate su nenuspėjamais arba retais laukais (pvz., pasirinktiniais atributais).
3. Dinaminės schemos: JSON suteikia lankstumo, kai duomenų reikalavimai dažnai keičiasi.
4. Hierarchiniai arba įdėtieji duomenys: JSON palaiko duomenis su tėvų ir antrinių ryšiais arba masyvais.

API integravimas

• : saugokite naudingus krovinius, atsakymus arba žurnalus kaip JSON dokumentus.
• Tačiau venkite JSON:
• Dažnai užklausiami laukai, kuriuos reikia indeksuoti (funkciniai indeksai gali padėti, tačiau reliacinis dizainas dažnai yra greitesnis).

Griežtai reliaciniai duomenys, kuriuos reikia normalizuoti.

Situacijos, kai sudėtingos užklausos JSON keliais pablogintų našumą.

Kaip saugoti JSON duomenis „MySQL“?

Jei norite saugoti JSON duomenis MySQL, turite dvi pagrindines parinktis. Pirma, galite naudoti JSON duomenų tipą, įvestą MySQL, kad sukurtumėte lentelę su JSON stulpeliu. Šis metodas suteikia struktūrizuotą saugyklą ir geresnį JSON duomenų užklausų našumą.

Arba galite saugoti JSON duomenis kaip tekstą įprastame stulpelyje VARCHAR arba TEXT. Šis metodas tinka, kai pirmiausia reikia saugoti ir gauti JSON duomenis neatliekant sudėtingų duomenų bazės operacijų. Kaip indeksuojate JSON duomenis „MySQL“? Nors negalite tiesiogiai indeksuoti JSON stulpelio, MySQL leidžia kurti

funkciniai indeksai

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$(0)')),
ADD INDEX idx_first_tag (first_tag);

sugeneruotuose stulpeliuose, gautuose iš JSON reikšmių.

Pavyzdžiui, norėdami indeksuoti pirmąjį JSON masyvo elementą:

Šis metodas pagerina dažnai pasiekiamų JSON kelių užklausos našumą.

• Ar JSON duomenims turėtumėte naudoti MySQL arba NoSQL duomenų bazę? Tai priklauso nuo jūsų projekto reikalavimų:
• Pasirinkite MySQL jei jums reikia reliacinės saugyklos su retkarčiais JSON tvarkymu pusiau struktūriniams duomenims, pasirinktiniams atributams ar hierarchiniams duomenims reliaciniame modelyje.

Pasirinkite NoSQL duomenų bazę

(pvz., MongoDB), jei jūsų projektas apima didelę JSON saugyklą, lanksčias schemas ir dokumentais pagrįstas operacijas kaip pagrindinį naudojimo atvejį.

MySQL JSON palaikymas puikiai tinka hibridiniams darbo krūviams, tačiau negali visiškai pakeisti specialiai sukurtos NoSQL duomenų bazės dokumentų saugojimui. Kaip iš MySQL JSON lauko išgauti konkrečias reikšmes?Norėdami išskirti konkrečias reikšmes iš a

SELECT JSON_EXTRACT(tags, '$(0)') AS first_tag FROM book;


SELECT tags->'$(0)' AS first_tag FROM book;

MySQL JSON laukas

naudokite funkciją JSON_EXTRACT() arba trumpinį -> operatorių. Kaip pateikti užklausas ir filtruoti duomenis MySQL JSON lauke?Norėdami pateikti užklausas ir filtruoti duomenis, saugomus a

SELECT * FROM book 
WHERE JSON_CONTAINS(tags, '("JavaScript")');


SELECT * FROM book 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;


SELECT * FROM book 
WHERE JSON_EXTRACT(tags, '$(0)') = 'JavaScript';