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

序言

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

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