Come gestire i dati array in MySQL usando JSON (Guida completa con esempi)

1. Introduzione

La Necessità di Gestire Dati Array in MySQL

I database tipicamente memorizzano i dati basati su principi di design relazionale. Tuttavia, a seconda dei requisiti dell’applicazione, ci sono casi in cui potresti voler memorizzare più valori in una singola colonna. In tali situazioni, una struttura dati simile a un “array” diventa utile.

Ad esempio, considera i seguenti scenari:

• Memorizzare più tag selezionati da un utente.
• Salvare più URL di immagini per un prodotto.
• Combinare cronologia o log in un singolo campo.

Vantaggi dell’Uso del Tipo di Dati JSON

MySQL non fornisce un tipo “array” diretto, ma utilizzando il tipo di dati JSON, puoi gestire strutture dati simili a un array. Il tipo JSON è altamente flessibile e offre i seguenti vantaggi:

• Supporta strutture dati annidate.
• Consente una facile manipolazione dei dati all’interno delle query.
• Abilita la gestione di più formati di dati all’interno di un singolo campo.

In questo articolo, introdurremo come gestire efficientemente i dati array in MySQL utilizzando il tipo di dati JSON.

2. Conoscenze di Base sulla Gestione di Array con MySQL JSON

Cos’è il Tipo di Dati JSON?

JSON (JavaScript Object Notation) è un formato leggero e semplice per lo scambio di dati. In MySQL, il supporto nativo per JSON è stato introdotto nella versione 5.7 e successive, consentendo di memorizzare e manipolare dati formattati in JSON direttamente all’interno del database.

Esempio: Di seguito è riportato un esempio di dati che possono essere memorizzati in una colonna JSON.

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

Vantaggi e Casi d’Uso del Tipo di Dati JSON

I principali vantaggi dell’uso del tipo JSON sono i seguenti:

1. Struttura Dati Flessibile : Puoi gestire dati a lunghezza variabile senza modificare lo schema relazionale.
2. Manipolazione Dati Efficiente : Puoi manipolare facilmente i dati utilizzando le funzioni dedicate di MySQL (ad es., JSON_EXTRACT , JSON_ARRAY ).
3. Design Senza Schema : Non è necessario modificare frequentemente lo schema quando i requisiti dell’applicazione cambiano.

Esempi di casi d’uso:

• Assegnare più categorie alle informazioni di un prodotto.
• Salvare impostazioni utente personalizzate.
• Utilizzare dati JSON annidati in applicazioni web.

3. Operazioni di Base su Array JSON

Creazione di un Array JSON

In MySQL, puoi creare facilmente un array JSON utilizzando la funzione JSON_ARRAY. Gli array sono utili quando si memorizzano più valori in una singola colonna.

Esempio

La seguente query crea un array JSON chiamato tags.

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

Risultato:

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

Esempio Pratico

L’esempio seguente mostra come memorizzare un array JSON nel database utilizzando un’istruzione INSERT.

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

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

Estrazione Dati da un Array JSON

Per recuperare i dati memorizzati in un array JSON, utilizza la funzione JSON_EXTRACT. Questa funzione ti consente di estrarre facilmente elementi specifici dall’array.

Esempio

L’esempio seguente recupera il secondo elemento nell’array (l’indice parte da 0).

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

Risultato:

"MySQL"

Recupero di Più Elementi

Puoi anche recuperare più elementi contemporaneamente.

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

Aggiunta, Aggiornamento e Rimozione Dati

Aggiunta Dati a un Array

Puoi utilizzare la funzione JSON_ARRAY_APPEND per aggiungere nuovi dati a un array esistente.

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

Risultato:

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

Aggiornamento Dati in un Array

Puoi aggiornare un elemento specifico nell’array utilizzando la funzione JSON_SET.

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

Risultato:

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

Rimozione di Dati da un Array

Puoi rimuovere un elemento specifico dall’array usando la funzione JSON_REMOVE.

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

Risultato:

["PHP", "JavaScript"]

4. Ricerca e Filtraggio di Array JSON

Ricerca di Array che Contengono Dati Specifici

Per verificare se un array JSON contiene dati specifici, usa la funzione JSON_CONTAINS. Questa funzione determina se un valore specificato esiste all’interno dell’array JSON.

Esempio

Il seguente esempio verifica se l’array JSON contiene “MySQL”.

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

Risultato:

1  (if present)
0  (if not present)

Esempio Pratico: Ricerca Condizionale

Per cercare righe che contengono un valore specifico in un array JSON all’interno di una tabella del database, usa JSON_CONTAINS nella clausola WHERE.

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

