mysql 5.6 timestamp_mysql5.6 timestamp 变化，数据库版本升级到5.6需要注意此字段

1、版本，实测环境为centos6.6+mysql 5.6.25 社区版64bit

select @@version;

+------------+

| @@version  |

+------------+

| 5.6.25-log |

+------------+

2、系统参数应设置为on ,如下图：

show variables like '%timestamp%';

+---------------------------------+-------------------+

| Variable_name                   | Value             |

+---------------------------------+-------------------+

| explicit_defaults_for_timestamp | ON               |

| timestamp                       | 1438824047.023471 |

+---------------------------------+-------------------+

3、创建表

CREATE TABLE `timestamp3` (

->   `id` int(11) NOT NULL AUTO_INCREMENT,

->   `time1` timestamp NULL DEFAULT NULL,

->   `time2` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

->   `time3` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

->   PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected (0.00 sec)

4、插入数据，观察5.6版本timestamp有什么变化

1)insert into timestamp3 (id) values(1);

Query OK, 1 row affected (0.00 sec)

2) select * from timestamp3;

+----+-------+---------------------+---------------------+

| id | time1 | time2               | time3               |

+----+-------+---------------------+---------------------+

|  1 | NULL  | 2015-08-06 09:44:07 | 2015-08-06 09:44:07 |

+----+-------+---------------------+---------------------+

1 row in set (0.00 sec)

5、观察变化

1)update timestamp3 set id=id+1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

2)select * from timestamp3;

+----+-------+---------------------+---------------------+

| id | time1 | time2               | time3               |

+----+-------+---------------------+---------------------+

|  2 | NULL  | 2015-08-06 09:44:07 | 2015-08-06 09:45:18 |

+----+-------+---------------------+---------------------+

1 row in set (0.00 sec)

6、结论TIMESTAMP如果没有显示声明NOT NULL，是允许NULL值的，可以直接设置改列为NULL，而没有默认填充行为。

TIMESTAMP不会默认分配DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP属性。