mysql中关于bit,enum,tinyint三种数据类型的差别

如果有说的不对的地方，请拍砖，以下总结的这些都是边测试边查资料和自己的分析，可能会存在一些错误的地方。

 

之前我们纠结过该使用哪些类型。

 

对此我做了很多测试。发现这三种类型，存储所占空间是一样的。对于查询效率上来讲由于查询的时间会受到其他因素的影响，所以我只看了一下大概值，这三种类型的数据在搜索速度上基本可以说也是一样的。

 

 

使用bit类型的表:

CREATE TABLE `test_bit` (

  `my_bit` bit(1) NOT NULL DEFAULT b'0',

  KEY `my_bit` (`my_bit`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

使用enum类型的表:

CREATE TABLE `test_enum` (

  `my_enum` enum('0','1') NOT NULL DEFAULT '0',

  KEY `my_enum` (`my_enum`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

使用tinyint类型的表:

CREATE TABLE `test_tinyint` (

  `my_tinyint` tinyint(1) NOT NULL DEFAULT '0',

  KEY `my_tinyint` (`my_tinyint`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

 

我用脚本分别向这三个表中插入1000万的数据。每个表第一个字段是0或1。第二个字段是个大写英文字母，用来作为查询时匹配的条件。

插入完毕之后，看了一下表的状态：

 

 

 

查询的时间由于会受到别的因素的影响，每次都不一样，我就不贴了，总之非常接近。

 

其实一开始我被bit(1)这个1给忽悠了。我以为他存储时真的只占1位，而实际上你指定了这个bit(1),所能插进去的二进制的确只有0和1(插入时要b'0'或b'1',直接插0或1插进去的是十进制的)。你多插入的值会被截断，比如插入一个b'10'，插进去后就变成了1。但他最小所占空间就是1字节，是8位。mysql手册上写明了

BIT(M)

大约(M+7)/8个字节

我指定bit(1),根据他的公式，还是要占1个字节。

 

 

Bit(M)这个M，其实起的作用只是做限了个限制，并不会实际减少数据对1字节的占用。1位的范围是二进制的0-1，2位是00-11，3位就是000-111….他里面直接存储的是二进制值。

 

 

 

enum的存储原理我仔细查看了下手册。是这样的:

在建立这个字段时，我们会给他规定一个范围比如enum('a','b','c'),这时mysql内部会建立一张hash结构的map表，类似:0000 -> a，0001 -> b，0002 -> c。

当我插入一条数据，此字段的值位a或b或c时，他存储在里面的不是这个字符，而是对应他的索引，也就是那个0000或0001或0002。

同样，enum在mysql手册上的说明:

ENUM('value1','value2',...)

1或2个字节，取决于枚举值的个数(最多65,535个值)

除非enum的个数超过了一定数量，否则他所占的存储空间也总是1字节。

 

 

 

还有那个set类型。他的存储原理和enum差不多。只不过他可以存储字符串集合，set('a','b','c')。在数据库内部也是先建立map表，数据存储的实际上是索引，他最多只支持64个元素，因为他最大是8个字节，因为在a,b,c这个集合中允许的值为a,b,c的所有排列组和。64个元素的话，每一条数据的值需要用最大需要用8*8位的索引去指向才能表达这个值。

 

 

 

还有tinyint:

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127

他的最小存储所占空间也是1字节。

 

 

 

 

最后做一个清晰点的测试，我去掉了以上3个表的my_text字段，只保留bit，enum，tinyint这三个类型的字段，然后各插入一个值:

insert into test_bit(`my_bit`) values(b'1');

insert into test_enum(`my_enum`) values('1');

insert into test_tinyint(`my_tinyint`) values(1);

 

 

 

 

 

所以基于以上来看，用这三种类型哪种来当作枚举字段或布尔字段，效果基本一样。也可能我只是单机测试，不是很能说明问题，因为使用bit，select出来时需要使用bin函数或那个字段+0来做一步转换成十进制我们这边才可以使用。enum需要在mysql内部根据map的索引找一下他真实的值。而tinyint只是直接从他内部取出来。理论上来讲，tinyint应该比其他两种效率强在他不用做那次转换。

 

 

虽然所占空间和查询效率上来讲基本一致，但他们毕竟还是有不同的地方：

Bit类型，比较适合直接存储二进制。

 

Enum，既然要用它，就不必要使用什么0，1，2来代替实际的字符串了。甚至中文字符串。他并不会对数据库性能进行多余开销。因为对于它来说，你使用'0','1','2'和'张三','李四','王五'数据表所占的存储空间一样。但是考虑到我们实际应用时数据需要从db服务器回传到web app，所以在网络传输时，当然还是尽可能的传输小数据比较好。所以如果很在意这些，还是不用它好了。

Tinyint，比较常用的是它，他也确实更加合适，使用它和enum的方式其实也类似，只不过我们把enum建立的那张map表建立在我们的web app端了。