当前位置 主页 > 服务器问题 > Linux/apache问题 >

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

    栏目:Linux/apache问题 时间:2019-11-26 16:23

    一、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 1
    mysql> 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)