总结下mysql的datetime和timestamp两种类型的相关知识
mysql 的datetime和timestamp的区别
create table test (id int(32) ,time1 timestamp, time2 timestamp);
mysql 建表时, 如果不对timestamp做任何限制,则默认就是自动初始化和自动更新的特性
- datetime的取值范围是1000-01-01 00:00:00 到9999-12-31 23:59:59 而timestamp仅为1970-01-01 00:00:00 到2037 23:59:59
- timestamp具有自动初始化和自动更新的特性:自动初始化为当前日期和时间,当发生变化时自动更新为当前时间
- timestamp可以自动转换为客户端时区
- timestamp 只需要4个字节而datetime需要8个字节
对于timestamp还有以下特性:
- 一张表中可以同时有多个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 |
+------+---------------------+---------------------+
+------+---------------------+---------------------+
| 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
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 |
+------+---------------------+---------------------+
| 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 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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