The guy BIT
was made with bitfields in mind, not "loose" bits. Not that you can’t save a single bit, but there’s no space saving in this case.
A column BIT(1)
will occupy one byte per row as well as one column BIT(8)
. A column BIT(64)
will occupy 8 bytes. A column BIT(59)
will occupy the same 8 bytes, because anyway, would not fit 59 bits in less than 8 bytes.
The manual defines this formula for space estimation:
BIT(M) approximately (M+7)/8 bytes
According to the documentation, you can have a BIT field up to 64 bits long.
As for input and feedback, there is a padding which corresponds to the value set in the column. If you enter 0b011
in a BIT(6) column, it is actually inserting 0b000011
. When reading the data, you will receive a value of 6 bits back.
Using a BIT column
To use BIT columns literally, use one of the two syntaxes below:
CREATE TABLE t (b BIT(8));
INSERT INTO t SET b = 0b1010;
INSERT INTO t SET b = b'1010';
The returned values are binary strings, if you need them in numerical format, you can force a cast:
mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
+------+----------+----------+----------+
| b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
| 255 | 11111111 | 377 | FF |
| 10 | 1010 | 12 | A |
| 5 | 101 | 5 | 5 |
+------+----------+----------+----------+
The manual has more details about literals.
Considerations
There’s a articleen that mentions many bugs in the implementation of BIT type, I do not know how much is updated.
Additionally, there is a discussion in a bug report Mysql comments that the BIT column is only implemented in Myisam. Since it is a 2005 occurrence, this may have changed.
It is worth noting that BIT has a relative, which is the SET type, which is saved bit by bit. SET looks like ENUM, but can store multiple values simultaneously (such as tags). SET is for checkbox
as the ENUM stands for the radiobutton
:
CREATE TABLE myset (col SET('A', 'B', 'C', 'D'));
INSERT INTO myset (col) VALUES ('A,D')
In this example, A
, B
, C
and D
occupy a bit each in the bank. The INSERT
above would be setting 2 of these bits.
I am marking this answer as accepted by referring directly to the use of BIT to represent boolean values, but strongly recommend reading the reply by @Bacco.
– bfavaretto