Introduzione
MySQL e MariaDB permettono di archiviare dati strutturati in formato JSON. Questa soluzione è utile quando una parte delle informazioni può cambiare nel tempo oppure quando non è conveniente creare subito una nuova tabella per ogni proprietà.
Un esempio concreto è un catalogo di prodotti. Ogni prodotto può avere caratteristiche diverse: un notebook può avere RAM e dimensione dello schermo, mentre una scarpa può avere taglia e materiale. Salvare queste proprietà in una colonna JSON consente di mantenere una struttura flessibile senza rinunciare alla possibilità di eseguire ricerche.
In questo tutorial utilizzeremo PDO per collegarci al database e impareremo a:
- creare una tabella con una colonna JSON;
- inserire dati JSON da PHP;
- leggere e decodificare i dati;
- cercare valori all´interno del JSON;
- modificare una singola proprietà;
- gestire correttamente validazione ed errori.
La sintassi JSON è generalmente compatibile tra MySQL e MariaDB, anche se l´implementazione interna può essere diversa. In particolare, MySQL dispone di un tipo JSON nativo, mentre in molte versioni di MariaDB JSON è gestito come alias di LONGTEXT. Per questo motivo è importante validare sempre i dati anche nell´applicazione PHP.
Codice completo
Il seguente esempio crea una tabella di prodotti, inserisce un prodotto con caratteristiche variabili, legge il JSON, cerca prodotti in base a una proprietà e aggiorna una singola voce.
<?php
declare(strict_types=1);
$dsn = ´mysql:host=localhost;dbname=negozio;charset=utf8mb4´;
$username = ´app_user´;
$password = ´password_sicura´;
try {
$pdo = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
// Creazione della tabella.
// In MySQL JSON è un tipo nativo; in MariaDB può essere un alias
// di LONGTEXT con validazione JSON a seconda della versione.
$pdo->exec(
´CREATE TABLE IF NOT EXISTS prodotti (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(150) NOT NULL,
prezzo DECIMAL(10, 2) NOT NULL,
caratteristiche JSON NOT NULL,
creato_il TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB´
);
// Dati strutturati in PHP.
$caratteristiche = [
´marca´ => ´TechPro´,
´ram_gb´ => 16,
´schermo_pollici´ => 15.6,
´colori´ => [´nero´, ´grigio´],
´disponibile´ => true
];
// json_encode converte l´array PHP in una stringa JSON.
$json = json_encode(
$caratteristiche,
JSON_THROW_ON_ERROR | JSON_UNESCAPED_UNICODE
);
$insert = $pdo->prepare(
´INSERT INTO prodotti (nome, prezzo, caratteristiche)
VALUES (:nome, :prezzo, :caratteristiche)´
);
$insert->execute([
´nome´ => ´Notebook professionale´,
´prezzo´ => 1299.90,
´caratteristiche´ => $json
]);
$idProdotto = (int) $pdo->lastInsertId();
// Lettura di un prodotto.
$select = $pdo->prepare(
´SELECT id, nome, prezzo, caratteristiche
FROM prodotti
WHERE id = :id´
);
$select->execute([´id´ => $idProdotto]);
$prodotto = $select->fetch();
if ($prodotto === false) {
throw new RuntimeException(´Prodotto non trovato.´);
}
// Conversione dal JSON a un array PHP.
$prodotto[´caratteristiche´] = json_decode(
$prodotto[´caratteristiche´],
true,
512,
JSON_THROW_ON_ERROR
);
echo ´<h2>´ . htmlspecialchars($prodotto[´nome´], ENT_QUOTES, ´UTF-8´) . ´</h2>´;
echo ´<p>RAM: ´ .
(int) $prodotto[´caratteristiche´][´ram_gb´] .
´ GB</p>´;
// Ricerca di prodotti con almeno 16 GB di RAM.
// JSON_EXTRACT estrae un valore usando il percorso JSON.
$search = $pdo->prepare(
´SELECT id, nome, prezzo
FROM prodotti
WHERE CAST(JSON_UNQUOTE(
JSON_EXTRACT(caratteristiche, ´$.ram_gb´)
) AS UNSIGNED) >= :ram_minima´
);
$search->execute([´ram_minima´ => 16]);
foreach ($search as $riga) {
echo ´<p>´ .
htmlspecialchars($riga[´nome´], ENT_QUOTES, ´UTF-8´) .
´ - € ´ . number_format((float) $riga[´prezzo´], 2, ´,´, ´.´) .
´</p>´;
}
// Aggiornamento di una sola proprietà del documento JSON.
$update = $pdo->prepare(
´UPDATE prodotti
SET caratteristiche = JSON_SET(
caratteristiche,
´$.disponibile´,
:disponibile
)
WHERE id = :id´
);
$update->execute([
´disponibile´ => 0,
´id´ => $idProdotto
]);
} catch (JsonException $e) {
echo ´Errore nel formato JSON: ´ .
htmlspecialchars($e->getMessage(), ENT_QUOTES, ´UTF-8´);
} catch (PDOException $e) {
echo ´Errore del database.´;
}
?>Spiegazione
Conversione tra PHP e JSON
In PHP un oggetto JSON viene normalmente rappresentato da un array associativo. La funzione json_encode() trasforma l´array in una stringa JSON pronta per essere salvata nel database. L´opzione JSON_THROW_ON_ERROR è consigliata perché genera un´eccezione se i dati non possono essere convertiti.
Per eseguire l´operazione inversa si utilizza json_decode(). Il secondo parametro impostato a true restituisce un array associativo invece di un oggetto PHP. Anche in questo caso JSON_THROW_ON_ERROR consente di intercettare dati corrotti o non validi.
Percorsi JSON
Un percorso come $.ram_gb indica la proprietà ram_gb al livello principale del documento. Per accedere a dati annidati si possono usare percorsi come $.specifiche.batteria.cicli. Per un elemento di un array si può utilizzare un indice, ad esempio $.colori[0].
JSON_EXTRACT() restituisce il valore individuato. JSON_UNQUOTE() rimuove le virgolette da una stringa JSON. Quando il valore deve essere confrontato numericamente, è opportuno usare anche CAST(), come nell´esempio della RAM.
Aggiornare un valore
JSON_SET() modifica una proprietà esistente oppure la crea se non è presente. Questa tecnica evita di leggere l´intero documento in PHP, modificarlo e riscriverlo completamente. È quindi utile quando il JSON è grande o quando più processi possono lavorare sugli stessi dati.
Best practice
- Valida i dati in PHP: controlla tipo, intervallo e presenza delle proprietà prima di chiamare
json_encode(). - Usa sempre query preparate: anche i valori utilizzati nelle ricerche JSON devono essere passati tramite parametri.
- Non inserire nomi di percorsi JSON direttamente dall´utente: i percorsi non possono essere trattati come normali parametri in ogni funzione SQL. Usa una lista di percorsi consentiti.
- Non usare JSON per tutto: dati frequentemente filtrati, ordinati o collegati ad altre tabelle dovrebbero normalmente avere colonne relazionali tradizionali.
- Considera gli indici: se una proprietà JSON viene cercata spesso, valuta una colonna generata o una colonna separata indicizzata. La sintassi esatta può variare tra MySQL e MariaDB.
- Limita la dimensione dei documenti: un JSON enorme rende più difficili manutenzione, validazione e prestazioni.
- Proteggi l´output HTML: usa
htmlspecialchars()quando mostri valori provenienti dal database in una pagina web. - Gestisci i valori booleani: JSON distingue tra
true,false, stringhe e numeri. Evita confronti ambigui e converti esplicitamente i tipi.
Riepilogo
Le colonne JSON sono una soluzione efficace per dati flessibili, come attributi personalizzati, preferenze utente o configurazioni di prodotto. Con PHP si lavora principalmente attraverso json_encode() e json_decode(), mentre MySQL e MariaDB offrono funzioni come JSON_EXTRACT() e JSON_SET() per cercare e aggiornare i contenuti direttamente nel database.
La flessibilità non deve però trasformarsi in disordine: i dati importanti per relazioni, filtri frequenti e vincoli di integrità dovrebbero restare in colonne o tabelle tradizionali. Il formato JSON è più efficace quando viene utilizzato per la parte variabile del modello, non come sostituto completo del modello relazionale.