mysql 自动存储记录创建时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4870536/
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 automatically store record creation timestamp
提问by Good Guy
Is there some way mysql can store timestamp automatically in a record row whenever that it is created. I was trying to use timestamp(data type) with current_timestamp as default value but then realised this will get updated everytime the record is updated. I just need something that will store create timestamp.
mysql 是否可以通过某种方式在创建时间戳时自动将时间戳存储在记录行中。我试图使用时间戳（数据类型）和 current_timestamp 作为默认值，但后来意识到每次更新记录时都会更新。我只需要一些可以存储创建时间戳的东西。
Thanks
谢谢
回答by OMG Ponies
Set the DEFAULT constraint to use CURRENT_TIMESTAMP:
将DEFAULT 约束设置为使用 CURRENT_TIMESTAMP：
CREATE TABLE ...
your_date_column DATETIME DEFAULT CURRENT_TIMESTAMP
...
For an existing table, use the ALTER TABLE statement:
对于现有表，请使用ALTER TABLE 语句：
ALTER TABLE your_table
ALTER COLUMN date_column SET DEFAULT CURRENT_TIMESTAMP
Unless you specify a value to for the date_column, the default will be the date & time the INSERT statement was run. NULLand DEFAULTor valid values to use the default constraint otherwise, assuming the column is nullable.
除非您为 date_column 指定一个值，否则默认值将是运行 INSERT 语句的日期和时间。 NULL和DEFAULT或有效值以否则使用默认约束，假设列可以为空。
回答by btilly
You can get the full details on timestamps in MySQL at https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html.
您可以在https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html获取有关 MySQL 中时间戳的完整详细信息。
The point that you care about is that if you define a timestamp column as DEFAULT CURRENT_TIMESTAMP clause and don't have an ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
您关心的一点是，如果您将时间戳列定义为 DEFAULT CURRENT_TIMESTAMP 子句并且没有 ON UPDATE 子句，则该列的默认值具有当前时间戳，但不会自动更新。
But be warned. The obvious thing to want to do is to have two timestamp columns, one being the creation time and the other being the last update time. Unfortunately it is a documented MySQL limitation that MySQL does not support this. I have no idea why MySQL has such an odd limitation - no other major database has problems with this common use case.
但请注意。显而易见的事情是有两个时间戳列，一个是创建时间，另一个是最后更新时间。不幸的是，MySQL 不支持这一点是有记录的 MySQL 限制。我不知道为什么 MySQL 有这样一个奇怪的限制——没有其他主要数据库在这个常见用例上有问题。
回答by Merlin
FYI = "Datetime" is date and time fixed. "Timestamp" is variable date and time-- system time.
FYI = "Datetime" 是固定的日期和时间。“时间戳”是可变日期和时间——系统时间。
So, Have two columns. One Create Col, One Update Col.
所以，有两列。一个创建列，一个更新列。
回答by vidur punj
The following command will create a hello table
1. id integer
2. create_at with current time.
以下命令将创建一个 hello 表
1. id integer
2. create_at 与当前时间。
create table hello (id int, created_at datetime DEFAULT CURRENT_TIMESTAMP);
回答by Rakesh Dongarwar
SELECT * FROM test WHERE timestamp >= CURDATE() AND timestamp < CURDATE() + INTERVAL 1 DAY ORDER BY timestamp;
SELECT * FROM test WHERE timestamp >= CURDATE() AND timestamp < CURDATE() + INTERVAL 1 DAY ORDER BY timestamp;
