MySql数据类型---⽇期时间类型的使⽤（含datetime和
timestamp的区别
说在前⾯
在这⼀路学习过来，每次不管看书还是⽹上看的资料，对于数据类型中的时间⽇期类型总是⼀扫⽽过，不曾停下来认认真真的研究学习。最近看了⼀本关于MySql的书籍，打算全⾯的学习研究⼀遍。
虽然每次都没有怎么注意，但是使⽤起来确实⾮常的糟糕。每次都转换起来⾮常不便。⽐如明明我只需要⽇期，却给我⼀个时间戳或者时分秒都有的东西之类的。
在之前，我对于时间⽇期数据类型不怎么感冒，也没怎么⽤过这⼀类型。在我的做项⽬⾥⽤到存贮时间的数据，我都是采⽤int整型数据类型来存储，即是存储时间戳。但是在后⾯学习MySQL优化的时候，就有⼀个原则就是存储数据时应采⽤最⼩占⽤空间的数据类型。int类型是4个字节，TIMESTAMP也是4个字节，但是在需要使⽤⽇期时，时间戳还需要进⼀步转换，⽽TIMESTAMP类型数据就不需要了。
时间⽇期数据类型总概况
MySQL中有多种表⽰时间⽇期的数据类型，主要有YEAR、TIME、DATE、DATETIME、TIMESTAMP
等。每⼀种数据类型都有存储的时间⽇期格式、以及取值范围，因此在使⽤时间⽇期数据类型的时候需要选取最佳的数据类型。
此处注意发现：Time类型HH的取值竟然是800+，⽽不是我们本以为的24以内的数字，具体原因，下⾯会有解释
各类型详细讲解
1、Year
见名之意，year⽤于存储年，存储时只需要⼀个字节，插⼊数据时可以使⽤各种格式指定YEAR值（⾮常节约内存，所以当你只需要年的时候，⽤此字段合适）。
⽀持的常见插⼊格式为：
1. （推荐甚⾄强制要求必须）四位字符串或者数字，范围为“1901”~“2155”，写多少即为多少
2. 两位字符串格式：范围为“00”“99”，"00""69"被转化为20**（例如：“16”转化为“2016”），“70”~“99”被转化为
19**（下同）
3. 两位数字格式，范围为199，169被转化为20**（例如：1转化为2001），70~99被转化为19**
2、Time
time⽤于存储时间信息，存储时需要三个字节。 虽然，⼩时的范围是0~~23，但是为了表⽰某种特殊需要的时间间隔，将Time类型的范围扩⼤了。⽽且还⽀持了负值。
⽀持的常见插⼊格式为：
1. （推荐甚⾄强制要求必须）字符串格式：‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’,'SS’等形式。举个例⼦，输
⼊‘30’，Time类型会⾃动转换为00：00：30。
2. ‘HHMMSS’格式的字符串或HHMMSS格式的数值表⽰，例如，输⼊‘123456’，Time类型会转换成12：34：56；输⼊
123456，Time类型会转换成12：34：56。如果输⼊0或者‘0’，那么TIME类型会转换为0000：00：00。
3. ‘D HH:MM:SS’格式的字符串表⽰。其中，D表⽰天数，取值范围是0~~34。保存时，⼩时的值等于（D*24+HH）。举个例⼦，
输⼊‘2 11：30：50’，Time类型会转换为59：30：50。
4. 使⽤current_time或者current_time()或者now()输⼊当前系统时间。（⼀般⽤于默认值）
SQL⽰例：
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '1 01:50:50');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '01:50:50');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '50:05');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '1 05:05');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '59');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', '66');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', '123456');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 123456);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 0);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', '0');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('11', now());
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('12', current_time);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('13', current_time());
Time类型专门⽤来存储时间数据，⽽且只占3个字节，所以如果只需要记录时间，选择Time类型是最合适的。
3、Date
date⽤于存储⽇期，没有时间部分，存储时需要三个字节。
MySQL中是以YYYY-MM-DD的形式显⽰date类型的值。
⽀持的常见插⼊格式为：
（推荐甚⾄强制要求必须） ‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串表⽰，这种⽅式可以表达的范围是‘1000-01-01’~‘9999-12-31’。
MySQL中还⽀持⼀些不严格的语法格式，任何标点都可以⽤来做间隔符。
如’YYYY/MM/DD‘，’YYYY@MM@DD‘，’YYYY.MM.DD‘等分隔形式。举个例⼦，输⼊’2011.3.8‘，date类型将转换为2011-03-08。
’YY-MM-DD‘或者’YYMMDD‘格式的字符串表⽰，其中’YY‘的取值，’00‘’69‘转换为20002069，’70‘’99‘转换为19701999。与year类型类似。
使⽤current_date或now()来输⼊当前系统时间。
SQL⽰例：
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '2008-08-08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '20080808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '2008@08@08');
-
- 格式出错，所以插⼊0000-00-00
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '2008#0808');
-- 格式没错，但是⼩于了date类型的最⼩值1000，但是数据库还是插进去了，我晕
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '0999-08-08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', '690808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', '700808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 690808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 700808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', '0');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('11', 0);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('12', now());
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('13', current_date);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('14', current_date());
4、Datetime
datetime类型使⽤8个字节来表⽰⽇期和时间。 MySQL中以‘YYYY-MM-DD HH:MM:SS’的形式来显⽰dateTime类型的值。
⽀持的常见插⼊格式为：
（推荐甚⾄强制要求必须）‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串表⽰。这种⽅式可以表达的范围是‘1000-01-01 00:00:00’~~‘9999-12-31 23:59:59’。
MySQL中还⽀持⼀些不严格的语法格式，任何的标点都可以⽤来做间隔符。情况与date类型相同，⽽且时间部分也可以使⽤任意的分隔符隔开，这与Time类型不同，Time类型只能⽤‘:’隔开呢。
使⽤now()来输⼊当前系统⽇期和时间。
SQL⽰例：
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('1', '2008-08-08 08:08:08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('2', '20080808080808');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('3', '2008@08@08 08*08*08');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('4', '69-01-01 11:11:11');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('5', '70-01-01 11:11:11');
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('6', 20080808080808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('7', 690808080808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('8', 700808080808);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('9', 0);
INSERT INTO `linkinframe`.`test` (`id`, `a`) VALUES ('10', now());
dateTime类型⽤来记录⽇期和时间，其作⽤等价于date类型和Time类型的组合。⼀个dateTime类型的
字段可以⽤⼀个date类型的字段和⼀个time类型的字段代替。但是如果需要同时记录⽇期和时间，选择dateTime类型是个不错的选择。
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中的date类型直接⽐较⼤⼩是按照字符串⽐较还是时间戳
Mysql在⽐较两种不同数据类型时，第⼀步是将他们转化为同⼀种类型，然后在⽐较。那么Date和String在⽐较的时候，⼀定是把String转化为Date吗？答案是Yes.
例⼦：
select * ders where ceate_record_time > '2019'
结果截图：
为什么会出现 2018 的字符串？
再举个例⼦：如果是⽤字符串⽐较，“2004-04-31"这个string应该⽐2004-01-01这个date来得⼤，但是
4-31是⼀个invalid的⽇期（4⽉是⼩⽉），会被转化成"0000-00-00”，所以2004-01-01 (⽇期) > “2014-04-31”。
所以，在存储⽅⾯：如果你是表⽰的时间，请尽量不要采⽤str类型来存储（虽然⼤多数情况下存⼊的效果⼀样，但不建议）。 在查询⽅⾯：如果你确实遇到的存储的是字符串，那么请⽤STR_TO_DATE函数转成⽇期格式在查询，形如：
select * from orders where date(str_to_date(`ceate_record_time`.`publish_date`,'%Y-%m-%d')) > '2019-0-0'
附：unsigned解释
整型的每⼀种都分有⽆符号（unsigned）和有符号（signed）两种类型（float和double总是带符号的），在默认情况下声明的整型变量都是有符号的类型。
如果需声明⽆符号类型的话就需要在类型前加上unsigned。⽆符号版本和有符号版本的区别就是⽆符号类型能保存2倍于有符号类型的正整数数据，⽐如16位系统中⼀个int能存储的数据的范围为-3276832767，⽽unsigned能存储的数据范围则是065535。由于在计算机中，整数是以补码形式存放的。根据最⾼位的不同，如果是1，有符号数的话就是负数；如果是⽆符号数，则都解释为正数。
简⽽⾔之就是由于Int型占4字节，也就是16位，2^16 = 65535，如果有符号位就+ -两边均分，如果没有就全给+
因此下⾯建表语句是被推荐的：
CREATE TABLE `fast_group_task` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
...
PRIMARY KEY (`id`)
unix时间戳转换日期格式) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='批量组建班级的任务';
Java中的 '0000-00-00 00:00:00’问题
在我们的数据库定义中，经常可以看到类似这样的定义：
`start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
这样定义的隐患其实是⽐较⼤的。如果我们链接Mysql的Url参数上不作为，那么查询就会报错如下：
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
通过此报错我们其实应该很敏感的得出两个结论：
1. MySql是允许0000-00-00 00:00:00这样的值存在的（否则就会报SQL执⾏的错，⽽不是现在的封装成java.sql.Timestamp的错）
2. Java中（⾄少java.sql.Timestamp这个类）是不能⽀持到0000-00-00 00:00:00这个时间的。
// 这句语句报错，也能说明我们的Timestamp根本就不能表⽰这个时间~~~
// 最主要的是，这种时间没有任何实际意义，就连MySql存储它都采⽤的是假时间来存储的，强烈不建议使⽤
System.out.println(Timestamp.valueOf("0000-00-00 00:00:00"));
我们的解决⽅案有两种： 1、jdbc的url后⾯，追加上参数：?zeroDateTimeBehavior=convertToNull，这样⼦Sql查出来的结果是
null，Java进⾏封装就没有问题了 2、修改timestamp字段的默认值，不再采⽤0000-00-00 00:00:00（⼀般⽤当前时间）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。