MySQL--时间戳属性2

在MySQL 5.6版本中引入参数explicit_defaults_for_timestamp设置，该参数会影响Timestamp的默认属性。

同时在MySQL 5.6版本中中，去除一张表只能有一个TIMESTAMP列的限制，允许单表中使用多个时间戳列。

===============================================================================

在MySQL 5.6中，当参数explicit_defaults_for_timestamp=OFF时：

1、TIMESTAMP列如果没有明确指定为NLL,则默认为NOT NULL
2、TIMESTAMP列如果明确指定为NULL,则会增加默认值NULL
3、表中第一个TIMESTAMP列，如果没有声明为NULL属性、DEFAULT或者 ON UPDATE，会自动分配 DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP 属性。
4、表中第二个TIMESTAMP列，如果没有声明为NULL或者DEFAULT子句，默认自动分配’0000-00-00 00:00:00′。插入行时没有指明改列的值，该列默认分配’0000-00-00 00:00:00′，且没有警告。

如使用下面脚本创建：
CREATE TABLE tb2004(
id INT PRIMARY KEY,
c1 TIMESTAMP,
c2 TIMESTAMP,
c3 TIMESTAMP);
然后SHOW CREATE TABLE tb2004会发现创建脚本为：
CREATE TABLE `tb2004` (
  `id` int(11) NOT NULL,
  `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

===============================================================================

在MySQL 5.6中，当参数explicit_defaults_for_timestamp=ON时：

1、TIMESTAMP列如果没有明确指定为NOT NLL则默认为NULL
2、表中TIMESTAMP列声明为NOT NULL时，则不会为列自动创建默认值，且该列不能显式插入NULL值。
3、表中第一个TIMESTAMP列不会自动分配 DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP 属性，需要显式声明。
4、表中第二各TIMESTAMP列与第一个TIMESTAMP列不会存在特殊设置。

如使用下面脚本创建：
CREATE TABLE tb2004(
id INT PRIMARY KEY,
c1 TIMESTAMP,
c2 TIMESTAMP,
c3 TIMESTAMP);
然后SHOW CREATE TABLE tb2004会发现创建脚本为：
CREATE TABLE `tb2004` (
  `id` int(11) NOT NULL,
  `c1` timestamp NULL DEFAULT NULL,
  `c2` timestamp NULL DEFAULT NULL,
  `c3` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

而如使用下面脚本创建:
CREATE TABLE tb2005(
id INT PRIMARY KEY,
c1 TIMESTAMP NOT NULL,
c2 TIMESTAMP NOT NULL,
c3 TIMESTAMP NOT NULL);
然后SHOW CREATE TABLE tb2005会发现创建脚本为：
CREATE TABLE `tb2005` (
  `id` int(11) NOT NULL,
  `c1` timestamp NOT NULL,
  `c2` timestamp NOT NULL,
  `c3` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

=================================================================

在MySQL5.7.8及之后版本中，参数sql_mode的默认值为：

ONLY_FULL_GROUP_BY 
STRICT_TRANS_TABLES 
NO_ZERO_IN_DATE 
NO_ZERO_DATE 
ERROR_FOR_DIVISION_BY_ZERO 
NO_AUTO_CREATE_USER 
NO_ENGINE_SUBSTITUTION

 

其中NO_ZERO_IN_DATE和NO_ZERO_DATE会严格限制时间字段和时间戳字段的值：

NO_ZERO_IN_DATE：要求年月日都为0或月日不为0
NO_ZERO_DATE：要求年月日中任一项不为0

当sql_mode中同时包含NO_ZERO_IN_DATE和NO_ZERO_DATE时，则不允许向时间字段和时间戳字段插入任何非法日期的值，即不允许插入'0000-00-00 00:00:00'的值。

 

=================================================================

总结和建议：
1、为避免受参数explicit_defaults_for_timestamp的影响，在建表时建议写全timestamp的所有属性。
2、时间戳字段主要用于记录行被插入或更新的时间，应避免向时间戳列显示插入NULL值或异常时间值。

=================================================================