MySQL CURRENT_TIMESTAMP 作为默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11400147/
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 CURRENT_TIMESTAMP as DEFAULT
提问by Bart Platak
While creating a table I am getting the following error:
在创建表时，我收到以下错误：
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
The problem is that I don't actually have two columns TIMESTAMPwith CURRENT_TIMESTAMPas default, neither I am using ON UPDATEclause.
问题是，我实际上没有两列TIMESTAMP同CURRENT_TIMESTAMP为默认值，无论我使用ON UPDATE条款。
The DDL query I'm trying to execute is
我试图执行的 DDL 查询是
CREATE TABLE user(
/* Basic Information */
id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(255) NOT NULL UNIQUE,
password CHAR(40) NOT NULL,
/* System status information */
active BOOL NOT NULL DEFAULT FALSE,
validated BOOL NOT NULL DEFAULT FALSE,
date_validated TIMESTAMP,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
/* Index */
PRIMARY KEY (id)
) Engine=InnoDB;
What's causing the issue?
是什么导致了这个问题？
回答by Fahim Parkar
You can use two timestamp in one table. For default, use DEFAULT field first and then the rest timestamp fields.
您可以在一张表中使用两个时间戳。默认情况下，首先使用 DEFAULT 字段，然后使用其余的时间戳字段。
Below query should work.
下面的查询应该可以工作。
CREATE TABLE myTable
(
id INT,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_validated TIMESTAMP
);
Demo at sqlfiddle
sqlfiddle 上的演示
回答by MER
I think it's important to note that this limitation is no longer present as of MySQL 5.6.5. The limitation has been removed and some additional functionality has been added. Specifically: multiple TIMESTAMP as well as DATETIME fields can have an automated default which will set the field to the current timestamp/datetime as explained in the following blog post (apparently written by the person who made the fix): http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html
我认为重要的是要注意，从 MySQL 5.6.5 开始，此限制不再存在。该限制已被删除，并添加了一些附加功能。具体来说：多个 TIMESTAMP 以及 DATETIME 字段可以有一个自动默认值，它将字段设置为当前时间戳/日期时间，如以下博客文章（显然由修复人员编写）中所述：http://optimize- this.blogspot.com/2012/04/datetime-default-now-finally-available.html
More Specifics
Additionally it may be valuable to read up on exactly how to produce default date & time values.
As outlined in the link below you can set both DATETIME & TIMESTAMP data types so the column will have a DEFAULT value of the current date & time of the INSERT operation for a given row.
Alternately you can set them ON UPDATE to have the current date & time set in the field on an UPDATE operation.
The Link:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
更多细节
此外，详细了解如何生成默认日期和时间值可能很有价值。
正如下面的链接中所述，您可以设置 DATETIME 和 TIMESTAMP 数据类型，以便列将具有给定行的 INSERT 操作的当前日期和时间的 DEFAULT 值。或者，您可以将它们设置为 ON UPDATE 以在 UPDATE 操作的字段中设置当前日期和时间。
链接：http:
//dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
Here's what both would look like in a CREATE TABLE statement:
下面是两者在 CREATE TABLE 语句中的样子：
CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
,ts2 TIMESTAMP DEFAULT NOW()
);
It's worth pointing out that CURRENT_TIMESTAMP is an alias to NOW().
Additionally setting a TIMESTAMP or DATETIME field to NULL will change the columns DEFAULT to null instead of a date time value, (again see the link for specifics & more details).
Another potentially valuable point is that DATETIME can contain a date range from:
1000-01-01 to 9999-12-31
while TIMESTAMP has a range of:
1970-01-01 00:00:01 to 2038-01-19 03:14:07
For additional comparisons between DATETIME & TIMESTAMP read this MySQL manual pagecomparing them.
值得指出的是 CURRENT_TIMESTAMP 是 NOW() 的别名。
此外，将 TIMESTAMP 或 DATETIME 字段设置为 NULL 会将列 DEFAULT 更改为空，而不是日期时间值，（再次参见链接以获取详细信息和更多详细信息）。
另一个可能有价值的点是 DATETIME 可以包含从
1000-01-01 到 9999-12-31的日期范围，
而 TIMESTAMP 的范围是：
1970-01-01 00:00:01 到 2038-01-19 03： 14:07
有关 DATETIME 和 TIMESTAMP 之间的其他比较，请阅读比较它们的MySQL 手册页。
回答by Jason Swett
Allow me to quote my own blog post: "MySQL, for whatever reason, only allows one auto-updating timestamp per table. There are a number of ways around this but not all of the ways don't suck."
请允许我引用我自己的博客文章：“无论出于何种原因，MySQL 只允许每个表有一个自动更新时间戳。有很多方法可以解决这个问题，但并非所有方法都不会出错。”
The way I recommend is to use a trigger for each timestamp:
我推荐的方法是为每个时间戳使用触发器：
CREATE TRIGGER customer_create BEFORE INSERT ON `customer`
FOR EACH ROW SET NEW.created_at = NOW(), NEW.updated_at = NOW();
See the post itself for more details.
有关更多详细信息，请参阅帖子本身。
