mysql 的datetime和timestamp的知识小结

总结下mysql的datetime和timestamp两种类型的相关知识

 

mysql 的datetime和timestamp的区别

1. datetime的取值范围是1000-01-01 00:00:00 到9999-12-31 23:59:59 而timestamp仅为1970-01-01 00:00:00 到2037 23:59:59
2. timestamp具有自动初始化和自动更新的特性：自动初始化为当前日期和时间，当发生变化时自动更新为当前时间
3. timestamp可以自动转换为客户端时区
4. timestamp 只需要4个字节而datetime需要8个字节

对于timestamp还有以下特性：

1. 一张表中可以同时有多个TIMESTAMP类型列，但是只能有一列具有上述特性，其他列都会被初始化为0测试验证如下：

create table test (id int(32) ,time1 timestamp, time2 timestamp); 

insert test values(1);

insert test values(2);

select * from test;
+------+---------------------+---------------------+
| id   | time1               | time2               |
+------+---------------------+---------------------+
|    1 | 2014-03-04 11:51:56 | 0000-00-00 00:00:00 |
|    2 | 2014-03-04 11:51:58 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
       

可以看出只有time1被初始化了当前时间

而如果执行

alter table test modify column `time2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

即看出不允许一张表同时有两个都是自动更新的。

而可以通过 设置为NULL将其值更新为当前时间

insert test(id,time2) values(3, NULL);

+------+---------------------+---------------------+
| id   | time1               | time2               |
+------+---------------------+---------------------+
|    1 | 2014-03-04 11:51:56 | 0000-00-00 00:00:00 |
|    2 | 2014-03-04 11:51:58 | 0000-00-00 00:00:00 |
|    3 | 2014-03-04 11:56:07 | 2014-03-04 11:56:07 |
+------+---------------------+---------------------+

mysql 建表时， 如果不对timestamp做任何限制，则默认就是自动初始化和自动更新的特性

show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(32) DEFAULT NULL,
  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

同时对于timestamp 的not null，说明其不能保存NULL值，但是仍然可以给该列赋NULL，数据库会自动将其保存为当前日期和时间

如果希望timestamp自动初始化但是不自动更新，可以只使用DEFAULT CURRENT_TIMESTAMP