一个具有多个 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.

我知道，如果您有一个 TIMESTAMP 列和一个 DEFAULT CURRENT_TIMESTAMP 或一个 ON UPDATE CURRENT_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.

我发现你 可以有多个 TIMESTAMP 列，方法是让每个列不带 DEFAULT 或 ON UPDATE 并插入 NULL 创建记录时，导致每个记录都有当前时间戳.从那时起，第一列将自动更新.

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屋！