Questa query recupera le righe in cui la colonna tags contiene “MySQL”.

Ottenere la Lunghezza di un Array

Per ottenere il numero di elementi in un array JSON, usa la funzione JSON_LENGTH. Questa funzione restituisce il numero di elementi nell’array ed è utile per l’analisi dei dati e la logica condizionale.

Esempio

Il seguente esempio recupera il numero di elementi nell’array.

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

Risultato:

3

Esempio Pratico: Estrarre Righe Che Soddisfano una Condizione Specifica

Per estrarre le righe in cui il numero di elementi è maggiore o uguale a un valore specifico, usa JSON_LENGTH nella clausola WHERE.

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

Questa query recupera le righe in cui la colonna tags contiene due o più elementi.

Esempio Avanzato di Query Condizionale

Puoi combinare più condizioni per ricerche più avanzate. La query seguente cerca le righe in cui l’array tags contiene “JavaScript” e ha tre o più elementi.

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

5. Esempi Pratici: Uso di Array JSON in Casi Reali

Come Memorizzare le Categorie di Prodotto come un Array JSON

Nei siti di e‑commerce e sistemi simili, un prodotto può appartenere a più categorie. In questi casi, puoi memorizzare efficientemente le informazioni delle categorie usando un array JSON.

Esempio: Memorizzare i Dati delle Categorie di Prodotto

Di seguito è un esempio di creazione di una colonna JSON chiamata categories in una tabella di prodotti e di memorizzazione di più categorie.

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'));

Questa struttura mantiene i dati concisi anche quando un prodotto appartiene a più categorie.

Query per Estrarre Prodotti in una Categoria Specifica

Sfruttando il tipo di dato JSON, puoi facilmente cercare i prodotti che appartengono a una categoria specifica.

Esempio di Query

La query seguente recupera tutti i prodotti nella categoria “Electronics”.

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

Risultato:

Laptop
Smartphone

Questa query facilita il recupero di elenchi di prodotti per categoria in modo flessibile.

Esempio: Filtrare per Fascia di Prezzo

Vediamo come memorizzare dati JSON che includono informazioni sui prezzi e poi cercare i prodotti in base a una fascia di prezzo.

Dati di Esempio

Il seguente esempio memorizza le informazioni sui prezzi per prodotto utilizzando il tipo JSON.

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"]}');

Esempio di Query

Per cercare i prodotti con prezzo pari a 100.000 o superiore, usa la funzione JSON_EXTRACT.

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

Risultato:

Laptop

Espandere JSON con JSON_TABLE e Esempio di Query

Se desideri interrogare i dati JSON in un formato relazionale, la funzione JSON_TABLE è molto utile. Questa funzione consente di espandere un array JSON in una tabella virtuale.

Esempio

Il seguente esempio espande un array JSON e visualizza ogni categoria come una riga separata.

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

Risultato:

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

6. Considerazioni Importanti Quando Si Usa il Tipo di Dato JSON

Suggerimenti per l’Ottimizzazione delle Prestazioni

Sebbene il tipo JSON sia flessibile, una progettazione scadente può influire negativamente sulle prestazioni del database. Di seguito sono riportati i punti chiave di ottimizzazione.

1. Utilizzo degli Indici

In MySQL non è possibile creare un indice direttamente su una colonna JSON, ma è possibile creare una colonna generata e indicizzare una chiave specifica.

Esempio: Creare un Indice Utilizzando una Colonna Generata

Nel seguente esempio, la chiave price all’interno dei dati JSON è utilizzata come obiettivo dell’indice.

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

Utilizzando una colonna generata, è possibile migliorare significativamente le prestazioni di ricerca sui dati JSON.

2. Evitare Strutture JSON Eccessivamente Complesse

Strutture JSON profondamente annidate possono ridurre la leggibilità delle query e le prestazioni. Quando progetti i dati, scegli la struttura JSON più semplice possibile.

Buon esempio:

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

Esempio da evitare:

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

Come Sfruttare gli Indici

Quando si indicizza utilizzando colonne generate, tieni presente i seguenti punti:

1. La colonna generata deve essere STORED.
2. Usa la funzione JSON_EXTRACT per estrarre una chiave specifica come colonna generata.

Ad esempio, per estrarre il primo elemento della chiave categories e creare un indice, procedi come segue.

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

L’Importanza della Validazione dei Dati

Poiché i dati JSON sono flessibili, è anche più facile memorizzare dati in un formato errato. Per mantenere l’integrità dei dati, utilizza i seguenti approcci.

