我们已经准备好了,你呢?

2020我们与您携手共赢,为您的企业形象保驾护航!

当前位置:首页>>知识中心>>源码知识>>MySQL入门(二) 数据库数据类型详解

人气:51342

MySQL入门(二) 数据库数据类型详解
来源:脚本之家 更新时间:2018年07月26日 编辑:一杯凉茶 点击:51342次
把这个数据库所遇到的数据类型今天统统在这里讲清楚了,以后在看到什么数据类型,咱度应该认识,但是通过今天的学习,已经解惑了。下面就跟着我的节奏去把这个拿下吧。

序言

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

  ---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、整数类型

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

不同整数类型的取值范围

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

  例如:

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、浮点数类型和定点数类型

  

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、日期与时间类型

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

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表中了。注意:这里插入数据和查询数据操作还没学过,如果不知道,可以暂时跳过,直接看结论。

 

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

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

例子一:就拿上面那张tmp表来说。向表中插入用字符串代表的YEAR值,''0' 、'00'、 '77'、 '10'
删除表中数据:DELETE FROM tmp;
向表中插入数据:INSERT INTO tmp VALUES('0'),('00'),('77'),('10');

结果:

插入的字符'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);

结果:

插入的数字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;  

 

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

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

  DELETE FROM tmp;

  INSERT INTO tmp VALUES(CURRENT_TIME),(NOW());

  SELECT * FROM tmp;

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允许“不严格”语法,也就是任何标点符号度可以当用日期部分之间的间隔符,比如"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允许“不严格”语法,也就是任何标点符号度可以当用日期部分之间的间隔符,比如"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;

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;

  转换时区

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

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

4、字符串类型

有CHAR、CARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET

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');

结果报错:

INSERT INTO tmp VALUES('ab ','ab ');

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

  

说明,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不区分大小写。

INSERT INTO tmp VALUES('four');

报错:

例子二:

CREATE TABLE tmp(
  soc INT,
  level ENUM('excellent','good','bad')
);

INSERT INTO tmp VALUES(70,2),(90,1),(55,3);
SELECT * FROM tmp;

使用索引值,也可以选择枚举中得值,从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;

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');

报错:

结论:

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

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

5、二进制类型  

用来存放二进制数,也就是01010这种。有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUNBLOB和LONGBLOB

   

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;

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

SELECT b+0 FROM tmp;

 

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';

解释:在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是可变程度,这两个的作用就是为了区分大小写的,注意这两个是字节字符串。

上一条:服务器安全策略之《通过IP安全策略阻止某个IP访问的设置方法》
下一条:Win7下搭建python开发环境图文教程(安装Python、pip、解释器)
联系电话:13431448806
选择我们,助您企业扬帆起航!多年来,我们一直用诚心、责任心服务每一位客户。
10月优惠套餐· video

玩法形式多样助力品牌扩散 · 90后、00后已成为短视频主流用户

10月源码开发与建站流程 · Process

千里之行,始于足下 · 迈出源码开发与建站这一步,就从联系我们开始

我们已经准备好了,你呢?

从2021年10月21日起:我们与您携手共赢,为您的企业形象保驾护航!