Massiivi sarnaste andmete tõhus haldamine MySQL-is JSON‑tüübi abil

1. Sissejuhatus

MySQL-is massiivi-muundatud andmete haldamise vajadus

Andmebaasides salvestatakse andmed tavaliselt relatsiooniliseks kujunduseks. Kuid rakenduse nõudmiste põhjal võib olla kasulik salvestada mitut väärtust ühte veergu. Sellisel juhul aitab andmestruktuur nagu “massiiv”.

• Salvestada kasutaja valitud mitu märksõna.
• Salvestada mitu pildi URL-i toote jaoks.
• Koguda ajalugu või logisid ühte välja.

JSON-tüübi kasutamise eelised

MySQL ei paku otsest “massiiiv-tüüpi”, kuid JSON-tüübi kasutades saab hallata massiiv-muundatud andmeid. JSON-tüüp on väga paindlik ja pakub järgmisi eeliseid:

• Toetab sisseehitatud andmestruktuure.
• Luba andmete manipuleerimist otse päringutes.
• Halda mitmeid andmeformaatid ühes väljas.

Selles artiklis tutvustame, kuidas tõhusalt hallata massiiv-muundatud andmeid MySQL-is kasutades JSON-tüüpi.

2. Massiivide haldamise põhitõed MySQL-i JSON-tüübi abil

Mis on JSON-tüüp?

JSON (JavaScript Object Notation) on kerge ja lihtne andmevahetusvorming. MySQL toetab 5.7 versioonist lähtudes põhi JSON-tüüpi, mis võimaldab JSON-vormingus andmeid otse andmebaasis salvestada ja töödelda.

Näide: järgnevad andmed on salvestatavad JSON-tüübi väli:

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

JSON-tüübi eelised ja kasutusjuhtumid

Peamised eelised JSON-tüübi kasutamisel:

1. Paindlik andmestruktuur : Saate käsitleda muutuja pikkusega andmeid, ilma et peaksite relatsioonilist skeemi muutma.
2. Tõhusandmete manipulatsioon : Kasutage MySQL-i sisseehitatud funktsioone (nt JSON_EXTRACT, JSON_ARRAY) andmete hõlpsaks manipuleerimiseks.
3. Skeemivaba disain võimalik : Te ei pea skeemi sagedamini muutma, sest rakenduse spetsiifikad muutuvad.

Kasutusjuhtumid:

• Määrata tooteinfole mitu kategooriat.
• Salvestada kohandatud kasutaja seaded.
• Kasutada veebirakendustes sisseehitatud JSON-andmete töötlemisel.

3. JSON-massiivide põhitegevused

JSON-massiivide loomine

MySQL-is võite kasutada JSON_ARRAY funktsiooni, et hõlpsasti luua JSON-vormingus massiive. Massiivid on kasulikud, kui soovite salvestada mitu väärtust ühe veeru sisse.

Näide kasutamisest

Järgmises päringus loome JSON-massiivi nimega tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Tulemus:

["PHP", "MySQL", "JavaScript"]

Rakendatud näide

Siin on näide JSON-massiivi andmebaasi salvestamisest INSERT avaldise kaudu.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Andmete tõmbamine JSON-massiividest

JSON-massiivist salvestatud andmete hankimiseks kasutate JSON_EXTRACT funktsiooni. See võimaldab välja võtta spetsiifilisi elemente massiivist.

Näide kasutamisest

Järgmises näites tõmbame massiivi teist elementi (0-põhine indeks).

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Tulemus:

"MySQL"

Tõmba mitu elementi

Saate ka mitme elemendi korraga saada.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Lisa, uuenda ja kustuta andmeid

Andmete lisamine massiivi

JSON_ARRAY_APPEND funktsiooni kasutades saate lisada uue andme olemasolevale massiivile.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Tulemus:

["PHP", "MySQL", "JavaScript"]

Andmete uuendamine massiivis

Saate konkreetse elemendi massiivis uuendada kasutades JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Tulemus:

["PHP", "Python", "JavaScript"]

Andmete eemaldamine massiivist

Kasutage JSON_REMOVE, et kustutada konkreetne element massiivis.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Tulemus:

["PHP", "JavaScript"]

4. JSON-massiivide otsimine ja filtreerimine

Otsing massiividest, mis sisaldavad konkreetseid andmeid

Et teha kindlaks, kas JSON-massiiv sisaldab konkreetset väärtust, kasutate JSON_CONTAINS funktsiooni. See funktsioon tagastab, kas määratud JSON-massiiv sisaldab antud väärtust.

Näidis kasutus

Järgmisel näites kontrollime, kas JSON-massiiv sisaldab „MySQL”.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Tulemus:

1  (if present)
0  (if not present)

Rakendatud näide: tingimuslik otsing

