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

问题描述

如何将字段的默认值设置为MySQL中的其他列?

How to set default value for a field to other column in MySQL?

我已经在Oracle的虚拟字段中完成了该操作，但是我不知道如何在MySQL中执行该操作.

I have done it in Oracle with virtual field, but I do not know how to do it in 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
);

推荐答案

如

数据类型规范中的 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屋！