欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL入门(二) 数据库数据类型详解

程序员文章站 2023-12-04 18:47:34
序言 今天去健身了,感觉把身体练好还是不错的,闲话不多说,把这个数据库所遇到的数据类型今天统统在这里讲清楚了,以后在看到什么数据类型,咱度应该认识,对我来说,最不熟悉的应...

序言

今天去健身了,感觉把身体练好还是不错的,闲话不多说,把这个数据库所遇到的数据类型今天统统在这里讲清楚了,以后在看到什么数据类型,咱度应该认识,对我来说,最不熟悉的应该就是时间类型这块了。但是通过今天的学习,已经解惑了。下面就跟着我的节奏去把这个拿下吧。

  ---wh

一、数据类型

mysql的数据类型有大概可以分为5种,分别是 整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型。现在可以来看看你对这5种类型的熟悉程度,哪个看起来懵逼了,那就说明自己哪个不熟悉,不理解。  


注意:整数类型和浮点数类型可以统称为数值数据类型,这不难理解。

数值数据类型

  整数类型:tinyint、smallint、mediumint、int、bigint

  浮点数类型:float、double

  定点小数:decimal

日期/时间类型

  year、time、date、datetime、timestamp

字符串类型

  char、varchar、text、enum、set等   

二进制类型

  bit、binary、varbinary、blob

1、整数类型

MySQL入门(二) 数据库数据类型详解

不管你学什么语言,在基础方面,都应该知道 1个字节= 8位二进制数。 每个类型的取值范围也就能够知道,比如 tinyint占用1个字节,也就是8位,2的8次方减1等于255,也就是说如果代表没符号的整数,该取值范围为0~255,如果是有符号的,最高位为符号号位,也就是2的7次方减1,也就是127,取值范围为-128~127, 为什么需要减1,这个问题就需要考虑临界值的问题了。而考虑临界值问题又有需要讨论原码补码反码的知识,这些度不是我们讨论的重点,所以在这就自行百度。给出一张范围表,给大家做参考。

不同整数类型的取值范围

MySQL入门(二) 数据库数据类型详解

根据自己所需去选取不同的类型名称,

  例如:

create table aaa(
 id int(10) primary key,
 age int(6)
  );

  这个例子中int(10)、int(6) 括号中的数字表示的是该数据类型指定的显示宽度,指定能够显示的数值中数字的个数。这里要注意:显示宽度和数据类型的取值范围是无关的,显示宽度只是指明mysql最大可能显示的数字个数,注意是可能。通俗点讲就是,比如这个age字段,显示宽度为6,但是如果你插入的数据大于6,达到了8,6666 6666,那也没关系,只要插入的数值的位数不超过该类型整数的取值范围,就行,如果插入的数值长度是4,或者3,数值的位数小于指定的宽度,后面的位数就会由空格填空,5555插入age字段,存的就是"5555 "后面用空格补齐。还有一点,这个显示宽度没限制,你写100度没问题,但是插入数据时,实际起控制作用的还是数据类型的取值范围。如果不写显示宽度,就会用系统默认的,比如,int的默认显示宽度是11,看上面表,最高也就能表示10位大小的数值,但是要注意,有符号的,也就是负数时,符号位也占一位。

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

 MySQL入门(二) 数据库数据类型详解 

2.1解释m,d的意思:

m:数值的总位数。 通俗点讲,就是看有多少个数字,比如,5.6789,m就是5
d:小数点后面能保留几位。 比如上面的5.6789 ,d就是4。 这只是举一个例子,来说明m,d是什么,实际是先有m,d的,然后在来控制数值,而不是更具数值来确定m,d。

不单单就mecimal有m,d这两个参数,float 和 double 度有,看下面例子

比如:

   create table tmp(
  x float(3,1),
  y double(5,3),
  z decimal(5,4)
   );

