mysql 将字段默认值设置为其他列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15384429/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql set field default value to other column
提问by BMW
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
)
回答by eggyal
As documented under Data Type Default Values:
The
DEFAULT valueclause 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 asNOW()orCURRENT_DATE. The exception is that you can specifyCURRENT_TIMESTAMPas the default for aTIMESTAMPcolumn. See Section 11.3.5, “Automatic Initialization and Updating forTIMESTAMP”.
数据类型规范中的子句指示列的默认值。除了一个例外，默认值必须是一个常量；它不能是函数或表达式。这意味着，例如，您不能将日期列的默认值设置为函数的值，例如或。例外是您可以指定为列的默认值。见第 11.3.5 节，“自动初始化和更新”。
DEFAULT valueNOW()CURRENT_DATECURRENT_TIMESTAMPTIMESTAMPTIMESTAMP
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;;
回答by Darkside
The 'NEW' is unacceptable for AFTER insert triggers. You should do the 'field update' by a BEFORE insert trigger. So,
“新”对于 AFTER 插入触发器是不可接受的。您应该通过 BEFORE insert 触发器执行“字段更新”。所以，
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;;
