Tirer parti des colonnes générées par MySQL pour des données virtuelles

Les colonnes générées virtuelles de MySQL conviennent au stockage ou au calcul des données qui ne sont pas directement entrées par les utilisateurs mais sur la base d'autres champs du tableau. Ils sont calculés dynamiquement lors de la requête et n'occupent pas l'espace de stockage physique. Les scénarios d'utilisation incluent les valeurs de dérive d'autres colonnes telles que le nom complet, le prix total, l'URL formatée, etc. L'avantage est de garder la structure de la base de données simple et de réduire la logique de duplication de la couche d'application. Les notes incluent éviter les expressions complexes, assurer la correspondance du type de données, utiliser des fonctions déterministes et faire attention aux impacts des performances. L'utilisation rationnelle peut améliorer la maintenabilité, mais elle doit être conçue avec prudence pour éviter les problèmes potentiels.

Les colonnes générées par MySQL peuvent être un outil puissant lorsque vous devez stocker ou calculer des données qui ne sont pas directement entrées par les utilisateurs mais dérivées d'autres champs du tableau. Ils sont particulièrement pratiques pour les données virtuelles - des valeurs qui ne sont pas stockées sur le disque (dans le cas du type VIRTUAL ) mais qui sont calculées à la volée lorsqu'elles sont interrogées.

Voici comment et pourquoi vous voudrez peut-être les utiliser efficacement.

Que sont les colonnes générées?

Dans MySQL, une colonne générée est celle dont la valeur est dérivée d'une expression impliquant d'autres colonnes dans la même ligne. Vous définissez cette expression lors de la création ou de la modification du tableau.

Il existe deux types:

• Virtuel - non stocké physiquement; calculé lors de la lecture.
• Stocké - calculé et stocké lorsque les données sont insérées ou mises à jour.

Pour les données virtuelles qui n'ont pas besoin d'être persistées, VIRTUAL est généralement la voie à suivre.

Quand devriez-vous utiliser des colonnes générées virtuelles?

Les cas d'utilisation où VIRTUAL a du sens inclure:

• Dériver des noms complets des champs de noms de premier et de famille.
• Calcul des totaux en fonction des éléments de ligne.
• Formatage d'URL ou de limaces des titres.
• Normaliser les horodatages en temps local ou formats spécifiques.

Ces valeurs n'ont pas besoin d'être stockées séparément car elles sont toujours dérivées à partir des données existantes.

Disons que vous avez une table comme ceci:

 Créer des produits de table (
  id int clé primaire,
  prix décimal (10,2),
  tax_rate décimal (5,4),
  Total_Price Decimal (10,2) As (Prix * (1 Tax_Rate)) Virtual
));

Maintenant, chaque fois que vous interrogez total_price , il calculera en fonction price actuel et tax_rate .

Considérations de performance

Bien que les colonnes VIRTUAL ne prennent pas un espace de stockage, elles affectent les performances au moment de la requête, car le calcul se produit alors.

Certaines choses à garder à l'esprit:

• Évitez les expressions complexes avec un calcul lourd.
• Ne les utilisez pas dans de grands ensembles de résultats sauf si nécessaire.
• Si l'expression implique plusieurs jointures ou sous-requêtes, envisagez d'utiliser une procédure stockée à la place.

De plus, les index sur les colonnes VIRTUAL sont possibles, mais seulement s'ils sont marqués comme PERSISTENT ou pris en charge par votre version MySQL (comme MariADB). Dans MySQL standard, l'indexation d'une colonne VIRTUAL est autorisée, mais elle recalcule toujours la valeur pendant les lookups.

Conseils pratiques pour utiliser des données virtuelles

Voici quelques conseils réels pour éviter les pièges:

• Gardez l'expression simple et déterminez. MySQL n'autorisera pas les fonctions non déterministes comme NOW() dans certains contextes.
• Assurez-vous que les types de données correspondent. Par exemple, l'ajout d'une décimale et d'un entier peut provoquer une troncature inattendue.
• Utilisez des conventions de dénomination claires afin que les futurs développeurs sachent que la colonne est dérivée.
• Documentez la logique quelque part, surtout si ce n'est pas immédiatement évident.

Vous pouvez également utiliser des colonnes générées pour nettoyer ou formater les données:

 full_name varchar (100) as (concat (first_name, '', last_name)) virtuel

Cela évite d'avoir à répéter la logique de concaténation dans chaque requête ou couche d'application.

Réflexions finales

Les colonnes générées, en particulier les colonnes VIRTUAL , offrent un moyen voisin de garder votre schéma de base de données propre et de réduire la logique redondante dans votre code d'application. Ils fonctionnent mieux pour les calculs légers et prévisibles que vous souhaitez disponibles comme s'ils étaient des colonnes régulières.

Ce n'est pas une solution miracle - une mauvaise utilisation peut conduire à des requêtes confuses ou à des problèmes de performances - mais bien utilisés, ils gagnent du temps et améliorent la maintenabilité.

Et c'est essentiellement ça.

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!