Server Version:5.5.33-31.1-log Percona Server (GPL), Release rel31.1, Revision 566
mysql> CREATE TABLE ' T1 ' (
' ID ' int (one) not NULL DEFAULT ' 1 ',
' NAME ' varchar (ten) 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)
From the above experiment, in version 5.533, Create_time, although defined as NOT NULL, is actually able to insert null only, and is automatically converted to Current_time. Next look at the 5.6 operation:
Server Version:5.6.22-71.0-log Percona Server (GPL), Release 71.0, Revision 726
mysql> CREATE TABLE ' T1 ' (
' ID ' int (one) not NULL DEFAULT ' 1 ',
' NAME ' varchar (ten) 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)
Direct error, intercept statement insertion. In 5.6, this bug is solved by opening parameters. Here is a description from the official documentation
With explicit_defaults_for_timestamp enabled, inserting null to a timestamp NOT NULL column now produces an error
(as it already did for and not NULL data types), instead of inserting the current timestamp. (Bug #68472, bug #16394472)
MySQL 5.5 and 5.6 version about timestamp not null Type field about NULL handling