DATETIME与TIMESTAMP比较与使用

2019-02-16 09:07:11   MySQL
  MySQL  

DATETIME&TIMESTAMP

今天要给之前存在的表添加一个publish_time,然后之前记得一直都是习惯性的用TIMESTAMP来作为这种用MySQL自动添加时间字段的数据格式,DATETIME来作为其他一些记录时间的数据格式。看网上很多的文章,这里不得不吐槽一下,百度出来的前几条基本都是一样的,无非是换个人,换个网站,内容基本都是复制的第一个人的,试想一下:第一个人对某一个知识点认知有点偏差,那后来的人呢?单纯的听说,单纯的引用,不去自己实验是不推荐的。

就http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp这个网页上一些回答和一些评论,然后自己测试,得出以下拙见,如有不当,还请指正!

有一个观点是TIMESTAMP不能为NULL

我不信!于是我没忍住,手一抖建了个表

create table if not exists test_vv(
    id int(6) not null auto_increment primary key,
    word text
) engine = MyISAM default charset utf8;
# 添加一个字段ttime 类型是 TIMESTAMP
alter table test_vv add dtime datetime null;
alter table test_vv add ttime timestamp null;

你猜怎么着?没错,执行成功!也就是说是TIMESTAMP也是可以接受NULL值。所以,在这点上二者是没有区别的!

还有一个区别是时区改变对二者的影响

时区的改变对TIMESTAMP是有影响的,对DATETIME是没影响的。

mysql> select * from test_vv;
+----+------+---------------------+---------------------+
| id | word | dtime               | ttime               |
+----+------+---------------------+---------------------+
|  1 | test | 2017-01-18 02:23:07 | 2017-01-18 02:23:07 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | -08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> set global time_zone='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select * from test_vv;
+----+------+---------------------+---------------------+
| id | word | dtime               | ttime               |
+----+------+---------------------+---------------------+
|  1 | test | 2017-01-18 02:23:07 | 2017-01-18 02:23:07 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

mysql> select * from test_vv;
+----+------+---------------------+---------------------+
| id | word | dtime               | ttime               |
+----+------+---------------------+---------------------+
|  1 | test | 2017-01-18 02:23:07 | 2017-01-18 18:23:07 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到,在改变时区后,当前session下两者是一样不变的,为什么说是当前session,因为大家可以发现,我exit之后,重新连接数据库,再次查询,数据格式为TIMESTAMP的ttime是发生了改变的。也就是说时区改变会改变后续的session中TIMESTAMP类型数据,但是任何情况下DATETIME的是不变的。

二者区间的差别

还有一个比较是区间的比较,看有人说DATETIME:The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

这个我不清楚对不对,但是我把0000-00-00 00:00:00插入到两种数据格式的字段,是可以执行成功的。所以我不清楚是从什么纬度来规定二者范围的。如果真的是有范围的,那么生日很容易击中TIMESTAMP的下限,而类似银行贷款,或者保险的截止日期很容易击中TIMESTAMP的上限,而且到2038年也并不是很遥远了,在这种情况下,建议用DATETIME来记录这些比较固定的数据;TIMESTAMP用来记录数据的插入和变动情况。

其他

附:添加或者修改字段 记录数据插入或者更新时间 SQL

#添加一列插入数据时createtime更新为当前时间
ALTER TABLE tablename ADD COLUMN createtime timestamp NULL DEFAULT CURRENT_TIMESTAMP;
#修改一列插入数据时createtime更新为当前时间,两种写法都可以
ALTER TABLE tablename MODIFY createtime timestamp  NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE tablename CHANGE createtime createtime timestamp  NULL DEFAULT CURRENT_TIMESTAMP;
#插入修改的时候createtime更新为当前操作时间,只要在后面加“ON UPDATE CURRENT_TIMESTAMP”, 即可,只举一例,其他相同
ALTER TABLE tablename ADD COLUMN createtime timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

然后,还通过把TIMESTAMP换成DATETIME发现,效果貌似是一样的,DATETIME也是可以实现的。 不过要注意一点就是,数据“真”的变更才会更新时间。例如:name原来就是vien,然后你set name = 'vien',这种情况下是不会更新时间的,而在set name = 'shavien' 这种情况下时间是会更新的。

vien.tech版权所有,允许转载,但转载请注明出处和原文链接: https://viencoding.com/article/59
欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

猜你喜欢


评论

There are no comments yet.
未登录

登录后即可发表评论

登录或注册

亲情非友情链接