Column calculated from another column?

Question

Given the following table:

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

Is there a way to add a column that is automatically calculated based on another column in the same table? Like a VIEW, but part of the same table. As an example, calculated would be half of value. Calculated should be automatically updated when value changes, just like a VIEW would be.

The result would be:

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

Answer 1

If it is a selection, you can do it as:

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

Else, you can also first alter the table to add the missing column and then do an UPDATE query to compute the values for the new column as:

UPDATE mytable SET calculated = value/2;

If it must be automatic, and your MySQL version allows it, you can try with triggers

Answer 2

Generated Column is one of the good approach for MySql version which is 5.7.6 and above.

There are two kinds of Generated Columns:

• Virtual (default) - column will be calculated on the fly when a record is read from a table
• Stored - column will be calculated when a new record is written/updated in the table

Both types can have NOT NULL restrictions, but only a stored Generated Column can be a part of an index.

For current case, we are going to use stored generated column. To implement I have considered that both of the values required for calculation are present in 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);

amount will automatically pop up in table and you can access it directly, also please note that whenever you will update any of the columns, amount will also get updated.

Answer 3

If you want to add a column to your table which is automatically updated to half of some other column, you can do that with a trigger.

But I think the already proposed answer are a better way to do this.

Dry coded trigger :

CREATE TRIGGER halfcolumn_insert AFTER INSERT ON table
  FOR EACH ROW BEGIN
    UPDATE table SET calculated = value / 2 WHERE id = NEW.id;
  END;
CREATE TRIGGER halfcolumn_update AFTER UPDATE ON table
  FOR EACH ROW BEGIN
    UPDATE table SET calculated = value / 2 WHERE id = NEW.id;
  END;

I don't think you can make only one trigger, since the event we must respond to are different.

Answer 4

I hope this still helps someone as many people might get to this article. If you need a computed column, why not just expose your desired columns in a view ? Don't just save data or overload the performance with triggers... simply expose the data you need already formatted/calculated in a view.

Hope this helps...

Answer 5

@krtek's answer is in the right direction, but has a couple of issues.

The bad news is that using UPDATE in a trigger on the same table won't work. The good news is that it's not necessary; there is a NEW object that you can operate on before the table is even touched.

The trigger becomes:

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

Note also that the BEGIN...END; syntax has to be parsed with a different delimiter in effect. The whole shebang becomes:

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 6

MySQL 5.7 supports computed columns. They call it "Generated Columns" and the syntax is a little weird, but it supports the same options I see in other databases.

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