Kui soovite pärida andmebaasist ridu, mis sisaldavad JSON-massiivi koos konkreetse väärtusega, võite kasutada JSON_CONTAINS funktsiooni WHERE klause sees.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

See päring tõmbab ridu, kus veerg tags sisaldab „MySQL”.

Massiivi pikkuse leidmine

JSON-massiivi elementide arvu saamiseks kasutage JSON_LENGTH funktsiooni. See on kasulik andmete analüüsi või tingimusliku filtriga.

Näidis kasutus

Järgnevas näites saame massiivi elementide arvu.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Tulemus:

3

Praktiline näide: ridade välja tõmbamine, mis vastavad tingimusele

Et tõmmata ridu, kus elementide arv on võrdne või suurem kui teatud väärtus, kasutage JSON_LENGTH funktsiooni WHERE klause sees.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

See päring valib read, kus veerg tags sisaldab kaks või rohkem elementi.

Praktiline näide tingimuslike päringutega

Saate kombineerida mitu tingimust keerukamate otsingute jaoks. Järgnevas päringus leitakse read, kus tags massiiv sisaldab „JavaScript” ja millel on kolm või rohkem elementi.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Praktilised kasutusjuhtud: õpe tegelike stsenaariumitega JSON-massiivide kohta

Kuidas salvestada toodete kategooriaid JSON-massiivina

E-kaubanduse saitidel võivad tooted kuuluda mitmesse kategooriasse. Sellistes olukordades võib kategooriaandmete salvestamiseks JSON-massiivi kasutamine olla tõhus.

Näidis: kategooriaandmete salvestamine toodetele

Allpool on näide tabeli loomisel, millel on JSON veerg nimega categories ja kus salvestatakse mitu kategooriat.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

See andmestruktuur võimaldab lühikese salvestamise ka siis, kui toode kuulub mitmesse kategooriasse.

Päring tooteid, mis kuuluvad konkreetse kategooriasse

Kasutades JSON tüüpi, saate hõlpsasti otsida tooteid, mis kuuluvad konkreetsetesse kategooriatesse.

Päringu näide

Järgnevas päringus leitakse kõik tooted kategoorias „Elektronikad”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Tulemus:

Laptop
Smartphone

See päring võimaldab paindlikult saada toodete loendeid kategooria järgi.

Näidis: filtreerimine hindade vahemiku järgi

Vaatame, kuidas salvestada hinnatõmmist JSON-is ja pärida tooteid vastavalt hindade vahemikule.

Andme näidis

Allpool salvestame toote hindade teavet üksiku kauba kohta, kasutades JSON tüüpi.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Päringu näide

To otsida tooteid, mille hind on 100 000 või rohkem, kasutate JSON_EXTRACT.

Tulemus:

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Tulemus:

Laptop

JSON_TABLEi kasutamine laiendamiseks ja päringute näidiste jaoks

Näidis kasutus

Allpool on näidis, kus JSON massiiv laieneb ja iga kategooria kuvatakse eraldi read.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Tulemus:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Hoiatused JSON tüübi kasutamisel

Jõudluse optimeerimise punktid

Kuigi JSON tüüp on väga paindlik, võib ilma korraliku disainita andmebaasi jõudlust negatiivselt mõjutada. Allpool on peamised jõudluse optimeerimise punktid.

1. Indeksite kasutamine

MySQLis ei saa otse indeksi JSON veergu määrata, kuid saate luua virtuaalse veeru ja indekseerida kindlat võtit.

Näidis: indeksi loomine virtuaalse veeru kaudu

Selles näites indetseerime price võtme JSON-andmetes.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Virtuaalse veeru kasutamisega saate oluliselt parandada JSON‑tüübi andmete otsingu jõudlust.

2. Vältige liiga keerulisi JSON struktuure

Sügavalt omavahel seotud JSON‑struktuurid mõjutavad päringute loetavust ja jõudlust. Andmete kavandamisel valige võimalikult lihtne JSON‑struktuur.

Hea näide:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Struktuur, mida vältida:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Kuidas indeksit tõhusalt kasutada

Virtuaalse veeru kasutamisel indeksi loomiseks tuleb arvestada järgmiste punktidega:

1. Virtuaalne veerg peab olema STORED .
2. Kasutage JSON_EXTRACT‑i, et eraldada konkreetseid võtmeid virtuaalse veergu.

Näiteks, et eraldada categories võtme väärtus ja selle indekseerida:

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

Andmete valideerimise tähtsus

Kuigi JSON‑tüüp on paindlik, riskeerib ka vale vormingu salvestamist. Andmete terviklikkuse säilitamiseks kasutage järgmisi lähenemisi.

1. Kasutage CHECK piiranguid

Alates MySQL 8.0‑st võite kasutada CHECK piiranguid JSON‑andmete struktuuri või sisu valideerimiseks.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Valideerimine rakenduse tasandil

