在 MySQL 5.6 中比较 timestamp 和 datetime 有什么不同
|
小编给大家分享一下MySQL 5.6中timestamp和datetime区别有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ MySQL会根据当前时区转化TIMESTAMP值，在查询时候会根据当前时区来处理。 mysql> create table test2(a int(20)); Query OK, 0 rows affected (0.07 sec) mysql> insert into test2 values(1466929145); Query OK, 1 row affected (0.00 sec) mysql> select * from test2; +------------+ | a | +------------+ | 1466929145 | +------------+ 1 row in set (0.00 sec) mysql> select from_unixtime(a) from test2; +---------------------+ | from_unixtime(a) | +---------------------+ | 2016-06-26 08:19:05 | +---------------------+ 1 row in set (0.00 sec) mysql> insert into test2 values(14669291450); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select from_unixtime(a) from test2; +---------------------+ | from_unixtime(a) | +---------------------+ | 2016-06-26 08:19:05 | | 2038-01-19 03:14:07 | +---------------------+ 2 rows in set (0.00 sec) mysql> insert into test2 values(14669291450); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> select from_unixtime(a) from test2; +---------------------+ | from_unixtime(a) | +---------------------+ | 2016-06-26 08:19:05 | | 2038-01-19 03:14:07 | | 2038-01-19 03:14:07 | +---------------------+ 3 rows in set (0.00 sec) 查看当前时区 mysql> show variables like 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | +00:00 | +---------------+--------+ 1 row in set (0.00 sec) 更改会话时区参数后，可以看到查询出来的时间发生了变化 mysql> set session time_zone='+01:00'; Query OK, 0 rows affected (0.00 sec) mysql> select from_unixtime(a) from test2; +---------------------+ | from_unixtime(a) | +---------------------+ | 2016-06-26 09:19:05 | | 2038-01-19 04:14:07 | | 2038-01-19 04:14:07 | +---------------------+ 3 rows in set (0.00 sec) datetime不受时区的影响 mysql> create table test3(a datetime); Query OK, 0 rows affected (0.10 sec) mysql> select * from test3; Empty set (0.00 sec) mysql> insert into test3 values(now()); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.02 sec) mysql> select * from test3; +---------------------+ | a | +---------------------+ | 2017-02-13 10:02:20 | +---------------------+ 1 row in set (0.00 sec) mysql> set session time_zone='+10:00'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | +10:00 | +---------------+--------+ 1 row in set (0.00 sec) mysql> select * from test3; +---------------------+ | a | +---------------------+ | 2017-02-13 10:02:20 | +---------------------+ 1 row in set (0.00 sec) 以上是“MySQL 5.6中timestamp和datetime区别有哪些”这篇文章的所有内容，感谢各位的阅读！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
