关于MySQL中存储时间格式的问题

1、MySQL中存储存储时间的格式可以分为四种类型：

其中：

（1）、跟整数相关的两种（INT和BIGINT）

 （2）、MySQL内置的格式两种（TIMESTAMP和DATETIME）

2、关于整数相关的两种（INT和BIGINT）:

UNSIGNED INT(32位)可以存储的数据大概是43亿，大概是10位，可以存储精确到秒的Unix时间，最大存储时间到2100年左右，所以足够存储，不需要用BIGINT UNSIGNED（64位）来存储（如果只是精确到秒）需要比INT多一倍的存储空间，即使我们指定了BIGINT(10)，其实际的存储空间与BIGINT是一样的。

3、MySQL中内置的存储日期的类型有：

DATETIME(8个字节,即64位，显示的时间范围：1001年到9999年)和TIMESTAMP(4个字节，即32位，显示的时间范围：1970到2038年)

4、举例说明上述四种日期的用法：

CREATE TABLE tbl_tim_test(

    id int UNSIGNED NOT NULL AUTO_INCREMENT,

    name VARCHAR(5) NOT NULL,

    update_time int UNSIGNED,

    active_time datetime default NULL,

    create_time timestamp,

    PRIMARY KEY(id)

)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

在上述的建表中timestamp会被自动设置为：

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

即timestamp会自动设置缺省时间值。

insert into tbl_tim_test values(1, "tim", 1484400134, "2017-01-14", "2017-01-14");

其中时间戳1484400134是精确到秒。如果插入毫秒级别就会数据Out of range value错误。

插入后的数据为：

mysql> select * from tbl_tim_test;

+----+------+-------------+---------------------+---------------------+

| id | name | update_time | active_time         | create_time         |

+----+------+-------------+---------------------+---------------------+

|  1 | tim  |  1484400134 | 2017-01-14 00:00:00 | 2017-01-14 00:00:00 |

+----+------+-------------+---------------------+---------------------+

即使没有输入小时分钟秒，也会自动补零，可以精确到秒级。

insert into tbl_tim_test values(1, "tim", 1484400134, "2017-01-14 12:0:0", "2017-01-14");

即使插入这样的数据MySQL也是可以解析的。

mysql> select * from tbl_tim_test;

+----+------+-------------+---------------------+---------------------+

| id | name | update_time | active_time         | create_time         |

+----+------+-------------+---------------------+---------------------+

|  1 | tim  |  1484400134 | 2017-01-14 00:00:00 | 2017-01-14 00:00:00 |

|  2 | tim  |  1484400134 | 2017-01-14 12:00:00 | 2017-01-14 00:00:00 |

+----+------+-------------+---------------------+---------------------+

以下这几种都是可以存储成上面的格式：

insert into tbl_tim_test values(2, "tim", 1484400134, "2017-01-14 12:0:01", "2017-01-14");

insert into tbl_tim_test values(4, "tim", 1484400134, "2017/01/14 12:0:01", "2017-01-14");

insert into tbl_tim_test values(5, "tim", 1484400134, "20170114", "2017-01-14");

insert into tbl_tim_test values(6, "tim", 1484400134, "20170114 ", "2017-01-14");

5、推荐日期格式使用MySQL内置的DATETIME，如果一定要用整数的话就用int unsigned，不要去用bigint。