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區別有哪些”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！