Quelles sont les colonnes générées dans MySQL?

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

Les colonnes générées dans MySQL sont une fonctionnalité introduite dans MySQL 5.7.8, permettant aux utilisateurs de définir une colonne calculée à partir d'une expression à l'aide d'autres colonnes dans le même tableau. Ces colonnes peuvent être virtuelles ou stockées. Les colonnes générées virtuelles sont calculées au moment où elles sont lues, tandis que les colonnes générées stockées sont calculées lorsque la ligne est insérée ou mise à jour puis stockée dans la ligne.

La syntaxe pour créer une colonne générée est la suivante:

 <code class="sql">CREATE TABLE t1 ( a INT, b INT, c INT AS (ab) VIRTUAL, d INT AS (ab) STORED );</code>

Dans cet exemple, c est une colonne générée virtuelle, et d est une colonne générée stockée, toutes deux calculées à partir de la somme des colonnes a et b .

Comment les colonnes générées peuvent-elles améliorer les performances de la base de données dans MySQL?

Les colonnes générées peuvent améliorer considérablement les performances de la base de données dans MySQL de plusieurs manières:

1. Exigences de stockage réduites : en utilisant des colonnes générées virtuelles, vous pouvez réduire les exigences de stockage car les valeurs sont calculées à la volée plutôt que d'être stockées sur le disque. Ceci est particulièrement utile pour les calculs qui n'ont pas besoin d'être stockés.
2. Amélioration des performances de la requête : les colonnes générées peuvent être utilisées dans les index, ce qui peut accélérer les performances de la requête. Par exemple, si vous interrogez fréquemment un tableau basé sur la somme de deux colonnes, vous pouvez créer une colonne générée stockée pour cette somme et l'indexer, accélérant ainsi les requêtes qui utilisent cette colonne dans leur clause.
3. Requêtes simplifiées : par des valeurs pré-calculatrices avec des colonnes générées, vous pouvez simplifier vos requêtes SQL, ce qui peut conduire à de meilleures performances. Par exemple, au lieu de calculer à plusieurs reprises une expression complexe dans une instruction SELECT, vous pouvez stocker le résultat de l'expression dans une colonne générée.
4. Mises à jour efficaces : lors de l'utilisation de colonnes générées stockées, les mises à jour des colonnes source mettront automatiquement à jour la colonne générée, en garantissant la cohérence des données sans frais généraux supplémentaires dans la plupart des cas.

Quels types de colonnes générées prennent en charge MySQL et comment diffèrent-elles?

MySQL prend en charge deux types de colonnes générées: virtuelle et stockée.

• Colonnes générées virtuelles : ces colonnes ne sont pas stockées sur le disque; Au lieu de cela, ils sont calculés à la volée chaque fois que la ligne est lue. L'avantage des colonnes générées virtuelles est qu'ils économisent l'espace disque car ils ne stockent pas la valeur calculée. Cependant, ils peuvent encourir un coût de performance chaque fois qu'ils sont accessibles car le calcul doit être effectué au moment de la lecture.
• Colonnes générées stockées : Ces colonnes stockent la valeur calculée sur le disque. La valeur est calculée et stockée lorsque la ligne est insérée ou mise à jour. Les colonnes générées peuvent être bénéfiques lorsque le calcul est coûteux ou lorsque la colonne est fréquemment accessible dans les requêtes, car le calcul est effectué uniquement au moment des opérations d'écriture, pas au moment de la lecture. Cependant, ils nécessitent un espace de stockage supplémentaire.

Le choix entre les colonnes virtuelles et les colonnes générées dépend du cas d'utilisation spécifique et des considérations de performances, telles que la fréquence des opérations de lecture et d'écriture, la complexité du calcul et les contraintes de stockage de votre base de données.

Quels sont les cas d'utilisation pratiques pour la mise en œuvre de colonnes générées dans MySQL?

Les colonnes générées ont plusieurs cas d'utilisation pratiques dans MySQL:

1. Calcul de l'âge de la date de naissance : Si vous avez une colonne date_of_birth dans un tableau des utilisateurs, vous pouvez créer une colonne générée pour calculer l'âge en années. Par exemple:

    <code class="sql">CREATE TABLE users ( id INT, date_of_birth DATE, age INT AS (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())) VIRTUAL );</code>

2. Nom complet Concaténation : Si vous avez des colonnes distinctes pour les noms de premier et de famille, vous pouvez créer une colonne générée pour les combiner en un nom complet:

    <code class="sql">CREATE TABLE employees ( id INT, first_name VARCHAR(50), last_name VARCHAR(50), full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) STORED );</code>

3. Calculs géographiques : pour une table avec des colonnes de latitude et de longitude, vous pouvez créer une colonne générée pour calculer la distance d'un point fixe:

    <code class="sql">CREATE TABLE locations ( id INT, lat DECIMAL(10, 8), lon DECIMAL(11, 8), distance_from_home DECIMAL(10, 2) AS ( 3959 * acos ( cos ( radians(40.7128) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-74.0060) ) sin ( radians(40.7128) ) * sin( radians( lat ) ) ) ) VIRTUAL );</code>

4. Gestion des stocks : vous pouvez utiliser des colonnes générées pour garder une trace de la valeur totale des articles d'inventaire en fonction de la quantité et du prix unitaire:

    <code class="sql">CREATE TABLE inventory ( id INT, item_name VARCHAR(100), quantity INT, unit_price DECIMAL(10, 2), total_value DECIMAL(10, 2) AS (quantity * unit_price) STORED );</code>

Ces exemples illustrent comment les colonnes générées peuvent simplifier la gestion des données et améliorer les performances de la requête par des valeurs pré-calculatrices fréquemment utilisées dans les requêtes.

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!