假设x插入的值为:5.69,56.78,5.438,349.2 (注意:实验给x这个字段插入的值,可能实验了三次,不要错看成x的值为5.69,y为56.78等等了)
实际上在数据库中存的值为:5.7,56.9,5.3,349.2这个报错
分析:x的m为3,d为1,那么小数点上必须是占了一位数字,就算没有值,也会用0来填充,所以说,整数位上最多就只能是2位,这里要切记要先根据d的值,来算整数位能最多有多少位。

通过分析x,y和z也就简单了,

y字段上的值,整数部分最多是2位,小数点后的位数最多是3位,也就是说小数点后超过了3位,就会四舍五入。
z字段上的值,整数部分最多只能是一位,小数点后的位数最多是4位,如果不足4位,也会用0补充。比如插入1.56,在数据库中存的就是1.5600, 比如插入25.46,这个就会报错,因为整数部分只能是一位,小数点后的位数已经占了4位了。这里要搞清楚。

2.2、float、double、decimal三者的区别。

都是用来表示我们所说的小数的也就是浮点数,但是三种的精度不一样,也就是后面显示的位数不一样,

区别一:

float显示后面的小数点位大概在40多位,

double能显示的就是300多位了,不是一个层次上的,

decimal这个小数点后面能显示的位数跟double差不多,

区别二:

   float和double在不指定精度时,也就是不用(m,d),默认会按照实际的精度,也就是你写多少就是多少,而decimal如不指定精度默认为(10,0),也就是如果不指定精度,插入数值56.89,在数据库中存储的就是57。所以一般使用decimal时就会指定精度,而使用float和double就不用。

区别三:

   浮点数相对与定点数(decimal)的优点就是在长度一定的情况下,浮点数能够表示更大的数据范围,但是缺点是会引起精度问题。

   2.3、什么时候使用float、double、decimal

   对精度要求比较高的时候,比如货币、科学数据等,使用decimal的类型比较好。其他的时候,看你要存放的数据的大小而定了,一般使用double。并且在使用浮点数时需要注意,尽量避免做浮点数的比较,比如加、减,谁大谁小,这样的操作,会引起精度缺失。相信在一些程序语言中,遇到过float精度丢失的问题。  

3、日期与时间类型

MySQL入门(二) 数据库数据类型详解

现在有些东西看不懂没关系,大概有个了解先,接下来一一进行讲解。

3.1、year 

   3.1.1、重点看他的存储范围,1901~2155. 在插入该数值时,有两种方式,一种是用字符串来代表插入的year值,另一种是用数字代表year值,其中字符串插入的可以用单引号和双引号,没区别,跟一些程序设计语言不一样,单个字符就必须用单引号,多字符就要用双引号,在mysql中,单双引号度表示字符。

例子:

  create table tmp(
   y year
   );  

向表中插入数据:insert into tmp values(2010),('2010'),("2010");
查询表中数据:select * from tmp;
能查看三条记录度插入到数据库tmp表中了。注意:这里插入数据和查询数据操作还没学过,如果不知道,可以暂时跳过,直接看结论。

MySQL入门(二) 数据库数据类型详解 

如果向表中插入超出范围的值,2166则会报错

MySQL入门(二) 数据库数据类型详解

3.1.2、在插入完整年份时,用字符串和用数字代表year值的效果是一样的,但是当省略year值时,用这两种方式就不一样了。

例子一:就拿上面那张tmp表来说。向表中插入用字符串代表的year值,''0' 、'00'、 '77'、 '10'
删除表中数据:delete from tmp;
向表中插入数据:insert into tmp values('0'),('00'),('77'),('10');

MySQL入门(二) 数据库数据类型详解

结果:

插入的字符'0'、'00'变成了2000
插入的字符'77'变成了1977
插入的字符'10'变成了2010

例子二:

向tmp表中y字段插入2位数字表示year值,0,00,78,11
删除表中数据:delete from tmp;
向表中插入数据:insert into tmp values(0),(00),(77),(11);

MySQL入门(二) 数据库数据类型详解

结果:

