MySQL 数据类型优化(四:位数据类型)

  • A+
所属分类:MySQL

MySQL 有少数几种存储类型使用紧凑的位存储结构。所有这些位类型,不管底层存储格式和处理方式如何,从技术上来讲都是字符串类型。
BIT

在 MySQL 5.0 之前,BIT 是 TINYINT 的同义词。但是在 MySQL 5.0以及以上版本,这是一个特性完全不同的数据类型。

可以使用 BIT 列在一列中存储一个或者多个 true/false 值。 BIT(1) 定义一个包含单个位的字段,BIT(2) 存储2个位,以此类推。 BIT 列的最长长度是 64 位。

BIT 的行为因存储类型而异。 MyISAM 会打包存储所以得 BIT 列,所以17个单独的 BIT 列 只需要 17个位存储,这样MyISAM 只使用 3 个字节便能存储。其他存储引擎例如 InnoDB 和 Memory ,为每个 BIT 字节使用一个能够足够存储的最小整数类型来存放,所以不能节省存储空间。

MySQL 把 BIT 当作字符串类型,而不是字符串类型。当检索 BIT(1) 的值时,结果是一个包含二进制 0 或 1 值的字符串,而不是 ASCⅡ 码的 “0”或“1”。然而,在数字上下文的场景中检索时,结果将是位字符串转换成的数字。例如,如果存储一个值 b '00111001' (二进制等于 57 ) 到 BIT(8) 的列并且检索它,得到的是内容是字符串为 57 的字符串(57 对应的 ASCⅡ 码为 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 值,另一个方法是创建一个可以为空的 CHAR(0) 列,该列可以保存空置 (NULL) 或者长度为零的字符串 (空字符串)。

SET

如果需要保存很多的 true/false 值,可以考虑合并这些列到一个 SET 数据类型,它在 MySQL 内部是以一系列打包的位的集合来表示的。这样就有效的利用了存储空间,并且MySQL 有像 FIND_IN_SET() 和 FIELD() 的函数,方便在查询中使用。它的主要缺点是改变列的定义的代价较高:需要 ALERT TABLE ,这对大表来说是非常昂贵的操作。一般来说,也无法在 SET 列上通过索引查找。

在整数列上进行按位操作

一种替代 SET 的方式是使用一个整数包装一系列的位。例如,可以把 8 个位包装到一个 TINYINT 中,并且按位操作来使用。可以在应用中为每个定义名称变量来简化。

比起 SET ,这种方法主要的好处是避免了使用 ALERT TABLE,缺点是查询语句更难写而且难以理解。

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: