MySQL timestamp类型探索

有个外部厂商的产品在我们的MySQL上执行DDL，爆出了这样的错误:

CREATE TABLE TP_TASKINSTANCE

(

   I_SRLNO              BIGINT NOT NULL ,

   D_ACCTDATE           DATE NOT NULL ,

   C_TASKTYPE           CHAR(1) NOT NULL,

   C_TASKSTATE          CHAR(1) NOT NULL,

   S_TASKMENT           VARCHAR(80),

   TS_BEGIN             TIMESTAMP,

   TS_END               TIMESTAMP,

   TS_SYSUPDATE         TIMESTAMP ,

   PRIMARY KEY (I_SRLNO)

);

ERROR 1067 (42000): Invalid default value for 'TS_END'

粗看没啥问题，详细分析下来，还跟timestamp类型的特征以及我们的sql_mode配置有关，来看下原因。

先交待下我们的环境:

MySQL 5.6.16

sql_mode配置: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

从官方文档，我们可以明确了解到如下信息:

◦ TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.   没有明确声明NULL属性实际是变成NOT NULL。这个值要设定NULL，则会设置成当前时间。

◦ The first TIMESTAMP column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.   表中的第一个timestamp类型列，如果不是设置NULL属性，或者显式指定default值或者ON UPDATE, 会默认加上DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP 属性，即会自动更新。

◦ TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs. 非第一个timestamp字段，如果没有指定NULL属性或default，则默认DEFAULT '0000-00-00 00:00:00'.

原因如下:

timestamp类型有几个特征(系统变量explicit_defaults_for_timestamp=off时，默认即是off)，使得它和其他数据类型有所差异:

1.如果表中非第一个TIMESTAMP类型字段，如果创建时没有指定NULL属性，没有指定default或者ON UPDATE特性，默认为赋予默认DEFAULT '0000-00-00 00:00:00'。这时候插入记录时不明确指定这一列，则写入'0000-00-00 00:00:00'

2.我们环境下默认的sql_mode设置中，包含NO_ZERO_IN_DATE，目的是期望所有的日期类型均有含义。

这两点的冲突，就导致了上面的问题。

解决方式:

1) explicit_defaults_for_timestamp=on，这样未指定NULL属性，就是NULL，和其他数据类型一致。但是在我们的环境属于非标准做法，不建议。

2) 类似的字段指定业务允许的默认值，例如'2000-01-01 00:00:01'或者current_timestamp，类似如下:

CREATE TABLE TP_TASKINSTANCE
(
   I_SRLNO              BIGINT NOT NULL ,
   D_ACCTDATE           DATE NOT NULL ,
   C_TASKTYPE           CHAR(1) NOT NULL,
   C_TASKSTATE          CHAR(1) NOT NULL,
   S_TASKMENT           VARCHAR(80),
   TS_BEGIN             TIMESTAMP not null default '2000-01-01 00:00:01' ,
   TS_END               TIMESTAMP not null default '2000-01-01 00:00:01',
   TS_SYSUPDATE         TIMESTAMP not null default '2000-01-01 00:00:01' ,
   PRIMARY KEY (I_SRLNO)
);

timestamp本身有时间范围限制, '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07'UTC，北京时区是东八区，按照东八区允许存入时的最小时间是'1970-01-01 08:00:01'。

3)  timestamp全部改成datetime类型，表现都是预期内。没有特殊时区要求，建议不要用timestamp类型。

进一步:

厂商根据我的建议，选了个对他们改造最小的方案，类型改为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP，自测也无误了。到我们线上环境部署，又有新报错了!

写入数据的时候，不指定TS_BEGIN字段，没问题，自动生成当前值；明确写入NULL，则直接报错

ERROR 1048 (23000): Column 'TS_BEGIN' cannot be null

这个我的测试场景下一直没法复现，想不明白，要来环境仔细一查，原来是别人给的环境（非标惹得祸！），按照厂商老的手册要求，设置了explicit_defaults_for_timestamp=on!

我们回过头来再好好看看这个参数.

字面意思也好理解:针对timestamp类型，明确默认值的特性。即启用后，和其他类型的表现一致:

1. 如果没有指定NOT NULL,默认就是允许NULL。设置NULL值有效，不会变成current_timestamp.
2. timestamp类型不会再被自动地加上默认当前值(DEFAULT CURRENT_TIMESTAMP)，或者自动更新(ON UPDATE CURRENT_TIMETAMP)
3. NOT NULL的TIMESTAMP，如果没有明确的默认值，则当作无default看待（和其他类型一样）
4. NOT NULL且有默认值的TIMESTAMP，不接受insert NULL的动作，无论sql_mode如何设置，都会报错。

顺便再5.7下验证了一下，和5.6表现一致。