BIT (1) versus TINYINT (1) for Boolean values

According to documentation there is no problem, on the contrary.

  

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted

It says it can save from 1 to 64 bits.

In the past BIT was an alias for TINYINT but it is not anymore. Crazy thing, right? BOOL remains alias of TINYINT .

The minimum storage size of a column of type BIT is 1 byte, even if it only needs 1 bit. If you have multiple columns, there will be an optimization taking advantage of the same byte to accommodate up to 8 columns in each stored byte. So you have the advantage of using it.

Some people recommend not using it because it is not easy to manipulate it correctly. So using a normal numeric type would be better since the gain in space is usually negligible. I do not have enough experience to state, but it does not seem to be so difficult to use correctly. At least if the person has a minimum of notion of how to handle this kind of data and read the documentation before making use of it, how should do with anything in software development. But I also do not see such a big gain in using it. But there may be in certain scenarios that bursts the page for little. Almost no one thinks of optimizations at this level.

It seems like BIT has the lowest level semantics and BOOL highest level, marking status . In general in database, it seems to me semantically that there is little need to use BIT .

Of course, the BOOL or TINYINT can accept values other than 0 and 1 if you do not make any other constraints on your own.

Type BIT was made with bitfields in mind, not "single" bits. Not that you can not save a single bit, but there is no space saving in this case.

A BIT(1) column will occupy one byte per line, as well as a BIT(8) column. A BIT(64) column will occupy 8 bytes. A BIT(59) column will occupy the same 8 bytes, because in any case, would not fit 59 bits in less than 8 bytes.

The manual defines this formula for space estimation: / p>

  

BIT (M) approximately (M + 7) / 8 bytes

According to documentation , you can have a BIT field with up to 64 bits in length.

As for data entry and return, there is a padding that corresponds to the value defined in the column. If you enter 0b011 in a BIT (6) column, you are actually entering 0b000011 . When reading the data, it will get a value of 6 bits back.

Using a BIT column

To use BIT columns literally, use one of 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 on literals .

Considerations

There is a article in that mentions many bugs in the BIT type implementation, I do not know how much it is updated.

In addition, there is a discussion in a MySQL bug report that comments that the BIT column is only implemented in MyISAM.

As is an instance of 2005, this may have changed.

Note that the BIT has a relative, which is the SET type, which is saved bit by bit. SET looks like ENUM, but you can save multiple values simultaneously (like tags, for example). SET is for checkbox as ENUM is for 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 one bit each in the database. The% with% of above would be setting 2 of these bits.