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