MySQL中的计算列

本文介绍了MySQL中的计算列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试添加计算列.

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

推荐答案

MySQL不支持MySQL 5.7之前的计算列.现在，较新的版本确实支持计算列.

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

您可以改用视图:

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

注意:

• 在支持计算列的数据库中，类型不属于列定义.它是从表达式派生的(可以使用 cast()/ convert()转换为特定类型).
• 使用浮点表示形式存储货币金额是一个坏主意.您应该改用 decimal / numeric .
• 如果您不想使用视图，则可以向表中添加一列(以及类型)，并使用触发器来维护值.

• 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.