插入的数字0、00变成了0000
插入的数字77变成了1977
插入的数字11变成了2011

结论:在省略写年份时,

1、用字符表示和用数字表示的区别就在于0。如果是字符0或字符00,则在数据库中会生成2000,如果是数字0或00,则会生成0000.
2、在不超过70,也就是小于70,度会生成2000年以上,也就是如果是69,则生成2069.如果是70以上包含70,就会变成1970以上。比如70,就会变为1970。也就是00-69范围的年值转换为2000~2069. 70-99范围的年值转换为1970~1999
3、一般我们如果要使用,也就用全称,这样不容易混淆,但是得知道有这些特性

3.2、time    

格式:hh:mm:ss hh表示小时 、mm表示分钟、ss表示秒

取值范围:-838:59:59 ~ 838:59:59

解释:这里的时间不仅仅可以用来表示一天的时间(也就是24小时),还可能是某件事情过去的时间或两个事件之间的时间间隔,通俗点讲,我们平常每天的时间就是从凌晨0点就重新开始计时,计满24个小时,然后又重新开始,也就相当于当前我们看到的时间是凌晨0点过去的时间,也就是距凌晨0点过去了多少个小时。 早上7点,也就是距离凌晨0点这件事7个小时,以此类推,直到距离了24个小时,然后重新开始计算。 在mysql中,这个time就不局限于每天距离凌晨0点多长时间了,可以是过去某个时间距离现在多长时间了,比如昨天早上7点,距离现在上午9.00多长时间了,就超过了24小时,所以。这个time的取值范围就比我们所理解的24小时更大。

3.2.1、表示time有很多种格式,上面的hh:mm:ss只是标准的一种,

  1、d hh:mm:ss :d表示日、天数。在数据库中存储时,d会被转换为小时保存,d*24+hh

  2、hh:mm 、d:hh、 ss :这些格式度是可以的,注意最后一个,如果是单独就是2个数字,那么就代表的是秒,比如"20"那就代表的是00:00:20

  3、hhmmss: 这是没有间隔符的字符串或者数值,比如101112会被理解为10:11:12,但是109712就是不合法的,因为分钟位上超过了59。存储时会报错。这个没有冒号时需要注意一点,数值的最右边两位表示秒,以此类推,比如 5523 表示的是00:55:23而不是55:23:00。 所以说上面ss格式时代表的是秒就是这样来的,从最右边看起。如果有冒号,则从左边小时开始看起,比如 55:23 就是代表的55:23:00

例如:

  create table tmp(

t time

  );

  插入值"10:05:05"、"23:23"、"2 10:10"、"3 02"、"10"、"101112"、"109712"
  insert into tmp values("10:05:05"),("23:23"),("2 10:10"),("3 02"),("10"),("101112");
  select * from tmp;  

MySQL入门(二) 数据库数据类型详解 

可以看出:都是如我们预期的那样显示数据。  

3.2.2、使用系统的函数,插入当前的时间。

  delete from tmp;

  insert into tmp values(current_time),(now());

  select * from tmp;

MySQL入门(二) 数据库数据类型详解

3.3、date

格式:yyyy-mm-dd  yyyy表示年份  mm表示月份  dd表示日

取值范围:1000-01-01~9999-12-3

  使用字符或者数值的数据度可以插入

注意:这个除了标准格式之外,跟time一样year一样,在年份这里也有其省略格式,其规则和year中一样。00-69范围的年值转换为2000~2069. 70-99范围的年值转换为1970~1999。 例如:12-12-31 表示2012-12-31 981231表示1998-12-31

也可以使用current_date或者now()插入当前的系统日期。

例子:(注意,每次创建表之前,会把之前的老表给删除掉,这里省略掉了,使用drop table 表名; )

   create table tmp(
 d date
   );

   insert into tmp values("1998-08-08"),(19980808),(100511),(current_date),(now());
   select * from tmp;

MySQL入门(二) 数据库数据类型详解

