一个带有多个 TIMESTAMP 列的 Mysql 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4851672/
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
One Mysql Table with Multiple TIMESTAMP Columns
提问by Josh Johnson
I want to have one table with two TIMESTAMPcolumns. 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列的桌子。一列用于跟踪记录的创建时间，另一列用于跟踪记录的修改时间。我希望这些值由数据库处理。我不希望我的应用层必须考虑它。
I know that if you have a TIMESTAMPcolumn with a DEFAULT CURRENT_TIMESTAMPor an ON UPDATE CURRENT_TIMESTAMPyou cannot have another TIMESTAMPcolumn. You can use DATETIMEbut there is no way to default it, that I know of, outside of a trigger.
我知道，如果您有一TIMESTAMP列带有 aDEFAULT CURRENT_TIMESTAMP或 an的列，ON UPDATE CURRENT_TIMESTAMP则不能再有另一TIMESTAMP列。您可以使用DATETIME但没有办法默认它，我知道，在触发器之外。
I found that you can have multiple TIMESTAMPcolumnsby leaving each without DEFAULTor ON UPDATEand inserting NULLwhen the record is created, causing each to have the current timestamp. From that point on the first column will automatically be updated.
我发现您可以TIMESTAMP通过在创建记录时将每个列保留为不DEFAULT或ON UPDATE并插入来拥有多个列，从而使每个列NULL都具有当前时间戳。从那时起，第一列将自动更新。
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?
这非常有效，但它给我留下了一种有趣的感觉。像这可能是一个错误，可以随时修补。如果这是它应该工作的方式，那么就这样吧。我会愉快地走我的路。谁能告诉我这是最好的方法还是我应该使用触发器？
采纳答案by Marc B
It's documented in the MySQL docs:
它记录在 MySQL 文档中：
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.
此外，您可以通过为其分配 NULL 值来将任何 TIMESTAMP 列初始化或更新为当前日期和时间，除非已使用 NULL 属性将其定义为允许 NULL 值。
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
回答by Santosh Achari
MySQL versions before 5.6.1 would not let two TIMESTAMPcolumns in the same table, unless as you rightly noted with out defaults and allowing null.
5.6.1 之前的 MySQL 版本不会让TIMESTAMP同一个表中有两列，除非您正确地指出没有默认值并允许空值。
MySQL 5.6.+ allows two or more TIMESTAMPcolumns in a table.
MySQL 5.6.+ 允许TIMESTAMP一个表中有两列或更多列。
More here: http://shankargopal.blogspot.in/2013/03/mysql-566-timestamp-columns-and-default.html
更多信息：http: //shankargopal.blogspot.in/2013/03/mysql-566-timestamp-columns-and-default.html
回答by Yonatan Alexis Quintero Rodrig
MySQL allows more than one TIMESTAMP columns in the same table, check this example:
MySQL 允许在同一个表中有多个 TIMESTAMP 列，请查看此示例：
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
Note that the ts1 TIMESTAMP column is DEFAULT with VALUE 0, and the ts2 TIMESTAMP column is DEFAULT with value CURRENT_TIMESTAMP. More info here http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
请注意，ts1 TIMESTAMP 列的值为 0 的默认值，而 ts2 TIMESTAMP 列的值为 CURRENT_TIMESTAMP 的默认值。更多信息在这里http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
