DATETIM和TIMESTAMP类型所占的存储空间不同,前者8个字节,后者4个字节,这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,后者范围为1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.
其次,TIMESTAMP类型在默认情况下,insert、update 数据时,TIMESTAMP列会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。
第三,TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响
# TIMESTAMP特性 根据时区自动更新时间
mysql> alter table user add add_time TIMESTAMP default CURRENT_TIMESTAMP after age;
mysql> insert into user(name) values('haha');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+-------+-----+---------------------+
| id | name | age | add_time |
+----+-------+-----+---------------------+
| 21 | 02O | 0 | 0000-00-00 00:00:00 |
| 22 | hello | 0 | 0000-00-00 00:00:00 |
| 23 | hello | 0 | 0000-00-00 00:00:00 |
| 24 | haha | 0 | 2015-11-08 03:27:51 |
+----+-------+-----+---------------------+
4 rows in set (0.00 sec)
mysql> set time_zone='+8:00';
Query OK, 0 rows affected (0.02 sec)
#注意时间变了
mysql> select * from user;
+----+-------+-----+---------------------+
| id | name | age | add_time |
+----+-------+-----+---------------------+
| 21 | 02O | 0 | 0000-00-00 00:00:00 |
| 22 | hello | 0 | 0000-00-00 00:00:00 |
| 23 | hello | 0 | 0000-00-00 00:00:00 |
| 24 | haha | 0 | 2015-11-08 16:27:51 |
+----+-------+-----+---------------------+