注意:mysql允许“不严格”语法,也就是任何标点符号度可以当用日期部分之间的间隔符,比如"98.11.23"、"98/11/31"、"98@11@31"都可以,自己可以去尝试尝试,但是一般使用标准格式比较好,让人看起来舒服。

3.4、datetime  

格式:yyyy-mm-dd hh:mm:ss

取值范围:1000-01-01 00:00:00~9999-12-3 23:59:59

  这个不用做多解释,因为这个就是date和time的结合体。其各种特点在这里也能够适合。但是注意hh:mm:ss 只能表示一天的时间,也就最多到23:59:59.

例子:

   create table tmp(

 dt datetime

   );

   insert into tmp values("1998-08-08 08-08-08"),(980808080808),(current_date()),(now());

   select * from tmp;

MySQL入门(二) 数据库数据类型详解

注意:mysql允许“不严格”语法,也就是任何标点符号度可以当用日期部分之间的间隔符,比如"98.11.23"、"98/11/31"、"98@11@31"都可以,自己可以去尝试尝试,但是一般使用标准格式比较好,让人看起来舒服。

3.4、datetime  

格式:yyyy-mm-dd hh:mm:ss

取值范围:1000-01-01 00:00:00~9999-12-3 23:59:59

  这个不用做多解释,因为这个就是date和time的结合体。其各种特点在这里也能够适合。但是注意hh:mm:ss 只能表示一天的时间,也就最多到23:59:59.

例子:

   create table tmp(

 dt datetime

   );

   insert into tmp values("1998-08-08 08-08-08"),(980808080808),(current_date()),(now());

   select * from tmp;

MySQL入门(二) 数据库数据类型详解

current_date()返回的是当前系统的日期 格式 yyyy-mm-dd 所以在前面打印year和date度可以用到该函数,因为包含了其数据类型所要的信息

 noe()返回当前系统的日期和时间值,格式为yyyy-mm-dd hh:mm:ss,所以在这里能够使用其输出datetime类型的值。

   3.5、timestamp

 格式:yyyy-mm-dd hh:mm:ss

 取值范围:1970-01-01 00:00:01 utc ~ 2038-01-19 03:14:17 utc

 解释:显示宽度固定在 19个字符。也就是这个输出标准格式,utc表示世界标准时间,这个跟datetime基本上一样,但是有一个最大的区别,我们需要知道。

 区别:存储字节和支持的范围不一样,最重要的区别在datetime在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,也就输出什么,与时区无关,而timestamp值的存储是以utc格式保存,存储时会对当前时区进行转换,检索时再转换回当前时区,也就是查询时,会根据当前时区的不同,显示的时间值不同。 时区的意思就是,你在美国和你在中国两边的时间显示不一样,你在美国有在美国的时间,比中国快多长时间,比如在中国才早上8点,在美国可能就是晚上8.9点了(这个只是打个比方,准备转换时间我没去看。)

 例如:

  create table tmp(
    ts timestamp
  );

  insert into tmp values(now());
  select * from tmp;

MySQL入门(二) 数据库数据类型详解

  转换时区

MySQL入门(二) 数据库数据类型详解

可以看到,如我们所想,输出时间变了,增加了两个小时,这个是关于什么东10区,东8区等,我也不懂这些时差,总之能够得出结论就行了。

也就是说,timestamp和datetime其实差不多,就是一个时区的差别。timestamp也叫时间戳。以后遇到它我们就应该知道是什么东西了

4、字符串类型

有char、carchar、tinytext、text、mediumtext、longtext、enum、set

MySQL入门(二) 数据库数据类型详解

4.1、char和varchar

