MySQL 为什么在 DEFAULT 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列？

Question

提问by ripper234

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause?

为什么在 DEFAULT 或 ON UPDATE 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列？

CREATE TABLE `foo` (
  `ProductID` INT(10) UNSIGNED NOT NULL,
  `AddedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=INNODB;

The error that results:

导致的错误：

Error Code : 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

错误代码：1293
表定义不正确；在 DEFAULT 或 ON UPDATE 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列

Answer 1

采纳答案by augustin

This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:

这个限制只是由于历史和代码遗留原因，在最近的 MySQL 版本中已经取消：

Changes in MySQL 5.6.5 (2012-04-10, Milestone 8)
Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

MySQL 5.6.5 中的变化（2012-04-10，里程碑 8）
以前，每个表最多有一个 TIMESTAMP 列可以自动初始化或更新为当前日期和时间。此限制已取消。任何 TIMESTAMP 列定义都可以具有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句的任意组合。此外，这些子句现在可以与 DATETIME 列定义一起使用。有关更多信息，请参阅 TIMESTAMP 和 DATETIME 的自动初始化和更新。

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

Answer 2

回答by Lachezar Balev

I also wondered that long time ago. I searched a bit in my history and I think that this post: http://lists.mysql.com/internals/34919represents the semi-official position of MySQL (before Oracle's intervention ;))

很久以前我也想知道。查了一下历史，觉得这个帖子：http: //lists.mysql.com/internals/34919代表了MySQL的半官方立场（在Oracle介入之前；））

In short:

简而言之：

this limitation stems only from the way in which this feature is currently implemented in the server and there are no other reasons for its existence.

此限制仅源于此功能当前在服务器中的实现方式，并且没有其他原因。

So their explanation is "because it is implemented like this". Doesn't sound very scientific. I guess it all comes from some old code. This is suggested in the thread above: "carry-over from when only the first timestamp field was auto-set/update".

所以他们的解释是“因为它是这样实现的”。听起来不太科学。我想这一切都来自一些旧代码。这是在上面的线程中建议的：“从只有第一个时间戳字段是自动设置/更新时结转”。

Cheers!

干杯!

Answer 3

回答by Scarlett

We can give a default value for the timestamp to avoid this problem.

我们可以给时间戳一个默认值来避免这个问题。

This post gives a detailed workaround: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

这篇文章给出了详细的解决方法：http: //gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

create table test_table( 
id integer not null auto_increment primary key, 
stamp_created timestamp default '0000-00-00 00:00:00', 
stamp_updated timestamp default now() on update now() 
);

Note that it is necessary to enter nulls into both columns during "insert":

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5; 
+----+---------------------+---------------------+ 
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)  
mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0  
mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       | 
+----+---------------------+---------------------+ 
|  3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec)

create table test_table( 
id integer not null auto_increment primary key, 
stamp_created timestamp default '0000-00-00 00:00:00', 
stamp_updated timestamp default now() on update now() 
);

请注意，在“插入”期间，有必要在两列中输入空值：

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5; 
+----+---------------------+---------------------+ 
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)  
mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0  
mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       | 
+----+---------------------+---------------------+ 
|  3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec)

Answer 4

回答by mooli

Indeed an implementation fault.

确实是实现错误。

The native approach in MySQL is to update a creation date yourself ( if you need one ) and have MySQL worry about the timestampupdate date ? update date : creation datelike so:

MySQL 中的本机方法是自己更新创建日期（如果您需要），并让 MySQL 担心时间戳，update date ? update date : creation date如下所示：

CREATE TABLE tracked_data( 
  `data` TEXT,
  `timestamp`   TIMESTAMP,
  `creation_date` TIMESTAMP                                   
) ENGINE=INNODB;

On creation Insert NULL:

在创建时插入 NULL：

INSERT INTO tracked_data(`data`,`creation_date`) VALUES ('creation..',NULL);

NULL values for timestamp are interperted as CURRENT_TIMESTAMP by default.

默认情况下，时间戳的 NULL 值被解释为 CURRENT_TIMESTAMP。

In MySQL the first TIMESTAMP column of a table gets both DEFAULT CURRENT_TIMESTAMPand ON UPDATE CURRENT_TIMESTAMPattribute, if no attributes are given for it. this is why TIMESTAMP column with attributes must come first or you get the error described in this thread.

在 MySQL 中，表的第一个 TIMESTAMP 列同时获取DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性，如果没有为其提供属性。这就是为什么带有属性的 TIMESTAMP 列必须首先出现，否则您会收到此线程中描述的错误。

Answer 5

回答by Feng-Chun Ting

Change data types of columns to datetime
Set trigger

将列的数据类型更改为日期时间
设置触发器

Such as:

如：

DROP TRIGGER IF EXISTS `update_tablename_trigger`;
DELIMITER //
CREATE TRIGGER `update_tablename_trigger` BEFORE UPDATE ON `tablename`
 FOR EACH ROW SET NEW.`column_name` = NOW()
//
DELIMITER ;

Answer 6

回答by user104309

Combining various answers :

结合各种答案：

In MySQL 5.5, DEFAULT CURRENT_TIMESTAMPand ON UPDATE CURRENT_TIMESTAMPcannot be added on DATETIMEbut only on TIMESTAMP.

在MySQL 5.5，DEFAULT CURRENT_TIMESTAMP并且ON UPDATE CURRENT_TIMESTAMP不能被添加上DATETIME，但只对TIMESTAMP。

Rules:

规则：

1) at most one TIMESTAMPcolumn per table could be automatically (or manually[My addition]) initialized or updated to the current date and time. (MySQL Docs).

1)TIMESTAMP每个表最多一列可以自动（或手动[我的添加]）初始化或更新为当前日期和时间。（MySQL 文档）。

So only one TIMESTAMPcan have CURRENT_TIMESTAMPin DEFAULTor ON UPDATEclause

所以只有一个TIMESTAMP可以有CURRENT_TIMESTAMPin DEFAULTorON UPDATE子句

2) The first NOT NULLTIMESTAMPcolumn without an explicit DEFAULTvalue like created_date timestamp default '0000-00-00 00:00:00'will be implicitly given a DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPand hence subsequent TIMESTAMPcolumns cannot be given CURRENT_TIMESTAMPon DEFAULTor ON UPDATEclause

2)NOT NULLTIMESTAMP没有显式DEFAULT值的第一列created_date timestamp default '0000-00-00 00:00:00'将被隐式赋予 a DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，因此后续TIMESTAMP列不能CURRENT_TIMESTAMP在DEFAULTorON UPDATE子句中给出

CREATE TABLE `address` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `village` int(11) DEFAULT NULL,
    `created_date` timestamp default '0000-00-00 00:00:00', 

    -- Since explicit DEFAULT value that is not CURRENT_TIMESTAMP is assigned for a NOT NULL column, 
    -- implicit DEFAULT CURRENT_TIMESTAMP is avoided.
    -- So it allows us to set ON UPDATE CURRENT_TIMESTAMP on 'updated_date' column.
    -- How does setting DEFAULT to '0000-00-00 00:00:00' instead of CURRENT_TIMESTAMP help? 
    -- It is just a temporary value.
    -- On INSERT of explicit NULL into the column inserts current timestamp.

-- `created_date` timestamp not null default '0000-00-00 00:00:00', // same as above

-- `created_date` timestamp null default '0000-00-00 00:00:00', 
-- inserting 'null' explicitly in INSERT statement inserts null (Ignoring the column inserts the default value)! 
-- Remember we need current timestamp on insert of 'null'. So this won't work. 

-- `created_date` timestamp null , // always inserts null. Equally useless as above. 

-- `created_date` timestamp default 0, // alternative to '0000-00-00 00:00:00'

-- `created_date` timestamp, 
-- first 'not null' timestamp column without 'default' value. 
-- So implicitly adds DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. 
-- Hence cannot add 'ON UPDATE CURRENT_TIMESTAMP' on 'updated_date' column.


   `updated_date` timestamp null on update current_timestamp,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8;

INSERT INTO address (village,created_date) VALUES (100,null);

mysql> select * from address;
+-----+---------+---------------------+--------------+
| id  | village | created_date        | updated_date |
+-----+---------+---------------------+--------------+
| 132 |     100 | 2017-02-18 04:04:00 | NULL         |
+-----+---------+---------------------+--------------+
1 row in set (0.00 sec)

UPDATE address SET village=101 WHERE village=100;

mysql> select * from address;
+-----+---------+---------------------+---------------------+
| id  | village | created_date        | updated_date        |
+-----+---------+---------------------+---------------------+
| 132 |     101 | 2017-02-18 04:04:00 | 2017-02-18 04:06:14 |
+-----+---------+---------------------+---------------------+
1 row in set (0.00 sec)

Other option (But updated_dateis the first column):

其他选项（但是updated_date是第一列）：

CREATE TABLE `address` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `village` int(11) DEFAULT NULL,
  `updated_date` timestamp null on update current_timestamp,
  `created_date` timestamp not null , 
  -- implicit default is '0000-00-00 00:00:00' from 2nd timestamp onwards

  -- `created_date` timestamp not null default '0000-00-00 00:00:00'
  -- `created_date` timestamp
  -- `created_date` timestamp default '0000-00-00 00:00:00'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8;

Answer 7

回答by HLGEM

Well a fix for you could be to put it on the UpdatedDate field and have a trigger that updates the AddedDate field with the UpdatedDate value only if AddedDate is null.

好吧，您的修复方法可能是将它放在 UpdatedDate 字段上，并有一个触发器，仅当 addedDate 为 null 时才使用 UpdatedDate 值更新 AddDate 字段。

Answer 8

回答by Shoaib Qureshi

Try this:

尝试这个：

CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = INNODB;

Answer 9

回答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。

Error

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

MySQL 为什么在 DEFAULT 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列？

提问by ripper234

采纳答案by augustin

回答by Lachezar Balev

回答by Scarlett

回答by mooli

回答by Feng-Chun Ting

回答by user104309

回答by HLGEM

回答by Shoaib Qureshi

回答by Ankur Rastogi

相关推荐

最近更新

标签

MySQL 为什么在 DEFAULT 子句中只能有一个带有 CURRENT_TIMESTAMP 的 TIMESTAMP 列？

提问by ripper234

采纳答案by augustin

回答by Lachezar Balev

回答by Scarlett

回答by mooli

回答by Feng-Chun Ting

回答by user104309

回答by HLGEM

回答by Shoaib Qureshi

回答by Ankur Rastogi

相关推荐

MySQL 中 GROUP_CONCAT 的反面是什么？

无法通过 socket '/var/lib/mysql/mysql.sock' 连接到本地 MySQL 服务器 (2)

MySQL order 子句中的未知列

MySQL mysql分组并排序每个组

相关推荐

最近更新

标签