返回
前端
分类

五种类型表示如下,MySQl中有多种表示日期和时间的数据类型

日期: 2020-01-02 08:02 浏览次数 : 112

Mysql的时间类型(大集合)

mysql(5.5)所支持的日期时间类型有:DATETIME、 TIMESTAMP、DATE、TIME、YEAR。
五种类型表示如下:

MySQL自学篇(三)

MySQL数据类型介绍

MySQL支持多种数据类型,主要有数值类型、日期类型、字符串类型

 

日期时间类型 占用空间 日期时间格式 最小值 最大值 零值表示
YEAR 1bytes YYYY 1901 2155 0000
TIME 3bytes HH:MM:SS -838:59:59 838:59:59 00:00:00
DATE 4bytes YYYY-MM-DD 1000-01-01 9999-12-31 0000-00-00
DATETIME 8bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31
23:59:59 0000-00-00 00:00:00
TIMESTAMP 4bytes YYYY-MM-DDHH:MM:SS 19700101080001 2038
年的某个时刻 00000000000000

数值类型

必赢备用网址 ,MySQL主要提供的整数类型有:TINYINT/SMALLINT/MEDIUMINT/INT(INTEGER)/BIGINT

整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

1、MySQL的五种日期和时间类型

当插入值超出有效取值范围时,系统会报错,并将零值插入到数据库中。

1、MySQL中整数类型

整数类型的取值范围

 

YEAR类型

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

 CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_time_1` year DEFAULT NULL,
  `create_time_2` year(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

当希望使用两位格式的YEAR时,要显示声明为year(2);当希望使用四位格式的YEAR时,可以直接声明为year,系统默认是四位,也可以显示声明为year(4).year(2)和year(4)赋值方法如下

  • year(4)赋值
    当类型为year(4)时,直接插入4位字符串或者4位数字即可,有效值范围是1901-2155,超过这个范围赋值,系统会给出一个警告,并插入零值0000
  • year(2)赋值
    1.插入2位字符串或者两位数字,这种情况下如果插入‘00’‘69’或者00-69,则相当于插入20002069;如果插入‘70’‘99’或者70-99,则相当于插入19701999。也可以看出当声明为year(2)时,其实year类型的有效范围已经被缩小了,且含义表达不如year(4)清楚明了.
 insert into test(create_time_1)values(2017);
 insert into test(create_time_1)values('2017');
 insert into test(create_time_2)values('00');
 insert into test(create_time_2)values('69');
 insert into test(create_time_2)values(70);
 insert into test(create_time_1)values(2255);#超出有效范围

必赢备用网址 1

从上图可以看出,当插入值超过1901-2155范围赋值,系统会给出一个警告,并插入零值0000.

2、浮点数类型和定点数类型

MySQL中使用浮点数和定点数表示小数。浮点数类型有两种,单精度浮点数(FLOAT)和双精度浮点数(DOUBLE)。定点类型只有DECIMAL。浮点类型和定点类型都可以用(M,N)来表示,其中M成为精度,表示总共的位数;N成为标度,表示小数的位数。

不论是定点还是浮点数,如果用户指定的精度超出精度范围,则会四舍五入进行处理

范例:

创建表test1,其中字段x,y,z数据类型依次为FLOAT(5,1)/DOUBLE(5,1)/DECIMAL(5,1)向表中插入数据5.51,5.12,5.123

create table test1

(

x FLOAT(5,1),

y DOUBLE(5,1),

z DECIMAL(5,1)

);

插入数据:

insert into test1 values (5.51,5.12,5.123);

查看数据:

select * from test1;

MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份,DATE表示日期,TIME表示时间,DATETIME和TIMESTAMP表示日期和实践。它们的对比如下:

TIME

TIME类型表示为“时:分:秒”,即 ‘HH:MM:SS’.其有效值范围是-838:59:59 838:59:59.一般而言小时范围是023,但是为了表示某些特殊时间间隔,MySQL将TIME的小时范围扩大了,而且支持负值.TIME类型只占3个字节,如果只是存储时间数据,它最合适了。
对TIME类型赋值,其格式有一下几种

  1. ‘HH:MM:SS’标准格式
  2. 'HHMMSS'格式,系统能够自动转化为标准格式。
  3. D HH:MM:SS’格式,此格式相当于插入‘(D*24+HH):MM:SS’。比如插入‘2 23:50:50’,相当于插入了‘71:50:50’。
  4. ‘HH:MM’或‘SS’格式,其效果是其他未被表示位的值赋为零值。比如插入‘30’,相当于插入了‘00:00:30’;如果插入‘11:25’,相当于插入了‘11:25:00’。
  5. ‘D HH’和‘D HH:MM’格式,效果按上面的例子可以推理出来了吧。
    如果我们想插入系统的当前时间,则可以插入CURRENT_TIME或者NOW()。
CREATE TABLE `test_time` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into test_time(create_time)values(current_time);
insert into test_time(create_time)values(now());
insert into test_time(create_time)values('11:11:11');
insert into test_time(create_time)values('121212');
insert into test_time(create_time)values('2 23:50:50');
insert into test_time(create_time)values('13:13');
insert into test_time(create_time)values('14');
#超出有效范围
insert into test_time(create_time)values('840:60:60');

必赢备用网址 2

3、日期与时间类型

MySQL中有多种表示日期的数据类型,主要有:DATETIME,DATE,TIMESTAMP,TIME,YEAR。

 

DATE

DATE类型表示为"年-月-日",即"YYYY-MM-DD".其有效值范围是1000-01-01-9999-12-31.除了标准语法格式"YYYY-MM-DD",MySQL还支持一些不严格的语法格式,分隔符“-”可以用“@”、“.”等其他符号替代。
在插入数据时,也可以使用“YY-MM-DD”格式,YY转化成对应的年份的规则与YEAR类型类似。
如果我们想插入系统的当前时间,则可以插入CURRENT_DATE或者NOW()。

 CREATE TABLE `test_date` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into test_date(create_date)values(current_date);
insert into test_date(create_date)values(now());
insert into test_date(create_date)values('2017-01-01');
insert into test_date(create_date)values('2017@02@01');
insert into test_date(create_date)values('10000-12-31');

必赢备用网址 3

A、YEAR

YEAR类型是一个单字节类型用于表示年,在存储是只要一个字节。可以使用各种格式指定YEAR的值

(1)以4位字符串或者4位数字格式表示的YEAR,范围是1901-2155.输入格式为:‘YYYY’或者YYYY。例如输入‘2010’或2010

(2)以2位字符串格式表示的YEAR,范围是‘00’——‘99’。‘00’——‘69’范围的值分别被转换为2000—2069和1970—1999范围的YEAR值。插入超过取值范围的值将被转换为2000

(3)以2位数字表示的YEAR,范围1~99.1~69和70~99范围的值分别被转换为2001~2069和1970~1999范围的YEAR值。0将被转换为0000而不是2000

范例:

创建数据表test2,定义数据类型为YEAR的字段y,插入2014,‘2014’,‘2166’

Insert into test2 values(2014),(‘2014’),(‘2166’);

TEAR ,字节数为1,取值范围为“1901——2155”

DATETIME

DATETIME就是 DATE 和 TIME 的组合标准格式为“YYYY-MM-DD HH:MM:SS”,具体赋值方法与上面各种类型的方法相似。

CREATE TABLE `test_datetime` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_datetime` datetime DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,##我看公司的是这样写的,为什么错了呢.
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