char(m):为固定长度字符串,固定长度的意思就是m的值为多少,那么该m的值就是其实际存储空间的值,就算插入的数据少于m位,其存储空间还是那么大,多余的用空格补齐。在输出时,空格将被删除不输出。m最大为255,比如char(4),如果插入abc,则存储的值为'abc '后面多了一个空格,输出还是‘abc', 存储空间还是占4个字节。m最大为255
varchar(m):长度可变的字符串,跟char相反,会根据实际的大小值来确定存储空间的大小,比如 varchar(4),插入'ab',则存储空间为3字节,看上面图就知道varchar会多一个字节用来存储长度,m最大为65535.
注意:字符串跟数值类型不一样,m为多大,就最多能插入多少字符,超过了m,就会报错

例子:

create table tmp(
ch char(4),
vch varchar(4);
); 
insert into tmp values('asdf','asdfg');

结果报错:

MySQL入门(二) 数据库数据类型详解

insert into tmp values('ab ','ab ');

select concat( '(', ch ,')' ),concat( '(',vch,')' ) from tmp;//这句话的意思就用(将结果包起来,用来观察输出的字符长度)

MySQL入门(二) 数据库数据类型详解  

说明,char不能够存储空格字符,而varchar可以。        

4.2、text

    text分四种:tinytext、text、mediumtext和longtext
      tinytext:255字符
      text:65535字符
      mediuntext:16777215字符
      longtext:4294967295,大概4gb的字符

    也就是说,比如我们要存一本小说,那么就需要使用上面四种中的一种来存储,选取mediuntext或者longtext差不多。

4.3、enum

    枚举,格式:字段名 enum('值1','值2','值3'...,'值n'); n最多为65535
    例如:

      create table tmp(
        enm enum('first','second','third');
      );      

      解释:enm字段的数据类型为enum,枚举类型,那么在插入该字段中的值只能为枚举中的这几个值,不能插入别得值,否则报错

      insert into tmp values('first'),('first');
      select * from tmp;

MySQL入门(二) 数据库数据类型详解

mysql不区分大小写。

insert into tmp values('four');

报错:

MySQL入门(二) 数据库数据类型详解

例子二:

create table tmp(
  soc int,
  level enum('excellent','good','bad')
);

insert into tmp values(70,2),(90,1),(55,3);
select * from tmp;

MySQL入门(二) 数据库数据类型详解

使用索引值,也可以选择枚举中得值,从1开始,不是0,注意这点

总结:使用enum类型就是为了限制字段上的值的取值范围,只能取我们所规定的值。

4.4、set

格式:字段名 set('值1','值2','值3','值4'...,'值n') n最大为64
通过例子来讲解这个set的特点

create table tmp(
  s set('a','b','c','d');
);

insert into tmp values('a'),('a,b,a'),('c,a,d');
select * from tmp;

MySQL入门(二) 数据库数据类型详解

a 变成 a
a,b,a 变成 a,b
c,a,d 变成 a,c,d

结论:

1、插入set字段中的值如果有重复,则会自动删除重复的值
2、插入set字段中的值会按顺序排列,排列规则就是按照set中的值的排列优先顺序

insert into tmp values('a,x,b');

报错:

MySQL入门(二) 数据库数据类型详解

结论:

3、如果插入了不属于set中的值,就会报错

set的特性就上面所说的三点   

5、二进制类型  

用来存放二进制数,也就是01010这种。有bit、binary、varbinary、tinyblob、blob、mediunblob和longblob

MySQL入门(二) 数据库数据类型详解   

5.1、bit类型 

位字段类型,m表示插入值的位数,最大为64位,默认值为1,如果插入值小于m位,值的左边用0填充,

 例子:

create table tmp(
b bit(4)
);

解释:4位的二进制,也就是能够保存0到15之间的值。

insert into tmp values(2),(10),(15);       
select bin(b+0) from tmp;

MySQL入门(二) 数据库数据类型详解

解释:我们将2,10,15这三个10进制存入数据表中,其字段为bit类型,所以在表中存放的是二进制数,但是将其显示出来,要先将二进制数转换为对应的数字的值,也就是通过b+0, 然后在通过bin()函数将数字转换为二进制。我们可以尝试不用bin()函数,只用b+0,看输出什么

