MySQL中各种日期/时间字段类型的优点和缺点是什么？ [英] What are the pros and cons of the various date/time field types in MySQL?

问题描述

MySQL中的日期和时间可以存储为DATETIME，TIMESTAMP和INTEGER（从01/01/1970开始的秒数）。特别是在LAMP堆栈下开发的优点和缺点是什么？

Date and time in MySQL can be stored as DATETIME, TIMESTAMP, and INTEGER (number of seconds since 01/01/1970). What are the benefits and drawbacks of each, particularly when developing under a LAMP stack?

推荐答案

• TIMESTAMP存储在MySQL专有方法中（尽管它基本上只是一个由年，月，日，小时，分钟和秒组成的字符串），此外，每当插入或更改记录时，会自动更新TIMESTAMP类型的字段，没有提供明确的字段值：

  • TIMESTAMP is stored in a MySQL proprietary method (though it's basically just a string consisting of year, month, day, hour, minutes and seconds) and additionally, a field of type TIMESTAMP is automatically updated whenever the record is inserted or changed and no explicit field value is given:

    mysql> create table timestamp_test(
        id integer not null auto_increment primary key, 
        val varchar(100) not null default '', ts timestamp not null); 
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into timestamp_test (val) values ('foobar');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from timestamp_test;
    +----+--------+----------------+
    | id | val    | ts             |
    +----+--------+----------------+
    |  1 | foobar | 20090122174108 |
    +----+--------+----------------+
    1 row in set (0.00 sec)
    
    mysql> update timestamp_test set val = 'foo bar' where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from timestamp_test;
    +----+---------+----------------+
    | id | val     | ts             |
    +----+---------+----------------+
    |  1 | foo bar | 20090122174123 |
    +----+---------+----------------+
    1 row in set (0.00 sec)
    
    mysql> 
    

  
  

  • DATETIME是日期和时间的标准数据类型，与日期和MySQL中的时间函数。我可能在实践中使用这个

  • DATETIME is the standard data type for dates and times which works in conjunction with the date and time functions in MySQL. I'd probably use this in practice

    这篇关于MySQL中各种日期/时间字段类型的优点和缺点是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！