MySQL数据类型--日期时间

自接触学习MySQL已有一段时间了，对于MySQL的基础知识还是有一定的了解的。在这一路学习过来，每次不管看书还是网上看的资料，对于MySQL数据类型中的时间日期类型总是一扫而过，不曾停下来认认真真的研究学习。最近在图书馆借了一本关于MysQL的书籍，打算全面的学习研究一遍。

在之前，我对于时间日期数据类型不怎么感冒，也没怎么用过这一类型。在我的做项目里用到存贮时间的数据，我都是采用int整型数据类型来存储，即是存储时间戳。但是在后面学习MySQL优化的时候，就有一个原则就是存储数据时应采用最小占用空间的数据类型。int类型是4个字节，TIMESTAMP也是4个字节，但是在需要使用日期时，时间戳还需要进一步转换，而TIMESTAMP类型数据就不需要了。

所以说认真学习了解每一个知识点是必要的！

二、时间日期数据类型总概况

 　　MySQL中有多种表示时间日期的数据类型，主要有YEAR、TIME、DATE、DATETIME、TIMESTAMP等。每一种数据类型都有存储的时间日期格式、以及取值范围，因此在使用时间日期数据类型的时候需要选取最佳的数据类型。

下图列出了几种数据类型：

三、细讲

1、YEAR

year用于存储年，存储时只需要一个字节，插入数据时可以使用各种格式指定YEAR值。

常见的插入格式解析：

a、四位字符串或者数字格式，范围为“1901”~“2155”，多少即为多少。

b、两位字符串格式，范围为“00”~“99”，"00"~"69"被转化为20**（例如：“16”转化为“2016”），“70”~“99”被转化为19**

c、两位数字格式，范围为1~99，1~69被转化为20**（例如：1转化为2001），70~99被转化为19**

2、TIME

time用于存储时间信息，存储时需要三个字节。从上图中可以了解到time的存储格式，还有数据范围。其中HH小时大于一天的24小时，可以为八百多的数字。这是因为这个类型不仅仅可以用于存储一天的某个时间还可以记录一段时间的间隔。所以小时就24小时。插入数据可以有多种格式。

常见插入格式解析：

a、"D HH:MM:SS"格式字符串格式。还可以使用非严格语法插入，例如“HH:MM:SS”、"D HH:SS"、“MM:SS”、"D HH"、”SS“（D表示天，取值范围为0~34）。在存入数据库是D将会被转换为D*24+HH，最终将当做小时进行入库存储。

b、”HHMMSS“没间隔的字符串格式或者HHMMSS没间隔的数字格式，非严格语法插入可以为，MMSS、SS、”MMSS“等等。要注意的是：1、这两种插入格式采用非严格语法的时候（即为时分秒不完整，只有时分等等），最右边的数据将默认为秒数，例如插入2318时，最终入库为002318。 2、分钟与秒数的数据要有实际意义，假若·MM为65超过59，那么将会发生错误。

3、DATE

date用于存储日期，没有时间部分，存储时需要三个字节。插入数据可以有多种格式。

常见插入格式解析：

a、”YYYY-MM-DD“、"YYYYMMDD"。

b、”YY-MM-DD“、”YYMMDD“，以两位数字来插入年份，这里主要区别”00~69”与“70~99”，前者表示20**，后者表示19**。

c、数字格式YY-MM-DD、YYMMDD，两位数字插入年份，区别于上面一样。

d、使用函数插入，CURRENT_DATE()、NOW()

4、DATETIME

datetime用于存储日期时间数据，存储时需要八个字节。插入数据可以有多种格式。

常见插入格式解析：

a、”YYYY-MM-DD HH:MM:SS“、"YYYYMMDDHHMMSS"，最终插入数据库都为2017-01-17 23:55:59格式。

b、”YY-MM-DD HH:MM:SS“、"YYMMDDHHMMSS"，两位数字插入年份，主要区分“00~69”与“70~99”，区别于上面一样。

c、数字格式插入，YYYYMMDDHHMMSS、YYMMDDHHMMSS，两位数字插入年份问题与上述一致。

5、TIMESTAMP

