MySQL MySQL创建时间和更新时间时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3488536/
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 create time and update time timestamp
提问by Aishwar
I am creating some tables where I want to store the time when a record was created and when it was last updated. I thought I could have two timestamp fields where one would have the value CURRENT_TIMESTAMPand the other would have CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. But I guess I can't do this because you can have only 1 timestamp field with a default value in a table?
我正在创建一些表，我想在其中存储记录的创建时间和上次更新时间。我以为我可以有两个时间戳字段，其中一个具有值CURRENT_TIMESTAMP，另一个具有CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. 但我想我不能这样做，因为表中只能有 1 个带有默认值的时间戳字段？
How would you recommend I get and store the two times? Thanks!
您建议我如何获取和存储两次？谢谢！
采纳答案by a_horse_with_no_name
You can have two columns of type timestamp in one table.
一个表中可以有两列时间戳类型。
The following works for MySQL 5.0
以下适用于 MySQL 5.0
create table t ( id integer, created_at timestamp default current_timestamp, updated_at timestamp );
I think you are confusing this with SQL Server (where timestamp is not really a "time stamp" and there is indeed a limit on a single "timestamp" column)
我认为您将此与 SQL Server 混淆（其中时间戳不是真正的“时间戳”，并且确实对单个“时间戳”列有限制）
Edit: But you will need a trigger to update the update_at column each time the row is changed.
编辑：但是每次更改行时，您都需要一个触发器来更新 update_at 列。
回答by albert
A good way to create fields like 'created' and 'updated' is
创建“已创建”和“已更新”等字段的好方法是
CREATE TABLE `mytable` (
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
`created` TIMESTAMP DEFAULT '0000-00-00 00:00:00',
`updated` TIMESTAMP DEFAULT now() ON UPDATE now(),
`myfield` VARCHAR(255)
);
And its necessary to enter nulls into both columns during "insert":
并且有必要在“插入”期间将空值输入到两列中：
INSERT INTO mytable (created,updated,myfield) VALUES (null,null,'blablabla');
And now, in all updates, the 'updated' field will have a new value with actual date.
现在，在所有更新中，“已更新”字段将具有带有实际日期的新值。
UPDATE mytable SET myfield='blablablablu' WHERE myfield='blablabla';
Source : http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/
来源：http: //gusiev.com/2009/04/update-and-create-timestamps-with-mysql/
回答by Dimitry
As of MYSQL version 5.6.5 you can do this using DEFAULT and ON UPDATE. No triggers are needed.
从 MYSQL 5.6.5 版开始，您可以使用 DEFAULT 和 ON UPDATE 执行此操作。不需要触发器。
ts_createTIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_createTIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_updateTIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
ts_updateTIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
回答by Guido Hendriks
As far as I know, there's no workaround for that restriction. You'll need to manually set (at least) one of the timestamps, the easiest way is just add updated = NOW()to the UPDATE-query.
据我所知，该限制没有解决方法。您需要手动设置（至少）其中一个时间戳，最简单的方法就是添加updated = NOW()到UPDATE-query 中。
回答by duffymo
You'll need two columns: CREATE_TIME and UPDATE_TIME.
您将需要两列：CREATE_TIME 和 UPDATE_TIME。
You might want to add CREATE_USER and UPDATE_USER.
您可能想要添加 CREATE_USER 和 UPDATE_USER。
Perhaps you'd want to have a 1:many relationship with name of column changed, old and new values.
也许您希望与已更改的列名称、旧值和新值建立 1:many 关系。
It's all part of change data capture. You could have CDC tables that are updated using triggers.
这都是变更数据捕获的一部分。您可以使用触发器更新 CDC 表。
回答by silvo
I would leave the current timestamp the way you suggested and fill in the created_at field with current date on insert.
我会按照您建议的方式保留当前时间戳，并在插入时使用当前日期填写 created_at 字段。
