MySQL错误“在DEFAULT子句”中只能有一个具有CURRENT_TIMESTAMP的TIMESTAMP列“即使我没有做错 [英] MySQL Error "There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause" even though I'm doing nothing wrong

问题描述

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,
);

当尝试创建上表时，我得到以下错误：SQL错误（1293）：不正确表定义;在DEFAULT或ON UPDATE子句中只能有一个具有CURRENT_TIMESTAMP的TIMESTAMP列。

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".

我的问题是这是一个错误？因为肯定，我有两个TIMESTAMP列，但只有一个有一个默认定义。当我删除startedStamp时，没有错误。

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.

推荐答案

根据MySQL手册5.5版， TIMESTAMP 的自动初始化和更新

Per the MySQL manual, version 5.5, Automatic Initialization and Updating for TIMESTAMP

既不是 DEFAULT CURRENT_TIMESTAMP 也不是 ON UPDATE CURRENT_TIMESTAMP ，它与指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 相同。

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

CREATE TABLE t1 (
  ts TIMESTAMP
);

但是，

使用常数，默认值为给定值。在这种情况下，该列完全没有自动属性。

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
);

所以，这应该工作：

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
);

fiddle

这篇关于MySQL错误“在DEFAULT子句”中只能有一个具有CURRENT_TIMESTAMP的TIMESTAMP列“即使我没有做错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！