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

Question

I may be wrong, but I have the impression that in practice the default in Mysql is to use columns of the type TINYINT(1) to store boolean values, true/false or 0/1. Only that TINYINT accommodates up to 1 byte of information, which is not required for boolean values. In SQL Server, I have always used the type BIT for this, but in Mysql I have doubts if this is appropriate. By the way, Mysql Workbench itself maps BOOL and BOOLEAN as aliases of TINYINT(1).

Is there a problem in using BIT(1) for boolean values? If so, which one? If not, why does everyone use TINYINT?

Answer 1

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 article^en 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.

Answer 2

According to the documentation 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 you can store 1 to 64 bits.

In the past BIT was an alias for TINYINT but it’s not over. Crazy thing, right? BOOL continues to be the alias of TINYINT.

The minimum storage size of a column of the type BIT is 1 byte, even if you only need 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 byte stored. So it has advantage in using it.

There are those who recommend not to use it because it is not easy to manipulate it properly. So using a normal numeric type would be better since space gain is usually negligible. I don’t have enough experience to say, but I don’t think it’s so hard to use it properly. At least if the person has a minimum sense of how to treat this type of data and read the documentation before making use of it, as they should do with anything in software development. But also I do not see such a big gain in using it, there may be in certain scenarios that bursts the page for little thing. Almost no one thinks about optimizations at this level.

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

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