mysql 时间戳列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/155054/
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 timestamp column
提问by Dónal
Is it possible to define a timestamp column in a MySQL table that will automatically be updated every time a field in the same row is modified? Ideally this column should initially be set to the time a row was inserted.
是否可以在 MySQL 表中定义一个时间戳列，该列将在每次修改同一行中的字段时自动更新？理想情况下，此列最初应设置为插入行的时间。
Cheers, Don
干杯，唐
采纳答案by Adam Bellaire
That is the default functionality of the timestampcolumn type. However, note that the format of this type is yyyymmddhhmmss (all digits, no colons or other separation).
这是时间戳列类型的默认功能。但是，请注意，此类型的格式为 yyyymmddhhmmss（所有数字，无冒号或其他分隔符）。
EDIT:The above comment about the format is only true for versions of MySQL < 4.1... Later versions format it like a DateTime
编辑：上述关于格式的评论仅适用于 MySQL < 4.1 版本......更高版本将其格式化为DateTime
回答by quickcel
You can use the timestamp column as other posters mentioned. Here is the SQL you can use to add the column in:
您可以像提到的其他海报一样使用时间戳列。以下是可用于在其中添加列的 SQL：
ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
This adds a column called 'lastUpdated' with a default value of the current date/time. When that record is updated (lets say 5 minutes later) that timestamp will automatically update to the current time.
这会添加一个名为“lastUpdated”的列，其默认值为当前日期/时间。当该记录更新时（假设 5 分钟后），时间戳将自动更新为当前时间。
回答by Do Will
This is what I have observed (MySql 5.7.11) -
这是我观察到的（MySql 5.7.11）-
The first TIMESTAMP column in the table gets current timestamp as the default value. So, if you do an INSERT or UPDATE without supplying a value, the column will get the current timestamp.
表中的第一个 TIMESTAMP 列获取当前时间戳作为默认值。因此，如果您在不提供值的情况下执行 INSERT 或 UPDATE，该列将获得当前时间戳。
Any subsequent TIMESTAMP columns should have a default value explicitly defined. If you have two TIMESTAMP columns and if you don't specify a default value for the second column, you will get this error while trying to create the table -
任何后续的 TIMESTAMP 列都应该有一个明确定义的默认值。如果您有两个 TIMESTAMP 列，并且没有为第二列指定默认值，则在尝试创建表时会出现此错误 -
ERROR 1067 (42000): Invalid default value for 'COLUMN_NAME'
错误 1067 (42000)：“COLUMN_NAME”的默认值无效
回答by Petruza
A MySQL timestamp is set with creation or update time only if their default value is set as it. ALTER TABLE some_table ADD when TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
Otherwise it works just like a DateTime field, only that it's relative to 1970/01/01 UTC, so it's an absolute point in time not depending on a specific timezone as is DateTime.
仅当它们的默认值设置为创建或更新时间时，MySQL 时间戳才被设置为它。ALTER TABLE some_table ADD when TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
否则它就像一个 DateTime 字段一样工作，只是它相对于 1970/01/01 UTC，所以它是一个绝对的时间点，不依赖于特定的时区，就像 DateTime。
回答by Joel Coehoorn
IIRC, the first column in a table of type timestamp is set when a record is created and cannot be changed. The 2nd column of type timestamp is set whenever a record is updated. Or vice versa.
IIRC，时间戳类型表中的第一列在创建记录时设置并且无法更改。每当更新记录时，都会设置时间戳类型的第二列。或相反亦然。
I haven't done much MySQL work, so you'll want to verify this.
我没有做过很多 MySQL 工作，所以你需要验证一下。
