In MySQL, is a BIT column a suitable way to store 5, 6, or 7-byte integers?

Question

I have a table whose size I'd like to keep down, and one of the columns can be treated as a 5-byte unsigned integer. This is a column that I won't need to search by.

MySQL offers integer datatypes

• TINYINT, for 1-byte integers
• SMALLINT, for 2-byte integers
• MEDIUMINT, for 3-byte integers
• INT, for 4-byte integers
• BIGINT, for 8-byte integers.

But it also offers BIT(M), for 1 ≤ M ≤ 64. This stores (effectively) an unsigned integer from 0 to 2^M-1. Is there a reason to avoid using a BIT(40) column to store a 5-byte integer? (As I said, I will not need to search by this column. Any difficulties relating to query execution speed may therefore be ignored.)

Answer 1

Use the native integer types, such as tinyint.

Using a bit field does not save any bytes. That is, a bit field of 1 to 8 bits takes 1 byte and will thus occupy as much space as a tinyint.

Also, bitfields are a bit harder to work with if you only want to use them as numbers.

Edit: Actually, in some cases it may save some bytes. However, I would still advice using the integer types.

Answer 2

MySQL treats BIT as textual data and not numbers. Usually this is the reason working with BIT should be avoided - it may be a little bit confusing. For example, we want to store number 57 in BIT(8) column. The number 57 will be stored as `b'00111001' i.e. binary representative of 57. But it will be displayed as 9 as 57 is ASCII code of '9'. To get proper integer value you have convert columns data to numerical value. You can test this behaviour with:

CREATE TABLE testbit(a BIT(8));
INSERT INTO testbit VALUES (b'00111001');
INSERT INTO testbit VALUES (57);
SELECT a FROM testbit;
SELECT a+0 FROM testbit;