MySQL:在更新时设置为默认值

Is it possible to set a column to its default value (or any specified value) on update when no value is specifically given in the statement? I was thinking that a trigger might accomplish this. Something like

当语句中没有指定值时，是否可以在更新时将列设置为默认值(或任何指定值)?我在想一个触发器可以完成这个。类似的

IF ISNULL(NEW.column) THEN
 NEW.column = value
END IF;

didn't work.

没有工作。

2 个解决方案

#1

MySQL has function called DEFAULT(), which gets the default value from specified column.

MySQL有一个名为DEFAULT()的函数，它从指定列获取默认值。

UPDATE tbl SET col = DEFAULT(col);

MySQL Reference

MySQL参考

#2

UPDATE:

更新:

@JanTraenkner As far as I can tell, this is not possible. You can however make sure in your application code, that all columns are mentioned in your update statement and for those that do not have a value your use NULL as value. Then your trigger code is almost right, you just need to change it to

@JanTraenkner据我所知，这是不可能的。但是，您可以在应用程序代码中确保在update语句中提到了所有列，对于那些没有值的列，可以使用NULL作为值。那么您的触发器代码几乎是正确的，您只需要将它更改为

IF (NEW.column IS NULL) THEN
 SET NEW.column = value
END IF;

Original answer:

最初的回答:

I understood your question like, "set column to default value, if I don't specify the column in an update statement (which updates other columns from that table)".

我理解您的问题，比如“将列设置为默认值，如果我没有在update语句中指定列(它将从该表中更新其他列)”。

To check with ISNULL() or col IS NULL doesn't work here, because when you don't specify it in the update statement it simply isn't there. There's nothing to check for.

要检查ISNULL()或col是否为NULL在这里不起作用，因为当您不在update语句中指定它时，它根本不存在。没有什么可查的。

I wrote this little example script which makes it work like I understood the question.

我编写了这个示例脚本，使它能够像我理解问题一样工作。

drop table if exists defvalue;
create table defvalue (id int auto_increment primary key, abc varchar(255) default 'default');
insert into defvalue (id) values (null);
insert into defvalue (id, abc) values (null, 'not_default_value');
insert into defvalue (id, abc) values (null, 'another_not_default_value');

drop trigger if exists t_defval;
delimiter $$
create trigger t_defval before update on defvalue
for each row
begin
set @my_def_value = (select default(abc) from defvalue limit 1);
if (new.abc = old.abc) then
set new.abc = @my_def_value;
end if;
end $$
delimiter ;

select * from defvalue;

update defvalue set id = 99 where id = 1;

select * from defvalue;

update defvalue set id = 98 where id = 2;

select * from defvalue;

I also had to save the default value of the column in a variable first because the function needs to know from which table. Unfortunately one can't specify that as parameter, not even as default(tablename.column).

我还必须首先将列的默认值保存在一个变量中，因为函数需要知道从哪个表中。不幸的是，不能将其指定为参数，甚至不能指定为默认参数(tablename.column)。

All in all, please note, that this is rather a proof of concept. I'd recommend to solve this on application layer, not database layer. Having a trigger for this seems a bit dirty for me.

总之，请注意，这是一种概念的证明。我建议在应用层解决这个问题，而不是数据库层。对我来说，有个触发点似乎有点脏。

#1