B、TIME

TIME类型用在只需要时间信息的值,在存储时需要3个字节。格式为‘HH:MM:SS’。HH表示小时;MM表示分钟;SS表示秒。使用各种格式指定TI ME值。

(1)采用‘D HH:MM:SS’格式的字符串。

也可以使用下面任何一种非严格的语法:‘HH:MM:SS’/‘HH:MM’/‘D HH:MM’/‘D HH’/‘SS’。这里D表示是日,可以去0-34之间的值。插入数据库时,D被转换为小时保存,格式为“D*24+HH”

(2)采用‘HHMMSS’格式的,没有间隔符的字符串或者HHMMSS格式的数值。

范例:

创建test3表,定义数据类型为TIME的字段t,向其中插入‘11:05:05’,‘23:23’,‘2 10:10’,‘3 02’,‘10’。

insert into test3 values ('11:05:05'),('23:23'),('2 10:10');

 

TIMESTAMP类型

TIMESTAMP 用于表示年-月-日 时:分:秒,但是记录的年份比较短暂。TIMESTAMP 和时区相关,更能反映当前时间。当插入日期时,会先转换为本地时区后再存放;当查询日期时,会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。它的插入也与插入其他日期和时间数据类型类似。如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP。

那么TIMESTAMP类型如何插入当前时间?

  1. 可以使用CURRENT_TIMESTAMP;
  2. 输入NULL,系统自动输入当前的TIMESTAMP
  3. 无任何输入,系统自动输入当前的TIMESTAMP。

TIMESTAMP是一个非常特殊的时间类型.

  • 如果表中只有一个TIMESTAMP类型的字段.系统会默认设置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
  • 如果表中有两个TIMESTAMP类型的字段,系统会如何应对呢.
    用以下定义来创建一个表.
CREATE TABLE `test_timestamp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_timestamp` timestamp,
`create_timestamp2` timestamp ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

