mysql将字段默认值设置为其他列

How to set default value for a field to other column in Mysql I have done it oracle with virtual field but I do not know how to do it in Mysql this is my table:

如何在Mysql中将字段的默认值设置为其他列我已经使用虚拟字段完成了oracle但我不知道如何在Mysql中执行此操作这是我的表：

create table TSM_TRANSACTION_TBL
(
  TRANS_ID     INT primary key auto_increment,
  LOCATION_ID  INT,
  TRANS_DATE   DATE,
  RESOURCE_ID  INT,
  TS_ID        INT,
  MAX_VALUE    INT,
  BOOKED_UNITS INT default 0,
  REMAINING    INT default MAX_VALUE - BOOKED_UNITS,
  BOOKED       INT not null,
  USER_ID      INT,
  TRANS_TIME   TIMESTAMP
)

2 个解决方案

#1

As documented under Data Type Default Values:

如数据类型默认值中所述：

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

数据类型规范中的DEFAULT值子句指示列的默认值。除了一个例外，默认值必须是常量;它不能是一个功能或表达。这意味着，例如，您不能将日期列的默认值设置为函数的值，例如NOW（）或CURRENT_DATE。例外情况是您可以将CURRENT_TIMESTAMP指定为TIMESTAMP列的默认值。请参见第11.3.5节“TIMESTAMP的自动初始化和更新”。

Instead, you can define an insertion trigger:

相反，您可以定义插入触发器：

CREATE TRIGGER foo BEFORE INSERT ON TSM_TRANSACTION_TBL FOR EACH ROW
  IF NEW.REMAINING IS NULL THEN
    SET NEW.REMAINING := NEW.MAX_VALUE - NEW.BOOKED_UNITS;
  END IF;;

#2

The 'NEW' is unacceptable for AFTER insert triggers. You should do the 'field update' by a BEFORE insert trigger. So,

对于AFTER插入触发器，“新”是不可接受的。您应该通过BEFORE插入触发器执行“字段更新”。所以，

CREATE TRIGGER foo BEFORE INSERT ON TSM_TRANSACTION_TBL FOR EACH ROW
    IF NEW.REMAINING IS NULL THEN
        SET NEW.REMAINING := NEW.MAX_VALUE - NEW.BOOKED_UNITS;
    END IF;;

#1