Colonne calculée à partir d'une autre colonne ?

Question

Étant donné le tableau suivant :

id | value
--------------
1     6
2     70

Existe-t-il un moyen d'ajouter une colonne qui est automatiquement calculée sur la base d'une autre colonne dans le même tableau ? Comme une VIEW, mais faisant partie de la même table. Par exemple, calculated serait la moitié de value . Calculated devrait être automatiquement mis à jour lorsque value des changements, tout comme le serait un VIEW.

Le résultat serait :

id | value | calculated
-----------------------
1     6       3
2     70      35

Answer 1

Generated Column est une des bonnes approches pour la version MySql 5.7.6 et plus.

Il existe deux types de colonnes générées :

• Virtuel (par défaut) - la colonne sera calculée à la volée lorsqu'un enregistrement est lu à partir d'une table. enregistrement est lu à partir d'une table
• Stocké - la colonne sera calculée lorsqu'un nouvel enregistrement est écrit/mise à jour dans la table

Les deux types peuvent avoir des restrictions NOT NULL, mais seule une colonne générée stockée peut faire partie d'un index.

Dans le cas présent, nous allons utiliser une colonne générée stockée. Pour la mise en œuvre, j'ai considéré que les deux valeurs requises pour le calcul sont présentes dans la table.

CREATE TABLE order_details (price DOUBLE, quantity INT, amount DOUBLE AS (price * quantity));

INSERT INTO order_details (price, quantity) VALUES(100,1),(300,4),(60,8);

Le montant apparaîtra automatiquement dans le tableau et vous pourrez y accéder directement. Notez également que si vous mettez à jour l'une des colonnes, le montant sera également mis à jour.

Answer 2

S'il s'agit d'une sélection, vous pouvez le faire comme :

SELECT id, value, (value/2) AS calculated FROM mytable

Sinon, vous pouvez également modifier la table pour ajouter la colonne manquante, puis effectuer une requête UPDATE pour calculer les valeurs de la nouvelle colonne :

UPDATE mytable SET calculated = value/2;

Si cela doit être automatique, et que votre version de MySQL le permet, vous pouvez essayer avec déclencheurs

Answer 3

MySQL 5.7 prend en charge les colonnes calculées. Elles s'appellent "Generated Columns" et la syntaxe est un peu bizarre, mais elle supporte les mêmes options que celles que je vois dans d'autres bases de données.

https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

Answer 4

La réponse de @krtek va dans le bon sens, mais présente quelques problèmes.

La mauvaise nouvelle est que l'utilisation de UPDATE dans un trigger sur la même table ne fonctionnera pas. La bonne nouvelle est que ce n'est pas nécessaire ; il existe un objet NEW sur lequel vous pouvez opérer avant même de toucher la table.

Le déclencheur devient :

CREATE TRIGGER halfcolumn_update BEFORE UPDATE ON my_table
  FOR EACH ROW BEGIN
    SET NEW.calculated = NEW.value/2;
  END;

Notez également que la syntaxe BEGIN...END ; doit être analysée avec un délimiteur différent. L'ensemble devient :

DELIMITER |

CREATE TRIGGER halfcolumn_insert BEFORE INSERT ON my_table
  FOR EACH ROW BEGIN
    SET NEW.calculated = NEW.value/2;
  END;
|

CREATE TRIGGER halfcolumn_update BEFORE UPDATE ON my_table
  FOR EACH ROW BEGIN
    SET NEW.calculated = NEW.value/2;
  END;
|

DELIMITER ;

Answer 5

Vous pouvez utiliser les colonnes générées à partir de MYSQL 5.7.

Exemple d'utilisation :

ALTER TABLE tbl_test
ADD COLUMN calc_val INT 
GENERATED ALWAYS AS (((`column1` - 1) * 16) + `column2`) STORED;

VIRTUAL / STORED

• Virtuel : calculé à la volée lorsqu'un enregistrement est lu depuis une table (par défaut)
• Stocké : calculé lorsqu'un nouvel enregistrement est inséré/mis à jour dans la table. table