Comment stocker les données JSON dans MySQL et les interroger?

En utilisant MySQL 5.7 et plus, vous pouvez stocker et interroger directement les données JSON via des types de données JSON; 2. Définissez les colonnes JSON et insérez des données JSON légales lors de la création d'une table, et MySQL vérifiera automatiquement sa validité; 3. Utilisez les opérateurs -> et - >> pour extraire les valeurs de champ JSON, parmi lesquelles - >> peut être utilisée pour supprimer les citations de chaîne à comparer; 4. Recherchez le contenu JSON dans l'endroit où les clauses via des fonctions telles que JSON_CONTAIN, CAST, etc., et notez que la comparaison numérique nécessite une conversion de type; 5. Utilisez les fonctions JSON_SET, JSON_REPLACE et JSON_REMOVE pour modifier le champ JSON; 6. Bien que les colonnes JSON ne puissent pas être directement indexées, les index peuvent être créés en générant des colonnes (telles que des colonnes virtuelles basées sur JSON_UNQUOTE) pour améliorer les performances de la requête; 7. Il est recommandé d'extraire les champs fréquemment interrogés dans des colonnes générées et d'établir des index pour éviter la surdiffion sur les grandes requêtes d'objet JSON, et maintenir la structure des données de base pour obtenir un équilibre de flexibilité et de performances.

Le stockage et l'interrogation des données JSON dans MySQL sont simples si vous utilisez MySQL 5.7 ou version ultérieure, car ces versions ont une prise en charge intégrée pour les types de données JSON. Cela vous permet de stocker des données semi-structurées flexibles tout en bénéficiant des capacités de requête SQL. Voici comment le faire efficacement.

1. Stockage des données JSON dans MySQL

Pour stocker JSON, utilisez le type de données JSON lors de la définition d'une colonne dans votre table.

Créer une table avec une colonne JSON

 Créer des utilisateurs de table (
    id int Auto_Increment Clé primaire,
    nom varchar (100),
    Profile JSON
));

Insérer les données JSON

Vous pouvez insérer des objets ou des tableaux JSON valides:

 Insérer dans les valeurs des utilisateurs (nom, profil) (
    'Alice',
    '{
        "Age": 30,
        "City": "New York",
        "Hobbies": ["Reading", "Cycling"],
        "contact": {
            "Courriel": "Alice@example.com",
            "téléphone": "123-456-7890"
        }
    } '
));

MySQL valide le JSON sur l'insertion. JSON non valide provoquera une erreur.

2. Interroger les données JSON

MySQL fournit des fonctions et des opérateurs pour extraire et rechercher dans les champs JSON.

Extraire des valeurs en utilisant -> et ->>

• column->'$.path' Renvoie la valeur JSON (avec des devis pour les chaînes).
• column->>'$.path' Renvoie la valeur scalaire non cotée (mieux pour les comparaisons).

 - Obtenez l'objet de contact complet
Sélectionnez Profil -> '$. Contact' comme contact depuis les utilisateurs où name = 'Alice';

- recevoir l'e-mail non coté
Sélectionnez Profile - >> '$. Contact.email' comme e-mail des utilisateurs où name = 'Alice';

- Obtenez l'âge et la ville
SÉLECTIONNER 
    Profil - >> '$. Âge' comme âge,
    Profil - >> '$. City' As City
Des utilisateurs;

Recherche dans JSON

Utilisez JSON_CONTAINS() ou des expressions de chemin dans WHERE clauses.

 - Trouvez des utilisateurs qui ont du "vélo" dans les passe-temps
Sélectionnez le nom des utilisateurs 
Où JSON_CONTAINS (Profil -> '$. Hobbies', '"Cycling"');

- Trouvez des utilisateurs de plus de 25 ans
Sélectionnez le nom des utilisateurs 
Où coulé (profil - >> '$. Age' comme non signé)> 25;

- Trouvez des utilisateurs de New York
Sélectionnez le nom des utilisateurs 
Où le profil - >> '$. City' = 'New York';

Remarque: Puisque ->> Renvoie le texte, vous devrez peut-être lancer des valeurs (par exemple, SIGNED , UNSIGNED ou DECIMAL ) pour des comparaisons numériques.

3. Mise à jour des champs JSON

Utilisez JSON_SET() , JSON_REPLACE() , ou JSON_REMOVE() pour modifier le contenu JSON.

 - Ajouter ou mettre à jour un champ
Mettre à jour les utilisateurs 
Set Profil = JSON_SET (Profil, '$ .age', 31, '$ .City', 'Boston')
Où name = 'Alice';

- Ajouter à un tableau
Mettre à jour les utilisateurs 
Set profil = json_array_append (profil, '$ .hobbies', 'natation')
Où name = 'Alice';

- Retirez un champ
Mettre à jour les utilisateurs 
Set Profil = JSON_Remove (Profil, '$ .Contact.phone')
Où name = 'Alice';

4. Indexation des données JSON (indirectement)

Vous ne pouvez pas indexer directement une colonne JSON , mais vous pouvez créer des colonnes (virtuelles) générées et indexer.

Exemple: indexez le champ de messagerie à l'intérieur de JSON

 Modifier les utilisateurs de la table 
Ajouter un e-mail de colonne Varchar (255) 
Généré toujours en tant que (JSON_UNQUOTE (profil - >> '$. Contact.email')));

Créer index idx_email sur les utilisateurs (e-mail);

Désormais, le filtrage des requêtes sur email peut utiliser l'index:

 Sélectionnez le nom des utilisateurs où email = 'Alice@example.com';

Cela améliore considérablement les performances pour les lookups fréquents basés sur JSON.

Meilleures pratiques

• Utilisez JSON pour des attributs flexibles ou facultatifs (par exemple, préférences des utilisateurs, paramètres).
• Gardez les champs fréquemment interrogés ou critiques (comme le courrier électronique, le statut) comme colonnes régulières ou utilisez des colonnes générées avec des index.
• Évitez de stocker de grands blobs JSON si vous les interrogez souvent, normalisez lorsque cela est possible.
• Validez la structure JSON dans votre application pour éviter les erreurs.

Le stockage de JSON dans MySQL vous donne une flexibilité sans sacrifier trop de performances, en particulier lorsque vous le combinez avec des colonnes générées et une indexation appropriée. Il est idéal pour l'évolution des schémas ou des données hiérarchiques qui ne s'intègrent pas parfaitement dans les tables rigides.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!