Andmete lisamisel tuleks JSON‑vormingut rakenduse tasandil valideerida. Programmeerimiskeeltes nagu PHP või Python saate kasutada standardraamatukogusid JSON‑i valideerimiseks.

7. Kõige sagedamini esitatud küsimused array‑stiilis andmete kasutamisest MySQL-is

K1: Kas MySQL pakub massiivi tüüpi?

A1: MySQLil pole otsest „massiivi tüüpi“. Küll aga saate massiivi stiilis andmeid hallata JSON‑tüübi kasutades. JSON‑tüübi abil saate salvestada mitu väärtust ühte veergu ja manipuleerida nende kaudu päringuid.

Näide:

SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;

Tulemus:

["value1", "value2", "value3"]

K2: Kas saate JSON‑tüüpi andmetele indeksi määrata?

A2: Ei saa JSON‑tüüpi andmetele otse indeksi määrata. Küll aga saate eraldada konkreetseid võtmeid või väärtusi virtuaalse veeru ja seejärel seada indeks selle veeru peale.

Näide:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

See võimaldab tõhusat otsingut JSON‑andmete sisemiste väärtuste jaoks.

Q3: Kas JSON andmete jaoks on suuruse piir?

A3: MySQL‑i JSON‑tüüp saab taluda kuni 4 GB andmeid. Kuid väga suurte JSON‑andmete kasutamine võib jõudlust halvendada, seega on õige andmeprojekt oluline.

Soovitused

• Säilita ainult minimaalne vajalik andme.
• Vältida sügavalt sisaratud JSON‑struktuure.

Q4: Kuidas uuendada konkreetset elementi JSON‑massiivis?

A4: Saad konkreetseid elemente JSON‑massiivi sees uuendada funktsiooniga JSON_SET.

Example:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Result:

["PHP", "Python", "JavaScript"]

Q5: Võrdlus JSON‑tüübi ja traditsioonilise tabeli disaini vahel

A5: JSON‑tüüp pakub kõrget paindlikkust, kuid erineb ka loomupärasest suheloomist.

8. Kokkuvõte

JSON‑tüübi kasutamise eelised massiivi‑seda tüüpi andmete töötlemisel MySQL‑is

Selles artiklissa käsitletakse JSON‑tüüpi kasutamist massiivi‑seda tüüpi andmete töötlemiseks MySQL‑is. Peamised punktid on järgmised:

1. Miks kasutada JSON‑tüüpi
   MySQL‑il puudub otsene massiivi tüüp, kuid JSON‑tüübi kasutades saad salvestada mitmeid väärtusi ühte veergu ja teha paindlikke andmeoperatsioone.

2. Põhilised JSON‑operatsioonid
   * Õppisid, kuidas luua JSON‑massive, andmeid ekstraktida, väärtusi uuendada ja kustutada.
   * Kasutasid funktsioone nagu JSON_ARRAY, JSON_EXTRACT ja JSON_SET, et tõhusalt manipuleerida massiivi‑seda tüüpi andmeid.

3. Otsing ja filtreerimine
   * Kasutasid JSON_CONTAINS, et otsida konkreetseid väärtusi JSON‑massivides.
   * Kasutasid JSON_LENGTH, et saada massiivi pikkus ja teha tingimuslikku filtreerimist.

4. Praktilised kasutusjuhtud
   Saad teada konkreetseid rakendusjuhtumeid, näiteks toote kategooriate haldamist ja hindade järgi filtreerimist.

5. Hoiatused ja optimeerimine
   * Arutati JSON‑andmete indekseerimist virtuaalveergude kaudu ja JSON‑andmete valideerimise tähtsust.

Järgmised sammud JSON‑tüübi kasutamiseks

JSON‑tüübi kasutamisel MySQL‑is saad võimaldada paindlikumat andmehalduse kui traditsiooniline relatsiooniline andmebaasi disain. Kuid sobiv disain ja jõudluse kaalutlused on hädavajalikud.

Järgmised õppe teemad

• Komposiit‑indekside kasutamine
  Indeksite kavandamine, mis ühendavad JSON‑tüüpi veerge ja tavalisi veerge.
• Täpsemad JSON‑funktsioonid
  Kasutades JSON_MERGE, JSON_OBJECT ja muid funktsioone keerukamate operatsioonide jaoks.
• Rakendustaseme andmete manipuleerimine
  MySQL JSON‑andmete tõhus manipuleerimine PHP‑i või Pythoni kaudu.

Lõplik kokkuvõte

Selle artikliga oled õppinud, kuidas tõhusalt käsitleda massiivi‑seda tüüpi andmeid JSON‑tüübi abil MySQL‑is. Rakendades seda teadmist, saad disainida paindlikumaid ja skaleeritavamaid andmebaasijärjestusi.