为什么在 DEFAULT 子句中只能有一列 CURRENT _ TIMESTAMP？

小开

很久以前我也想过。我在我的历史中搜索了一下，我认为这篇文章: http://lists.mysql.com/internals/34919代表了 MySQL 的半官方地位(在 Oracle 介入之前;)

简而言之:

这个限制仅仅源于这个特性当前的方式在服务器和那里实现没有其他原因存在。

因此，他们的解释是“因为它是这样实现的”。听起来不太科学。我猜这一切都来自于某种古老的代码。在上面的帖子中提到了这一点: “从只有第一个时间戳字段是自动设置/更新时的结转”。

干杯！

小开

对于您来说，一个修复方法可以是将它放在 UpdatedDate 字段上，并且有一个触发器，只有在 AddedDate 为 null 时才使用 UpdatedDate 值更新 AddedDate 字段。

小开

将列的数据类型更改为 datetime
启动引爆器

例如:

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 ;

小开

为了避免这个问题，我们可以为时间戳提供一个默认值。

这篇文章给出了一个详细的解决方案: 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()
);

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

mysql> select now() as `before insert`;
+---------------------+
| before insert       |
+---------------------+
| 2022-04-29 18:43:58 |
+---------------------+
1 row in set (0.00 sec)


mysql> insert into test_table(stamp_created, stamp_updated) values(null, null);
Query OK, 1 row affected (0.00 sec)


mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  1 | 2022-04-29 18:43:58 | 2022-04-29 18:43:58 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)


mysql> select now() as `before sleep(3)`;
+---------------------+
| before sleep(3)     |
+---------------------+
| 2022-04-29 18:43:58 |
+---------------------+
1 row in set (0.00 sec)


mysql> DO SLEEP(3);
Query OK, 0 rows affected (3.00 sec)


mysql> select now() as `before update`;
+---------------------+
| before update       |
+---------------------+
| 2022-04-29 18:44:01 |
+---------------------+
1 row in set (0.00 sec)


mysql> update test_table set id = 2 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  2 | 2022-04-29 18:43:58 | 2022-04-29 18:44:01 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

小开

实际上是一个实现错误。

MySQL 的原生方法是自己更新创建日期(如果需要的话) ，并让 MySQL 像下面这样担心 时间戳 update date ? update date : creation date:

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

关于创建插入空:

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

时间戳的 NULL 值默认为 CURRENT _ TIMESTAMP。

在 MySQL 中，如果没有给出表的属性，那么表的第一个 TIMESTAMP 列同时获得 DEFAULT CURRENT_TIMESTAMP和 ON UPDATE CURRENT_TIMESTAMP属性。这就是为什么 TIMESTAMP 列的属性必须放在第一位，否则就会得到这个线程中描述的错误。

小开

最佳答案

这个限制，仅仅是由于历史和代码遗留的原因，在最近的 MySQL 版本中被取消了:

MySQL 5.6.5(2012-04-10，里程碑8)的变化

以前，每个表最多只能有一个 TIMESTAMP 列自动初始化或更新到当前日期和时间。此限制已取消。任何 TIMESTAMP 列定义都可以具有 DEFAULT CURRENT _ TIMESTAMP 和 ON UPDATE 的任意组合 CURRENT _ TIMESTAMP 子句。此外，现在可以使用这些子句有关更多信息，请参见 TIMESTAMP 和 DATETIME 的初始化和更新。

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

小开

试试这个:

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;

小开

这是 MYSQL 5.5版本的限制。您需要将版本更新到5.6。

Error

我在 MYSQL 中添加表时遇到了这个错误

表定义不正确; 只能有一个 TIMESTAMP 列在 DEFAULT 或 ON UPDATE 子句中使用 CURRENT _ TIMESTAMP

桌子看起来像这样。

Create table _ name (col1 int (5) auto _ Increment 主键 col2 Varchar (300) ，col3 varchar (500) ，col4 int (3) ，col5 tinyint (2) , Col6时间戳默认为 current _ time戳，col7时间戳默认为 Update current _ time戳上的 current _ time戳，col8 tinyint (1) 默认值0，col9 tinyint (1)默认值1) ;

经过一段时间的阅读不同的 MYSQL 版本的变化和一些谷歌。我发现 MYSQL 5.6版本比5.5版本做了一些更改。

本文将帮助您解决这个问题。 Http://www.oyewiki.com/mysql/incorrect-table-definition-there-can-be-only-one-timestamp-column

小开

综合各种答案:

在 MySQL 5.5中，DEFAULT CURRENT_TIMESTAMP和 ON UPDATE CURRENT_TIMESTAMP不能添加到 DATETIME上，而只能添加到 TIMESTAMP上。

规则:

1)每个表中最多可以有一个 TIMESTAMP列被自动(或手动[ 我的加法])初始化或更新到当前日期和时间。(MySQL 文档)。

所以只有一个 TIMESTAMP可以在 DEFAULT或 ON UPDATE子句中包含 CURRENT_TIMESTAMP

2)第一个 NOT NULL TIMESTAMP列没有像 created_date timestamp default '0000-00-00 00:00:00'这样的显式 DEFAULT值，将隐式地给予 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，因此后续的 TIMESTAMP列不能给予 DEFAULT或 ON UPDATE子句上的 CURRENT_TIMESTAMP

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)

其他选择(但 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;