Bit-Packed Data Types

MySQL has a few storage types that use individual bits within a value to store data
compactly. All of these types are technically string types, regardless of the underlying
storage format and manipulations:
BIT
Before MySQL 5.0, BIT is just a synonym for TINYINT. But in MySQL 5.0 and
newer, it’s a completely different data type with special characteristics. We discuss
the new behavior here.
You can use a BIT column to store one or many true/false values in a single column.
BIT(1) defines a field that contains a single bit, BIT(2) stores two bits, and
so on; the maximum length of a BIT column is 64 bits.
BIT behavior varies between storage engines. MyISAM packs the columns
together for storage purposes, so 17 individual BIT columns require only 17 bits
to store (assuming none of the columns permits NULL). MyISAM rounds that to
three bytes for storage. Other storage engines, such as Memory and InnoDB,
store each column as the smallest integer type large enough to contain the bits,

so you don’t save any storage space.

 

MySQL treats BIT as a string type, not a numeric type. When you retrieve a
BIT(1) value, the result is a string but the contents are the binary value 0 or 1,
not the ASCII value “0” or “1”. However, if you retrieve the value in a numeric
context, the result is the number to which the bit string converts. Keep this in
mind if you need to compare the result to another value. For example, if you
store the value b'00111001' (which is the binary equivalent of 57) into a BIT(8)
column and retrieve it, you will get the string containing the character code 57.
This happens to be the ASCII character code for “9”. But in a numeric context,
you’ll get the value 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 |
+------+-------+
This can be very confusing, so we recommend that you use BIT with caution. For
most applications, we think it is a better idea to avoid this type.
If you want to store a true/false value in a single bit of storage space, another
option is to create a nullable CHAR(0) column. This column is capable of storing
either the absence of a value (NULL) or a zero-length value (the empty string). 

 

 一般都推荐不采用bit类型存储，太过复杂，而且推荐使用char(0)来存储是非类型