mysql将字段默认值设置为其他列 [英] mysql set field default value to other column

问题描述

如何在Mysql中将字段的默认值设置为其他列我已经使用虚拟字段在oracle中完成了此操作，但是我不知道如何在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:

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
)

推荐答案

如

数据类型规范中的DEFAULT value子句指示列的默认值.除一个例外，默认值必须为常数；否则为0.它不能是函数或表达式.例如，这意味着您不能将日期列的默认值设置为诸如 CURRENT_TIMESTAMP 作为 TIMESTAMP 列的默认值.请参见第11.3.5节"TIMESTAMP的自动初始化和更新" .

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

相反，您可以定义一个插入触发器:

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;;

这篇关于mysql将字段默认值设置为其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！