【方向盘】MySql数据类型---日期时间类型的使用（含datetime和timestamp的区别） 0000-00-00 00:00:00问题解释(中)

5、timestamp

timestamp类型使用4个字节来表示日期和时间。

timestamp类型与dateTime类型显示的格式是一样的。

支持的常见插入格式为：

同datetime

二者主要区别在于取值范围。

timestamp存储需要四个字节，它的取值范围为“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” （和时区有关）

而datetime取值范围为“1000-01-01 00:00:00” ~ “9999-12-31 23:59:59”（和时区无关，怎么存入怎么返回，对程序员友好）

SQL示例：同datetime（但使用的是current_timestamp和now()）

INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', null);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', 'NULL');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', current_timestamp());

从数据库显示的结果来看，timestamp的范围确实很小的，2069明显的超过了2038，所以数据库插入0。

在MySQL 5.6.5版本之前，Automatic Initialization and Updating只适用于TIMESTAMP，而且一张表中，最多允许一个TIMESTAMP字段采用该特性。从MySQL 5.6.5开始，Automatic Initialization and Updating同时适用于TIMESTAMP和DATETIME，且不限制数量。

datetime和timestamp的比较

1、timestamp相对于datetime的不同之处：

（1.1），使用current_timestamp来输入系统当前日期与时间

（1.2），输入null时，系统会输入系统当前日期与时间

（1.3），无任何输入时，系统会输入null。

资料上面说系统会输入系统当前日期与时间，但是我自己尝试了下，如果输入null的时候，数据库中也是null，郁闷。 估摸和MySql版本有关

2、timestamp类型还有一个很大的特殊点，就是时间是根据时区来显示的。

例如，在东八区插入的timestamp类型为2009-09-30 14:21:25，在东七区显示时，时间部门就变成了13:21:25，在东九区显示时，时间部门就变成了15:21:25。

3、需要显示日期与时间，timestamp类型需要根据不同地区的时区来转换时间，但是，timestamp类型的范围太小，其最大时间为2038-01-19 11:14:07。

如果插入时间的比这个大，将会数据库插入0000-00-00 00:00:00。所以需要的时间范围比较大，还是选择dateTime类型比较安全。

MySQL中如何表示当前时间？

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

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

NOW()

LOCALTIME

LOCALTIME()

LOCALTIMESTAMP

LOCALTIMESTAMP()

小结

了解MySQL的日期时间数据类型对于选取一种适合存储类型是很有必要的。假若只有存储年份可以选取YEAR、仅存储时间可以选择TIME、又或者需要存储完整日期时间，那么可以根据实际情况选取DATATIME（推荐）或者TIMESTAMP数据类型。

附：MySql常用转换函数

unix_timestamp(),

unix_timestamp(date),

from_unixtime(unix_timestamp),

from_unixtime(unix_timestamp,format)

select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

附：MySql各大数据类型占用字节数

修正：varchar最大大小是65532字节。char是定长(每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足)，varchar是变长

MySQL中int(M)和tinyint(M)数值类型中M值的意义