MySQL 错误“DEFAULT 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列”，即使我没有做错任何事
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23054394/
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 Error "There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause" even though I'm doing nothing wrong
提问by Simon Fontana Oscarsson
CREATE TABLE AlarmHistory
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value DOUBLE NOT NULL,
startedStamp TIMESTAMP NOT NULL,
finishedStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
);
When trying to create the above table I get the following error: "SQL Error (1293): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause".
在尝试创建上表时，我收到以下错误：“SQL 错误 (1293)：表定义不正确；在 DEFAULT 或 ON UPDATE 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列”。
My question is this a bug? Because sure, I have two TIMESTAMP columns, but only ONE of them have a default definition. When I remove startedStamp I have no errors.
我的问题是这是一个错误吗？当然，我有两个 TIMESTAMP 列，但其中只有一个具有默认定义。当我删除 startedStamp 时，我没有错误。
回答by Damien_The_Unbeliever
Per the MySQL manual, version 5.5, Automatic Initialization and Updating for TIMESTAMP
根据 MySQL 手册，版本 5.5，自动初始化和更新TIMESTAMP
With neither
DEFAULT CURRENT_TIMESTAMPnorON UPDATE CURRENT_TIMESTAMP, it is the same as specifying bothDEFAULT CURRENT_TIMESTAMPandON UPDATE CURRENT_TIMESTAMP.
既不是
DEFAULT CURRENT_TIMESTAMP也不是ON UPDATE CURRENT_TIMESTAMP，它与同时指定DEFAULT CURRENT_TIMESTAMP和相同ON UPDATE CURRENT_TIMESTAMP。
CREATE TABLE t1 (
ts TIMESTAMP
);
However,
然而，
With a constant, the default is the given value. In this case, the column has no automatic properties at all.
对于常量，默认值为给定值。在这种情况下，该列根本没有自动属性。
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0
);
So, this should work:
所以，这应该有效：
CREATE TABLE AlarmHistory
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value DOUBLE NOT NULL,
startedStamp TIMESTAMP DEFAULT 0 NOT NULL,
finishedStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
回答by Ankur Rastogi
This is the limitation in MYSQL 5.5 version. You need to update the version to 5.6.
这是 MYSQL 5.5 版本的限制。您需要将版本更新到 5.6。
I was getting this error in adding a table in MYSQL
我在 MYSQL 中添加表时遇到此错误
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause My new MYSQL
表定义不正确；在 DEFAULT 或 ON UPDATE 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列我的新 MYSQL
table looks something like this.
桌子看起来像这样。
create table table_name (col1 int(5) auto_increment primary key, col2 varchar(300), col3 varchar(500), col4 int(3), col5 tinyint(2), col6 timestamp default current_timestamp, col7 timestamp default current_timestamp on update current_timestamp, col8 tinyint(1) default 0, col9 tinyint(1) default 1);
创建表 table_name (col1 int(5) auto_increment 主键, col2 varchar(300), col3 varchar(500), col4 int(3), col5 tinyint(2), col6 timestamp default current_timestamp, col7 timestamp default current_timestamp on update current_timestamp, col8 tinyint(1) 默认 0, col9 tinyint(1) 默认 1);
After some time of reading about changes in different MYSQL versions and some of the googling. I found out that there was some changes that were made in MYSQL version 5.6 over version 5.5.
在阅读了一些关于不同 MYSQL 版本的变化和一些谷歌搜索之后。我发现在 MYSQL 5.6 版中对 5.5 版进行了一些更改。
This article will help you to resolve the issue. http://www.oyewiki.com/MYSQL/Incorrect-table-definition-there-can-be-only-one-timestamp-column
本文将帮助您解决问题。 http://www.oyewiki.com/MYSQL/Incorrect-table-definition-there-can-be-only-one-timestamp-column