select b+0 from tmp;

MySQL入门(二) 数据库数据类型详解 

5.2、binary和varbinary类型

格式:字段 binary(m)或者varbinary(m)

这两个跟char和varchar类似,

  binary:长度是固定的,指定长度后,不足最大程度的,将在它们右边填充"\0"来达到指定长度,

  varbinary:长度是可变的,制定好长度后,其长度可以在0到最大值之间,例如,指定字段数据类型为varbinary(20),如果插入的值的长度只有10,则实际存储空间为10加1,即实际占用的空间为字符串的实际长度加1.

说了这么多,但是还是不明白这两个有什么用处,现在来告诉你,我们说mysql中对大小写不敏感,但是这两个数据类型却对大小写敏感,原因是他们是用二进制来保存数据的,比如a和a,两个的二进制就不一样。所以在很多时候我们需要区分大小写的时候,就会用到该类型。

注意:这两个类型的长度计算的是字节长度,一个字符等于2个字节,比如binary(4)这个表示能够存放4个字节的长度,也就是只能存放2个汉字。可以存4个字母。不要把这个长度当成二进制位的长度了,说是二进制字符串的意思是,用二进制来进行存储,但是其长度约束是字节长度。

例子:

  create table tmp(
    b binary(10)
  );

  insert into tmp values('a');
  select * from tmp where b='a';

MySQL入门(二) 数据库数据类型详解

解释:在tmp表中存放了一个小写a,然后通过大写a查找表中,看是否能找到a,结果找不到,就验证了我们上面的说法,具有区分大小写的功能。

总结:binary和varbinary的主要作用就是用来区分大小写的,其他没什么作用,但是使用时要注意限制其大小的是字节数,而不是二进制位,它存储的格式是用二进制来存储的。这两个不要搞混淆了。

 5.3、blob类型

是一个二进制大对象,tinyblob(32kb)、blob(64kb)、mediumblob(16m)和longblob(4gb)。一般存储的是一些图像,音频文件。

二、如何选择数据类型      

1、整数和浮点数

  如果不需要小数部分,则使用整数来保存数据,并且根据整数的大小,来选择合适的整数类型,如果需要小数部分,则使用浮点数类型,浮点数类型中,有float和double,如果需要精度高一点,则选择double。根据自己的需求来决定选什么。

2、浮点数和定点数

  浮点数float、double相对应定点数decimal的优势在于:在长度一定的情况下,浮点数能表示更大的数据范围,但是浮点数容易产生误差,因此在精度比较高时,建议使用decimal,比如货币这一类东西,就用decimal比较合理,注意浮点数在进行加减运算时也容易出现问题。如果进行数值比较,也建议用decimal

3、日期与时间类型

  可以看上面详解时的图,根据各种格式,选择自己所需要的数据类型,注意timestamp和datetime的区别,一个是跟时区有关,一个无关,其他没什么大的区别。

4、char与varchar之间的特点与选择

  区别:

    char是固定长度字符、varchar是可变长度字符。char会自动删除插入数据的尾部空格,varchar不会。
    char是固定长度,处理速度比varchar更快,缺点很明显,浪费存储空间,所以对存储不大,但在速度上有要求的可以使用char类型,反之用varchar。

5、enum和set

   enum只能取单值,也就是从枚举类型中选取其中一个值,但是set可以取多值,
   enum最多能存放65535个成员,set只能65个
   空字符串也能在set中存储,

   要存储一个人的喜爱时,最好使用set类型,其实最重要的是看具体的情况在选取最为合适的把

6、blob和text

   blob是二进制字符串,text是非二进制字符串,两者均可存放大容量的信息,blob主要存储图片、音频信息,而text只能存储纯文本文件。分清楚两者的用途

7、binary和varbinary

   这两个的区别和char与varchar的区别差不多,binary是固定长度、varbinary是可变程度,这两个的作用就是为了区分大小写的,注意这两个是字节字符串。