用show来看看系统自动补全了什么

CREATE TABLE `test_timestamp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_timestamp2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

从系统补全结果可以看出 表中的第一个 TIMESTAMP 列系统会默认设置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,这与表中只有一个 TIMESTAMP 列设置是相同的。第二个 TIMESTAMP 列,系统会默认设置DEFAULT '0000-00-00 00:00:00',且没有on update设置.这就告诉我们,在我们自己定义表示表时,不可以这样

CREATE TABLE `test_timestamp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_timestamp` timestamp  DEFAULT CURRENT_TIMESTAMP,
`create_timestamp2` timestamp  DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

必赢备用网址 4

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

从报错结果中可以看出,一个表中可以有两个TIMESTAMP类型的列,但是这两个列不可以同时设置DEFAULT CURRENT_TIMESTAMP 或ON UPDATE CURRENT_TIMESTAMP 属性.

  • 时间类型为 Timestamp的列,即使此列规定NOT NULL,如果往这属性的列中插入Null值的话,仍可以插入NULL成功,且系统会插入一个当前时间.当有两个 Timestamp的列时,如果往这两个列中均插入Null值的话,则两个都会插入系统当前时间.
    注意:并不是所有的时间类型都是特例,datetime,date,time,year类型如果规定NOT NULL,则不可以插入NULL值.
  • 无任何输入.第一个系统会插入一个当前时间,第二列系统会插入一个零值.
 insert into test_timestamp(id) values(null);#无任何输入
 insert into test_timestamp values(null,null,null);#插入NULL

必赢备用网址 5

C、DATE类型

DATE类型用在仅仅需要日期的时候,没有时间部分,在存储时需要3个字节。日期格式为‘YYYY-MM-DD’

(1)以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示日期。

(2)以‘YY-MM-DD’或者‘YYMMDD’字符串表示日期,在这里YY表示两位的年。‘00~69’范围转换为‘2000~2069’;‘70~99’范围转换为‘1970~1999’。

(3)以YY-MM-DD或者YYMMDD格式表示日期。与前面类似,00-69表示2000-2069;70-99转换为1970-1999

(4)使用CURRENT_DATE或者NOW()插入当前系统日期。

范例:

创建数据表test4,定义数据类型DATE的字段为d,插入‘YYYY-MM-DD’和‘YYYYMMDD’字符串格式的日期

insert into test4 values ('2014-12-11'),(20141211);

插入当前系统时间:

insert into test4 values (current_date),(now());

DATE,字节数为4,取值范围为“1000-01-01——9999-12-31”

D、DATETIME

DATETIME类型用在需要同时包含日期和时间的值。在存储时需要8字节。日期格式为‘YYYY-MM-DD HH:MM:SS’,在给DATETIME类型的字段赋值时,可以使用字符串类型或数字类型的数据插入。

(1)以‘YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式的值,其取值范围‘1001-01-01 00:00:00’~‘9999-12-3 23:59:59’

(2)以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示日期。

(3)以YYYYMMDDHHMMSS或者YYMMDDHHMMSS的数字格式表示日期和时间

范例:

insert into test5 values ('2014-12-11 15:27:12'),('20141211152712'),(20141211152712);

 

E、TIMESTAMP

TIMESTAMP显示格式与DATETIME相同,显示宽度固定在19个字符,日期格式为YYYY-MM-DD HH:MM:SS,存储时需要4字节。但是TIMESTAMP列的取值范围小于DATETIME的取值范围,为‘1970-01-01 00:00:01’UTC ~‘2038-01-19 03:14:07’UTC。UTC为世界标准时间。

范例:

创建test7,定义数据类型为TIMESTAMP的字段ts,向表中插入值‘19950101010101’,‘950505050505’,now().

insert into test6 values ('19950101010101'),(950505050505),(now());

TIME,字节数为3,取值范围为“-838:59:59——838:59:59”

4、字符串类型

MySQL中字符串类型指的:CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET

 

1、CHAR和VARCHAR类型

CHAR(M)为固定长度字符串,在定义时指定字符串列长。保存时在右侧填充空格以达到指定的长度。M表示列长,范围0~255个字符。例如:CH AR(4)定义了一个固定长度的字符串列,其包含的字符个数最大为4.当检索到CHAR值时,尾部的空空格将被删除。

VARCHAR(M)是长度可变的字符串,M表示列最大长度,M的范围0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度+1。例如VARCHAR(50)定义了一个最大长度为50的字符串。如果插入的字符串只有10个字符,则实际存储的字符串为10个字符串+一个结束字符串。VARCHAR在值保存和检索时,尾部的空格仍保留。

DATETIME,字节数为8,取值范围为“1000-01-01 00:00:00——9999-12-31 23:59:59”

2、TEXT类型

TEXT列保存非二进制字符串,如文章,评论等。当保存或查询TEXT列的值时,不删除尾部空格。TEXT类型分为4中:TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT。不同的TEXT类型的存储空间和数据长度不同。

(1)TINYTEXT最大长度为255字符的TEXT列

(2)TEXT最大长度为65535字符的TEXT列

(3)MEDIUMTEXT最大长度为16777215字符的TEXT列

(4)LONGTEXT最大长度为4294967295(4GB)字符的列

 

3、ENUM类型

ENUM是一个字符串对象,其值为表创建时在列规定中枚举的一列值。语法格式如下:

字段名 ENUM(‘值1’,‘值2’...‘值n’)

字段名指将要定义的字段,值n指枚举列表中的第n个值。ENUM类型的字段在取值时,只能在指定的枚举列表中取值,而且一次只能取一个。如果创建的成员中有空格时,其尾部的空格将自动被删除。ENUM值在内部用整数表示,每个枚举值均有一个索引:列表值所允许的成员值从1开始编号,MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

例如:定义ENUM类型的列(‘first’,’second’,’third’),该列可以取的值和每个值的索引如下:

 

索引

NULL

NULL

0

first

1

second

2

third

3

 

MySQL数据类型介绍 MySQL支持多种数据类型,主要有数值类型、日期类型、字符串类型 数值类型 MySQL主要提供的整数类型有...

TIMESTAMP,字节数为4,取值范围为“19700101080001——20380119111407”

 

当插入值超出有效取值范围时,系统会报错,并将零值插入到数据库中。

 

2、YEAR类型

 

给YEAR类型复制可以有三种方法。

 

第一种是直接插入4位字符串或者4位数字。

 

第二种是插入2位字符串,这种情况下如果插入‘00’~‘69’,则相当于插入2000~2069;如果插入‘70’~‘99’,则相当于插入1970~1999。第二种情况下插入的如果是‘0’,则与插入‘00’效果相同,都是表示2000年。

 

第三种是插入2位数字,它与第二种(插入两位字符串)不同之处仅在于:如果插入的是一位数字0,则表示的是0000,而不是2000年。所以在给YEAR类型赋值时,一定要分清0和‘0’,虽然两者相差个引号,但实际效果确实相差了2000年。

 

3、TIME类型

 

TIME类型表示为“时:分:秒”,尽管小时范围一般是0~23,但是为了表示某些特殊时间间隔,MySQL将TIME的小时范围扩发了,而且支持负值。

 

对TIME类型赋值,标准格式是‘HH:MM:SS’,但不一定非要是这种格式。

 

如果插入的是‘D HH:MM:SS’格式,则类似插入了‘(D*24+HH):MM:SS’。比如插入‘2 23:50:50’,相当于插入了‘71:50:50’。

 

如果插入的是‘HH:MM’或‘SS’格式,则效果是其他未被表示位的值赋为零值。比如插入‘30’,相当于插入了‘00:00:30’;如果插入‘11:25’,相当于插入了‘11:25:00’。

 

另外也可以插入‘D HH’和‘D HH:MM’,效果按上面的例子可以推理出来了吧。

 

在MySQl中,对于'HHMMSS'格式,系统能够自动转化为标准格式。

 

如果我们想插入当前系统的时间,则可以插入CURRENT_TIME或者NOW()。

 

TIME类型只占3个字节,如果只是存储时间数据,它最合适了。

 

4、DATE类型

 

MySQL是以YYYY-MM-DD格式来显示DATE类型的值,插入数据时,数据可以保持这种格式。另外,MySQL还支持一些不严格的语法格式,分隔符“-”可以用“@”、“.”等众多富豪来替代。

 

在插入数据时,也可以使用“YY-MM-DD”格式,YY转化成对应的年份的规则与YEAR类型类似。

 

如果我们想插入当前系统的时间,则可以插入CURRENT_DATE或者NOW()。

 

5、DATETIME类型

 

标准格式为“YYYY-MM-DD HH:MM:SS”,具体赋值方法与上面各种类型的方法相似。

 

6、TIMESTAMP类型

 

TIMESTAMP的取值范围比较小,没有DATETIME的取值范围大,因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。

 

那么TIMESTAMP类型如何插入当前时间?第一,可以使用CURRENT_TIMESTAMP;第二,输入NULL,系统自动输入当前的TIMESTAMP;第三,无任何输入,系统自动输入当前的TIMESTAMP。

 

另外有很特殊的一点:TIMESTAMP的数值是与时区相关

 

1、MySQL的五种日期和时间类型 MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份,DATE表示日期,TIME表...