mysql 5.5和5.6版本关于timestamp插null和0的处理

Server version: 5.5.33-31.1-log Percona Server (GPL), Release rel31.1, Revision 566

mysql> CREATE TABLE `t1` (
`ID` int(11) NOT NULL DEFAULT ‘1‘,
`NAME` varchar(10) NOT NULL DEFAULT ‘‘,
`CREATE_TIME` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 (create_time) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+—-+——+———————+
| ID | NAME | CREATE_TIME |
+—-+——+———————+
| 1 | | 2015-04-15 17:27:09 |
+—-+——+———————+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
从上面实验看，在5.533版本中，create_time虽然定义为not null ,但是实际是能插入null只的，而且自动转换为了current_time。接下来看5.6的操作：
Server version: 5.6.22-71.0-log Percona Server (GPL), Release 71.0, Revision 726

mysql> CREATE TABLE `t1` (
`ID` int(11) NOT NULL DEFAULT ‘1‘,
`NAME` varchar(10) NOT NULL DEFAULT ‘‘,
`CREATE_TIME` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from t1;
Empty set (0.00 sec)

mysql> insert into t1 (create_time) values (null);
ERROR 1048 (23000): Column ‘CREATE_TIME‘ cannot be null
mysql> select * from t1;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
直接报错，拦截语句插入。在5.6中是通过开启参数来实现解决这个BUG的。下面是摘自官方文档的描述

With explicit_defaults_for_timestamp enabled, inserting NULL into a
TIMESTAMP NOT NULL column now produces an error (as it already did for other NOT NULL data types),
instead of inserting the current timestamp. (Bug #68472, Bug #16394472)

故如果想实现，插入到timestamp中的null变为当前时间的话，需要将explicit_defaults_for_timestamp设为关闭状态

SET GLOBAL explicit_defaults_for_timestamp = off;
1
当数据库为严格模式时，STRICT_TRANS_TABLES；严格模式不允许非法日期，例如’2004-04-31’。不允许日期使用“零”部分，例如’2004-04-00’或“零”日期。要想禁止，应在严格模式基础上，启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。

NO_ZERO_DATE
在严格模式，不要将 ‘0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_IN_DATE
在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。

设置了这两个参数时，插入到timestamp中的0将被拒绝操作；如果没有设置着两个参数，插入到timestamp中的0将变为’0000-00-00 00:00:00’默认值

从 5.6开始，timestamp 的默认行为已经是 deprecated 了。

在MySQL 5.6.6之前，TIMESTAMP的默认行为：

TIMESTAMP列如果没有明确声明NULL属性，默认为NOT NULL。（而其他数据类型，如果没有显示声明为NOT NULL，则允许NULL值。）设置TIMESTAMP的列值为NULL，会自动存储为当前timestamp。

表中的第一个TIMESTAMP列，如果没有声明NULL属性、DEFAULT或者 ON UPDATE，会自动分配 DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP 属性。

表中第二个TIMESTAMP列，如果没有声明为NULL或者DEFAULT子句，默认自动分配’0000-00-00 00:00:00′。插入行时没有指明改列的值，该列默认分配’0000-00-00 00:00:00′，且没有警告。
要关闭警告，需要加入下面的参数：

explicit_defaults_for_timestamp=true
1
重启MySQL后错误消失，这时TIMESTAMP的行为如下：

TIMESTAMP如果没有显示声明NOT NULL，是允许NULL值的，可以直接设置改列为NULL，而没有默认填充行为。
TIMESTAMP不会默认分配DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP属性。
声明为NOT NULL且没有默认子句的TIMESTAMP列是没有默认值的。往数据表中插入列，又没有给TIMESTAMP列赋值时，如果是严格SQL模式，会抛出一 个错误，如果严格SQL模式没有启用，该列会赋值为’0000-00-00 00:00:00′，同时出现一个警告。（这和MySQL处理其他时间类型数据一样，如DATETIME）

也就是 explicit_defaults_for_timestamp 关闭了 timestamp 类型字段锁拥有的一些会让人感到奇怪的默认行为，加入了该参数之后，如果还需要为 timestamp类型的字段指定默认行为，那么就需要显示的在创建表时显示的指定。explicit_defaults_for_timestamp 也就是这个意思：显示指定默认值为timestamp类型的字段。