Schema的优化和索引 - 选择最佳的数据类型 - BIT数据类型

MySQL有很多使用单独的bit去存储数据的类型。不管底层的存储格式以及操作，从技术上来看所有的这些类型都是字符串类型。

 

BIT

在5.0之前的版本，BIT仅仅等同于TINYINT。但是在5.0之后的版本，它已经是个具有一些特性的，和以前完全不同的数据类型了。我们在这讨论的是新增加的特性。

 

你可以使用BIT列存储一个或多个true/false值。BIT(1)定义了一个包含1个bit的字段，BIT(2)存储了2个bits.如此类推。。

BIT最大长度是64bits.

 

不同存储引擎,BIT的特性是不同的。MyISAM为了存储它们把这些列打包为一个整体。因此17个单独的BIT列仅仅要存储17bits(假设每一列都没有NULL值)。MyISAM大约需要3字节存储它们。其他的存储引擎，如Memory和InnoDB。以足够存储这些bits的最小整数类型来存储每一列。因此你就不能节约使用的存储空间了。

 

MySQL把BIT看做字符串类型。并不是数字类型。当你获取一个BIT(1)的值，这个结果是个字符串，但是这字符串是二进制的0或1，要记住并不是ASCII的0或1.然而如果你获取的是数字，这个结果会被转换。如果你要作比较就一定要记住这一点。举个例子，如果你把b'00111001'（这个等同于57）存储到BIT(8)再获取它。你会获得字符串包含了字符码为57.ASCII字符码为9，但是在数字的环境下，你的获得值是57.

 

mysql> CREATE TABLE bittest(a bit(8));

mysql> INSERT INTO bittest VALUES(b'00111001');

mysql> SELECT a, a + 0 FROM bittest;

+------+-------+

| a | a + 0 |

+------+-------+

| 9 | 57 |

+------+-------+

 

这很令人迷惑，所以我们建议要小心的使用BIT类型。对于大部分应用，要尽可能避免使用这个类型。

 

如果你想用一个BIT存储true/false。另一个选择是使用一个可以为NULL的CHAR(0)列。这个列可以存储NULL也可以存储一个空字符串。

 

SET

如果你要存储非常多的true/false。考虑使用SET数据类型把许多列整合为一个。它把一系列的bit打包了。并且十分有效的进行存储。还有就是MySQL有一些如FIND_IN_SET()和FIELD()函数可以很容易使用SET类型。主要的缺点就是需要改变表的定义。需要使用ALTER TABLE，这对比较大的表来说消耗还是很大的。一般来讲，你也不能使用索引来查找SET列。

 

在整数列上进行位运算

对于SET的另一个原则是使用整型。一个例子，你可以把8bits打包一个TINYINT然后用位运算来操作它们。在应用代码中对每一位定义常量可以使这些操作变得更为简单。

 

这种方式最主要的优势是，你可以改变字段中的枚举值，而不必使用ALTER TABLE。缺点就是写出的语句很难理解。一些人很习惯位运算而另一些人确不是。所以是否使用这个技术完全是个人的喜好了。

 

下面的一个例子是关于权限控制的。每个bit或set元素表现的值都是CAN_READ, CAN_WRITE或者CAN_DELETE。如果使用的SET类型，那么MySQL存储的就是bit和值的映射。如果存储的是整形，你就要把映射存储到应用程序代码中了。

下面是SET的例子

mysql> CREATE TABLE acl (
-> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
-> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql> SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ', perms);
+---------------------+
| perms |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+

 

如果使用的整形，你可能写出如下的代码

 

mysql> SET @CAN_READ := 1 << 0,
-> @CAN_WRITE := 1 << 1,
-> @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
-> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
| 5 |
+-------+

 

我们使用了变量，但是你可以用应用程序中的代码来替换它们。