Computed Column in Mysql

Question

I am trying to add a computeed column.

alter table datatest add column amount2 double  as (amount*rate)

but I got error while executing this

Answer 1

MySQL doesn't support computed columns prior to MySQL 5.7. The more recent versions do now support computed columns.

You can use a view instead:

create view v_datatest as
    select t.*, (amount * rate) as amount2
    from datatest;

Notes:

• In databases that do support computed columns, the type is not part of the column definition. It is derived from the expression (you can use cast()/convert() to convert to a particular type).
• It is a bad idea to store monetary amounts using floating point representations. You should be using decimal/numeric instead.
• If you don't want to use a view, you can add a column to the table (along with the type) and use a trigger to maintain the value.