一个带有多个TIMESTAMP列的Mysql表 [英] One Mysql Table with Multiple TIMESTAMP Columns

问题描述

我想要一个包含两个TIMESTAMP列的表.一列用于记录的创建时间，另一列用于记录的修改时间.我希望这些值由数据库处理.我不想让我的应用程序层考虑一下.

I want to have one table with two TIMESTAMP columns. One column to keep track of when the record was created and another to keep track of when it was modified. I want these values handled by the database. I don't want my app layer to have to think about it.

我知道，如果您有带有DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP的TIMESTAMP列，则不能再有另一个TIMESTAMP列.您可以使用DATETIME，但是没有办法将它默认为触发器之外的东西.

I know that if you have a TIMESTAMP column with a DEFAULT CURRENT_TIMESTAMP or an ON UPDATE CURRENT_TIMESTAMP you cannot have another TIMESTAMP column. You can use DATETIME but there is no way to default it, that I know of, outside of a trigger.

我发现您

I found that you can have multiple TIMESTAMP columns by leaving each without DEFAULT or ON UPDATE and inserting NULL when the record is created, causing each to have the current timestamp. From that point on the first column will automatically be updated.

这很棒，但给我留下了一种有趣的感觉.这样可能是一个错误，并且可以随时对其进行修补.如果这是应该工作的方式，那就这样吧.我会很高兴地继续前进.谁能告诉我这是最好的方法还是应该使用触发器?

This works fantastically but it leaves me with a funny feeling. Like this may be a bug and it could be patched at any time. If this is the way it is supposed to work then so be it. I will merrily go on my way. Can anyone tell me if this is the best way to do this or should I be using triggers?

推荐答案

它已记录在MySQL文档中:

It's documented in the MySQL docs:

此外，您可以将任何TIMESTAMP列初始化或更新为 通过为当前日期和时间分配一个NULL值，除非已设置为当前日期和时间 使用NULL属性定义以允许使用NULL值.

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

这篇关于一个带有多个TIMESTAMP列的Mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！