timestamp用于存储日期时间数据，与datetime存储数据格式一样，二者主要区别在于取值范围。timestamp存储需要四个字节，它的取值范围为“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” UTC，而datetime取值范围为“1000-01-01 00:00:00” ~ "9999-12-31 23:59:59"（UTC：Coordinated Universal Time 世界时间标准） 。插入格式也有多种。

常见插入格式解析：

与DATETIME一致，可以参考上面。

四、小结

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

（以上是自己的一些见解，若有不足或者错误的地方请各位指出）

作者：壹叶随风

声明：本博客文章为原创，只代表本人在工作学习中某一时间内总结的观点或结论。转载时请在文章页面明显位置给出原文链接。

MySQL数据类型--日期时间

标签：原则   日期类   默认   没有   href   rsa   存储类型   入库   style   

小编还为您整理了以下内容，可能对您也有帮助：

数据库中 日期的几种表示方法

Mysql中经常用来存储日期的数据类型有三种：Date、Datetime、Timestamp。

【1】Date数据类型：用来存储没有时间的日期。Mysql获取和显示这个类型的格式为“YYYY-MM-DD”。支持的时间范围为“1000-00-00”到“9999-12-31”。

【2】Datetime类型：存储既有日期又有时间的数据。存储和显示的格式为 “YYYY-MM-DD HH:MM:SS”。支持的时间范围是“1000-00-00 00:00:00”到“9999-12-31 23:59:59”。

【3】Timestamp类型：也是存储既有日期又有时间的数据。存储和显示的格式跟Datetime一样。支持的时间范围是“1970-01-01 00:00:01”到“2038-01-19 03:14:07”。

扩展资料

切记不要用字符串存储日期

这种存储日期的方式的优点还是有的，就是简单直白，容易上手。但是，这是不正确的做法，主要会有下面两个问题：

1、字符串占用的空间更大。

2、字符串存储的日期比较效率比较低（逐个字符进行比对），无法用日期相关的 API 进行计算和比较。

DateTime 类型没有时区信息的

DateTime 类型是没有时区信息的（时区无关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。

当你的时区更换之后，比如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。不要小看这个问题，很多系统就是因为这个问题闹出了很多笑话。

Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。

mysql 时间字段用什么类弄，长度是多少

mysql时间字段用：Date、Datetime和Timestamp。

（1）“Date”数据类型：用于存储没有时间的日期。 Mysql获取并显示此类型的格式为“ YYYY-MM-DD”，支持的时间长度范围是“ 1000-00-00”至“ 9999-12-31”。

（2）“Datetime”类型：存储日期和时间的数据。 存储和显示格式为“ YYYY-MM-DD HH：MM：SS”， 支持的时间长度范围是“ 1000-00-00 00:00:00”到“ 9999-12-31 23:59:59”。

（3）“Timestamp”类型：存储日期和时间的数据。 存储和显示格式与日期时间相同。 支持的时间长度范围是“ 1970-01-01 00:00:01”至“ 2038-01-19 03:14:07”。

所有不符合上述格式的数据都将转换为0值的相应类型。

扩展资料：

MySQL主要分为三种类型：字符串，日期和数字（每种类型实际上都有一个二进制类型，但并不常用）。

*字符串分为定长char和不定长度varchar，char以声明的长度为准，而varchar的长度与值的长度和字符集有关。

*数字和日期均为固定长度的字段。 不同的类型具有不同的长度。 例如，Datetime是5.6.4之后的5个字节，之前的是8个字节； timestamp是4个字节，因为其存储整数，小数部分的长度通常与位数有关。

Mysql支持的数据类型有哪些

Mysql支持的多种数据类型主要有：数值数据类型、日期/时间类型、字符串类型。 

1.整数数据类型及其取值范围：

类型

说明

存储需求（取值范围）

tinyint    很小整数    1字节（[0~255]、[-128~127]）; 255=2^8-1;127=2^7-1    

smallint    小整数    2字节（0~65535、-32768~32767） ;65535=2^16-1    

mediumint    中等    3字节（0~16777215） ;16777215=2^24-1    

int(integer)    普通    4字节（0~4294967295） ;4294967295=2^32-1    

bigint    大整数    8字节（0~18446744073709551615）;18446744073709551615=2^64-1    

浮点数&定点数：

