MySQL的10种常用数据类型

MySQL的数据类型

常用的数据类型有：

• 整型（xxxint）
• 位类型(bit)
• 浮点型（float和double、real）
• 定点数（decimal,numeric）
• 日期时间类型（date,time,datetime,year）
• 字符串（char,varchar,xxxtext）
• 二进制数据（xxxBlob、xxbinary）
• 枚举（enum）
• 集合（set）

1、整数（xxxint）

整数列的可选属性有三个：

• M: 宽度(在0填充的时候才有意义，否则不需要指定)
• unsigned: 无符号类型(非负)
• zerofill: 0填充,(如果某列是zerofill，那么默认就是无符号)，如果指定了zerofill只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可

原来，在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。

2、浮点型

对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节

MySQL允许使用非标准语法（其他数据库未必支持，因此如果设计到数据迁移，则最好不要这么用）：FLOAT(M,D)或DOUBLE(M,D)。这里，(M,D)表示该值一共显示M位，其中D表示小数点后几位，M和D又称为精度和标度。例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。M取值范围为0255。D取值范围为030，同时必须<=M。

如果存储时，整数部分超出了范围（如上面的例子中，添加数值为1000.01），MySql就会报错，不允许存这样的值。如果存储时，小数点部分若超出范围，就分以下情况：若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存，例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如999.995和-999.995都会报错。

说明：小数类型，也可以加unsigned，但是不会改变数据范围，例如：float(3,2) unsigned仍然只能表示0-9.99的范围。

float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示

REAL就是DOUBLE ，如果SQL服务器模式包括REAL_AS_FLOAT选项，REAL是FLOAT的同义词而不是DOUBLE的同义词。

注意：在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。如果希望保证值比较准确，推荐使用定点数据类型。

3、位类型（了解）

BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。

BIT其实就是存入二进制的值，类似010110。如果存入一个BIT类型的值，位数少于M值，则左补0。如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式：如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。

对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()或hex()函数进行读取。

4、定点型

DECIMAL在MySQL内部以字符串形式存放，比浮点数更精确。定点类型占M+2个字节

DECIMAL(M,D)与浮点型一样处理规则。M的取值范围为065，D的取值范围为030，而且必须<=M，超出范围会报错。

DECIMAL如果指定精度时，默认的整数位是10，默认的小数位为0。

NUMERIC等价于DECIMAL。

5、日期时间类型

对于year类型，输入的是两位，“00-68”表示2000-2069年，“70-99”表示1970-1999年。记得比较麻烦，建议使用4位标准格式。

'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'，'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间隔符。例如，'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 113045'和'98@12@31 11^30^45'是等价的。

'YYYYMMDD'或'YYMMDD'格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，'19970523'和'970523'被解释为 '1997-05-23'，但'971332'是不合法的(它有一个没有意义的月和日部分)，将变为'0000-00-00'。

对于包括日期部分间隔符的字符串值，如果日和月的值小于10，不需要指定两位数。'1979-6-9'与'1979-06-09'是相同的。同样，对于包括时间部分间隔符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。'1979-10-30 1:2:3'与'1979-10-30 01:02:03'相同。

数字值应为6、8、12或者14位长。如果一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。如果数字 是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。

一般存注册时间、商品发布时间等，不建议使用datetime存储，而是使用时间戳，因为datetime虽然直观，但不便于计算。而且timestamp还有一个重要特点，就是和时区有关。

6、字符串型

char,varchar,text区别

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

char如果不指定(M)则表示长度默认是1个字符。varchar必须指定(M)。

char(M)类型的数据列里，每个值都占用M个字符，如果某个长度小于M，MySQL就会在它的右边用空格字符补足（在检索操作中那些填补出来的空格字符将被去掉；如果存入时右边本身就带空格，检索时也会被去掉）；在varchar(M)类型的数据列里，每个值只占用刚好够用的字符再加上一个到两个用来记录其长度的字节（即总长度为L字符+1/2字字节）。[I1]

由于某种原因char 固定长度，所以在处理速度上要比varchar快速很多，但相对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。

text文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用char，varchar来代替。还有text类型不用加默认值，加了也没用。

哪些情况使用char更好

一，存储很短的信息，比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。

二，固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

三，十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

MyISAM和MEMORY存储引擎中无论使用char还是varchar其实都是作为char类型处理的。

除此之外，建议使用varchar类型。特别是InnoDB存储引擎。

7、二进制值类型（了解）

包括：xxxBLOB和xxxBINARY

BINARY和VARBINARY类型类似于CHAR和VARCHAR类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。当保存BINARY(M)值时，在它们右边填充0x00(零字节)值以达到指定长度。取值时不删除尾部的字节。比较时所有字节很重要（因为空格和0x00是不同的，0x00<空格），包括ORDER BY和DISTINCT操作。比如插入'a '会变成'a \0'。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。分别与四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT对应有相同的最大长度和存储需求。在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。BLOB和TEXT列不能有默认值。BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

8、枚举（ENUM）

MySql中的ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值：

可以插入空字符串""和NULL（如果运行NULL的话）。

如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，如果是严格模式，将不能插入，如果是非严格模式，将选用第一个元素代替，并警告。

ENUM最多可以有65,535个成员，需要2个字节存储。

当创建表时，ENUM成员值的尾部空格将自动被删除。

值的索引规则如下：

来自列规定的允许的值列中的值从1开始编号。

空字符串错误值的索引值是0。

NULL值的索引是NULL。

9、集合（SET）

SET和ENUM类型非常类似，也是一个字符串对象，里面包含0~64个成员。

SET和ENUM存储上有所不同，SET是根据成员的个数决定存储的字节数。

SET和ENUM最主要的区别在于SET类型一次可以选择多个成员，而ENUM则只能选择一个。

10、特殊的NULL类型

Null类型特征：

（1）所有的类型的值都可以是null，包括int、float等数据类型

（2）空字符串””，不等于null，0也不等于null，false也不等于null

（3）任何运算符,判断符碰到NULL,都得NULL

（4）NULL的判断只能用is null,is not null

（5）NULL 影响查询速度,一般避免使值为NULL

面试：

为什么建表时,加not null default '' / default 0

答:不想让表中出现null值.

为什么不想要的null的值

答:（1）不好比较,null是一种类型,比较时,只能用专门的is null 和 is not null来比较.

碰到运算符,一律返回null

（2）效率不高,影响提高索引效果.

因此,我们往往,在建表时 not null default ''/0

（4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；5.0版本以上，varchar(20)，指的是20字符）