MySQL中datetime和timestamp的区别及使用详解

一、MySQL中如何表示当前时间？

其实，表达方式还是蛮多的，汇总如下：

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

NOW()

LOCALTIME

LOCALTIME()

LOCALTIMESTAMP

LOCALTIMESTAMP()

二、关于TIMESTAMP和DATETIME的比较

一个完整的日期格式如下：YYYY-MM-DD HH:MM:SS[.fraction]，它可分为两部分：date部分和time部分，其中，date部分对应格式中的“YYYY-MM-DD”，time部分对应格式中的“HH:MM:SS[.fraction]”。对于date字段来说，它只支持date部分，如果插入了time部分的内容，它会丢弃掉该部分的内容，并提示一个warning。

如下所示：

mysql> create table test(id int,hiredate date);Query OK, 0 rows affected (0.01 sec)mysql> insert into test values(1,'20151208000000');Query OK, 1 row affected (0.00 sec)mysql> insert into test values(1,'20151208104400');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> show warning;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1mysql> select * from test;+------+------------+| id  | hiredate  |+------+------------+|  1 | 2015-12-08 ||  1 | 2015-12-08 |+------+------------+2 rows in set (0.00 sec)

注：第一个没提示warning的原因在于它的time部分都是0

TIMESTAMP和DATETIME的相同点：

1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

 TIMESTAMP和DATETIME的不同点：

1> 两者的存储方式不一样

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

下面，我们来验证一下

首先创建两种测试表，一个使用timestamp格式，一个使用datetime格式。

mysql> create table test(id int,hiredate timestamp);Query OK, 0 rows affected (0.01 sec)mysql> insert into test values(1,'20151208000000');Query OK, 1 row affected (0.00 sec)mysql> create table test1(id int,hiredate datetime);Query OK, 0 rows affected (0.01 sec)mysql> insert into test1 values(1,'20151208000000');Query OK, 1 row affected (0.00 sec)mysql> select * from test;+------+---------------------+| id  | hiredate      |+------+---------------------+|  1 | 2015-12-08 00:00:00 |+------+---------------------+1 row in set (0.01 sec)mysql> select * from test1;+------+---------------------+| id  | hiredate      |+------+---------------------+|  1 | 2015-12-08 00:00:00 |+------+---------------------+1 row in set (0.00 sec)

两者输出是一样的。

其次修改当前会话的时区

mysql> show variables like '%time_zone%'; +------------------+--------+| Variable_name  | Value |+------------------+--------+| system_time_zone | CST  || time_zone    | SYSTEM |+------------------+--------+2 rows in set (0.00 sec)mysql> set time_zone='+0:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from test;+------+---------------------+| id  | hiredate      |+------+---------------------+|  1 | 2015-12-07 16:00:00 |+------+---------------------+1 row in set (0.00 sec)mysql> select * from test1;+------+---------------------+| id  | hiredate      |+------+---------------------+|  1 | 2015-12-08 00:00:00 |+------+---------------------+1 row in set (0.01 sec)