当未给出默认值时，MySQL时间戳的默认值无效。

Question

请看下面的SQL语句。

CREATE SCHEMA IF NOT EXISTS `scheduler`;
USE `scheduler` ;
CREATE TABLE IF NOT EXISTS `scheduler`.`JobHistory` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `Job` INT NOT NULL,
  `StartTime` TIMESTAMP NOT NULL,
  `FinishTime` TIMESTAMP NOT NULL,
  PRIMARY KEY (`Id`),
  INDEX `fk_JobHistory_Job_idx` (`Job` ASC));

出现了错误ErrorCode: 1067. Invalid default value for 'Finish Time' 但是我并没有给完成时间设置默认值，还有另一个时间戳StartTime和这个时间戳完全相同，但是我并没有遇到任何异常。

- Thomas

---

有趣的是，如果我移除StartTime列或FinishTime列中的任意一列，它就可以正常工作。看起来MySQL不允许没有默认值的第二个时间戳。 - Thomas

Answer 1

尽管 @jsnplank 的观点是正确的，即时间戳被不同对待，你应该考虑为这两个特殊列使用 datetime 数据类型，但他未能解释错误信息。

错误信息很可能是 mysql 处理时间戳字段时没有提供默认值和 sql 模式设置的组合结果。

1. 你将两个时间戳列定义为非空，并且没有设置任何特定的默认值。这意味着第一个时间戳列的默认值将是 current_timestamp()，并且每当记录更改时也将更新为 current_timestamp()。这就是为什么第一个时间戳字段不会生成错误消息，无论哪个是第一个都可以。

   但是，如果你没有显式地定义默认值，则第二个非空时间戳列的默认值将为 '0000-00-00 00:00:00'。

   请参见此博客文章了解更多详情。

2. 可能在你的服务器上显式启用或作为严格 sql 模式的一部分，启用了no_zero_date sql 模式。如果你想将 '0000-00-00 00:00:00' 设置为默认值或将该值插入任何日期字段中，此 sql 模式将生成错误。

因此，你可以在表中使用时间戳数据类型，但使第二个列可为空或提供0或任何有效日期(例如纪元)作为显式默认值。

由于你正在将开始和结束日期标记为这些字段，所以使用 datetime 而不是 timestamp 数据类型可能是个好主意。

- Shadow

Answer 2

对于StartTime和FinishTime列，您应该使用DATETIME数据类型。TIMESTAMPS具有非常特定的用途。请参见http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

- jsnplank

Answer 3

引用2000年的文章并不真正有帮助，因为自那时以来已经发生了很多变化，可能不再准确。如其他相关问题中已经提到的，TIMESTAMP值相对于UTC的1970年1月1日参考特定时间点。相反，DATETIME值只是存储一些日期和时间，没有任何时间点的参考。它们更像是显示值，墙上的时钟向您显示某个值。如果您想追踪何时发生某事，2018-01-12 14:00:00的日期时间在不同的时区可能是不同的。

另一方面，TIMESTAMP 始终存储为 UTC 时间，当读取或在日期时间函数中使用时，会自动转换回连接或数据库默认时区。因此，当您的连接设置为 +02:00 时，将存储在 TIMESTAMP 列中的实际值是 2018-01-12 12:00:00 而不是 2018-01-12 14:00:00。当您使用 +05:00 的连接读取该列时，您将看到 2018-01-12 17:00:00。无论数据库或连接设置的时区是什么，DATETIME 值始终保持为 2018-01-12 14:00:00。

因此，如果要跟踪事件发生或应该发生的时间，可以使用 TIMESTAMP。当您只想存储与时区无关的固定日期时间时，请使用 DATETIME（例如，用户应在凌晨 2 点收到电子邮件，因为对于每个人来说，凌晨 2 点都是相同的）。

- TheLevti