1. Utilizzare Vincoli CHECK

In MySQL 8.0 e versioni successive, è possibile convalidare la struttura e il contenuto JSON utilizzando i vincoli CHECK.

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

2. Validazione a Livello di Applicazione

Durante l’inserimento dei dati, è consigliato convalidare il formato JSON sul lato applicazione. Linguaggi di programmazione come PHP e Python possono convalidare JSON usando le loro librerie standard.

7. Domande Frequenti sull’Uso degli Array in MySQL

Q1: MySQL ha un tipo di dato array?

R1:MySQL non ha un tipo di dati “array” diretto. Tuttavia, utilizzando il tipo JSON, è possibile gestire strutture dati simili ad array. Con il tipo JSON, è possibile memorizzare più valori in una colonna e manipolarli tramite query.

Esempio:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Risultato:

["Value 1", "Value 2", "Value 3"]

D2: È possibile creare un indice sui dati JSON?

R2:Non è possibile creare un indice direttamente sul tipo JSON stesso. Tuttavia, è possibile estrarre una chiave o un valore specifico in una colonna generata e creare un indice su quella colonna generata.

Esempio:

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

Questo consente di cercare efficientemente i valori all’interno dei dati JSON.

D3: Esiste un limite di dimensione per i dati JSON?

R3:Il tipo JSON di MySQL può memorizzare fino a 4GB di dati. Tuttavia, l’utilizzo di documenti JSON estremamente grandi può ridurre le prestazioni, quindi è necessario progettare i dati con cura.

Raccomandazioni:

• Memorizzare solo i dati minimi richiesti.
• Evitare strutture JSON profondamente annidate.

D4: Come è possibile aggiornare un elemento specifico all’interno di un array JSON?

R4:È possibile aggiornare un elemento specifico in un array utilizzando la funzione JSON_SET.

Esempio:

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

Risultato:

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

D5: Confronto tra il Tipo JSON e la Progettazione di Tabella Normale

R5:Sebbene il tipo JSON sia altamente flessibile, ha caratteristiche diverse rispetto alla progettazione tradizionale di database relazionali.

8. Riepilogo

Vantaggi dell’Utilizzo del Tipo di Dati JSON per le Operazioni su Array in MySQL

In questo articolo, abbiamo spiegato il tipo di dati JSON, che è utile quando si lavora con dati simili ad array in MySQL. Di seguito è riportato un riepilogo dei punti chiave trattati:

1. Perché Utilizzare il Tipo JSON MySQL non ha un tipo array diretto, ma utilizzando il tipo JSON, è possibile memorizzare più valori in una colonna e ottenere una manipolazione flessibile dei dati.
2. Operazioni JSON di Base

• Abbiamo trattato come creare array JSON, estrarre dati, aggiornare dati e rimuovere dati.
• Utilizzando funzioni comode come JSON_ARRAY , JSON_EXTRACT e JSON_SET , è possibile manipolare efficientemente i dati array.

1. Ricerca e Filtro

• Come cercare dati che contengono valori specifici utilizzando JSON_CONTAINS .
• Come recuperare il numero di elementi con JSON_LENGTH ed eseguire un filtro condizionale.

1. Esempi Pratici Attraverso casi d’uso reali come la gestione delle categorie di prodotti e il filtro per prezzo, abbiamo imparato modi concreti per applicare array JSON nelle applicazioni.
2. Considerazioni e Ottimizzazione

• Abbiamo spiegato come impostare l’indicizzazione utilizzando colonne generate e abbiamo enfatizzato l’importanza di validare i dati JSON.

Passi Successivi Quando si Utilizza il Tipo di Dati JSON

Utilizzando il tipo JSON in MySQL, è possibile gestire i dati in modo più flessibile rispetto alle progettazioni di database relazionali tradizionali. Tuttavia, una buona progettazione e considerazioni sulle prestazioni sono essenziali.

Argomenti da Imparare Successivamente:

• Utilizzo di Indici Composti Progettazione di indici che combina dati JSON con colonne normali.
• Utilizzo di Funzioni JSON Avanzate Eseguire operazioni più complesse con funzioni come JSON_MERGE e JSON_OBJECT .
• Gestione dei Dati a Livello Applicazione Come manipolare efficientemente i dati JSON di MySQL utilizzando PHP o Python.

Riepilogo

Attraverso questo articolo, ora dovreste capire come gestire efficientemente dati simili ad array utilizzando il tipo di dati JSON di MySQL. Applicando questa conoscenza, è possibile progettare database più flessibili e scalabili.