MySQL 早于 1980 年的日期时间值不正确

Question

同时，尝试将 .sql 文件导入我的数据库时，其中一个插入语句出现以下错误 -

ERROR 1292 (22007) at line 31504: Incorrect datetime value: '1936-01-31 00:00:00' for column 'BatchDate' at row 1. Operation failed with exitcode 1

我只遇到早于 1980 年的日期的这个错误。它只在我尝试通过导入语句或通过 WorkBench 导入转储时发生。如果我单独执行该语句，它可以正常工作。这是表结构和插入语句

DROP TABLE IF EXISTS `BatchEntry`;

CREATE TABLE `BatchEntry` (
  `BatchNo` INTEGER NOT NULL AUTO_INCREMENT, 
  `BatchDate` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BTax_ID` DOUBLE NULL DEFAULT 0, 
  `BPayor_No` DOUBLE NULL DEFAULT 0, 
  `BBroker_No` DOUBLE NULL DEFAULT 0, 
  `BHam_Cont` VARCHAR(4), 
  `BInv_Org_Date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BInv_Due_Date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BDate_Adv` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
  `BRec_Amt` DECIMAL(19,4) DEFAULT 0, 
  `BPaymnt_Com` LONGTEXT, 
  `BTrans_Count` INTEGER DEFAULT 0, 
  `BPrefix` VARCHAR(10), 
  `BStartNumber` INTEGER DEFAULT 0, 
  `BSuffix` VARCHAR(10), 
  `BCreated` TINYINT(1) DEFAULT 0, 
  `BAdvMethod` INTEGER DEFAULT 0, 
  INDEX (`BPayor_No`), 
  INDEX (`BTax_ID`), 
  PRIMARY KEY (`BatchNo`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

INSERT INTO `BatchEntry` (`BatchNo`, `BatchDate`, `BTax_ID`, `BPayor_No`, `BBroker_No`, `BHam_Cont`, `BInv_Org_Date`, `BInv_Due_Date`, `BDate_Adv`, `BRec_Amt`, `BPaymnt_Com`, `BTrans_Count`, `BPrefix`, `BStartNumber`, `BSuffix`, `BCreated`, `BAdvMethod`) VALUES (1396, '1936-01-31 00:00:00', 561986585, 4528, 749, 'BSR', '2005-12-30 00:00:00', '2006-01-30 00:00:00', '2006-01-31 00:00:00', 0, NULL, 14, 'MC', 24850, NULL, 1, 1);

Answer 1

Batchdate 不是 DATETIME 列而是 TIMESTAMP 列。 TIMESTAMP 的范围不包括此日期:

CREATE TABLE `BatchEntry` (
  `BatchNo` INTEGER NOT NULL AUTO_INCREMENT, 
  `BatchDate` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    -- it's TIMESTAMP

错误信息是

Incorrect datetime value: '1936-01-31 00:00:00'

早于 '1970-01-01 00:00:01'，超出数据类型 TIMESTAMP 的范围

The DATE, DATETIME, and TIMESTAMP Types

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

解决方案

将数据类型更改为DATETIME。

CREATE TABLE `BatchEntry` (
  `BatchNo` INTEGER NOT NULL AUTO_INCREMENT, 
  `BatchDate` DATETIME DEFAULT CURRENT_TIMESTAMP, 
  [...]

如果您使用的是 MySQL 5.6.5 或更新版本，那么您可以将数据类型更改为 DATETIME，因为此版本 DATETIME 也支持自动初始化。您似乎使用了较新的版本，因为您使用的是多个具有自动初始化功能的列。此功能是同时添加的。

Automatic Initialization and Updating for TIMESTAMP and DATETIME

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.

注意

对于 1970 年代的日期值，它也适用。