类型名称

说明

存储需求

float    单精度浮点数    4字节    

double    双精度浮点数    8字节    

decimal    压缩的“严格”定点数    M+2字节    

注：定点数以字符串形式存储，对精度要求高时使用decimal较好；尽量避免对浮点数进行减法和比较运算。 

2.时间/日期类型： 

year范围：1901~2155; 

time格式：‘HH:MM:SS’（如果省略写，并且没有冒号，则默认最右起2位为秒，再到分，最后到时）； 

插入系统当前时间：insert into 表名 values(current_date()),(now()); 

date类型：‘YYYY-MM-DD’； 

datetime（日期+时间）：‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’，取值范围：‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’； 

timestamp格式同datetime，但在存储时需要4个字节(datetime需要8字节)，并且以UTC（世界标准时间）进行存储（即timestamp会随设置的时区而变化，而datetime存储的绝不会变化）；timestamp的范围：1970-2037。 

3.字符串类型： 

text类型：tinytext、text、mediumtext、longtext;

类型

范围

tinytext    255=2^8-1    

text    65535=2^16-1    

mediumtext    16777215=2^24-1    

longtext    4294967295=4GB=2^32-1    

char的存储需求是定义时指定的固定长度；varchar的存储需求是取决于实际值长度。 

set类型格式：set（’值1’,’值2’…) ——可以有0或者多个值，对于set而言，若插入的值为重复的，则只娶一个。插入的值乱序，则自动按顺序插入排列。插入不正常值，则忽略。 

二进制类型： 

bit（M）——保存位字段值（位字段类型），M表示值的位数； 

eg:select BIN(b+0) from 表名；—–b为列名;b+0表示将二进制的结果转换为对应的数字的值，BIN（）函数将数字转换为二进制。 

blog——-二进制大对象，用来存储可变数量的数据。

数据类型

存储范围(字节)

tinyblog    最多255=2^8-1 字节    

bolg    最多65535=2^16-1 字节    

mediumblog    最多16777215=2^24-1 字节    

longblog    最多4294967295=4GB=2^32-1 字节    

MySql建表时日期类型的出理

mysql(5.5)所支持的日期时间类型有：DATETIME、 TIMESTAMP、DATE、TIME、YEAR。

1.DATETIME 用于表示 年月日 时分秒，是 DATE 和 TIME 的组合，并且记录的年份比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。

2.TIMESTAMP

TIMESTAMP 用于表示 年月日 时分秒，但是记录的年份比较短暂。

TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是  不一样的。

表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。

TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

3.DATE

DATE 用于表示 年月日，如果实际应用值需要保存 年月日 就可以使用 DATE。

4.TIME

TIME 用于表示 时分秒，如果实际应用值需要保存 时分秒 就可以使用 TIME。

5.YEAR

YEAR 用于表示 年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。 默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

资料拓展：

每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下会报错，并以零值存储。

插入或更新时，日期时间类型允许“不严格”语法，以DATETIME为例（其他日期时间类型雷同）：

YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 格式的字符串。任何符号都可以用作日期部分或时间部分的间隔符。例如：“14-06-18 14:54:10”、“14*06*18 14.54.10”、“14+06+18 14=54=10”是等价的。对于包含日期时间的字符串值，如果月、日、时、分、秒的值小于10，不需要指定两位数。例如：“2014-2-3 2:3:6”、“2014-02-03 02:03:06”是等价的。

YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的字符串。如果字符串对于日期时间类型是合法的就可以解释为日期时间类型。例如：“20140618145410” 和 “140618145410”将被解释为 “2014-06-18 14:54:10” ，但是 “20140618145480” 是不合法的（秒数不合法），将被解释为 “0000-00-00 00:00:00”。

YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的数字。如果该数字对日期时间类型是合法的就可以解释为日期时间类型。例如：“20140618145410” 和 “140618145410” 将被解释为 “2014-06-18 14:54:10” 。数值的长度应为6、8、12、14。如果数值长度是 8 或 14 位长，则假定为 YYYYMMDD 或 YYYYMMDDHHMMSS 格式。如果数值为 6 或 12 位长，则假定为 YYMMDD 或 YYMMDDHHMMSS 格式。