According to this question and the docs, the storage requirements for a BIT (M) field in MySQL are "approximately (M+7)/8 bytes". This seems awfully vague, and the answers given there don't specify whether this is rounding up or down on the remainder. Which is it?

Specifically, (BIT(7)+7)/8 = 1.75. Is this one or two bytes? -- And is the bit used to determine NULL count towards an extra bit as in the case of SIGNED integers, making BIT (8) (one byte) effectively BIT (9) (two bytes)?

Or is every NULL bit stored in a separate byte at the head of the record, as suggested elsewhere?

Is it true that BIT (M) limits the value of the field, padding it physically with zeros only, but INT (N) doesn't? That seems inconsistent. Isn't the (N) dangerous if, even on the command line, it just tells it to calculate the same but hide the missing digits? -> Is the same true for BIT (M), e.g. can BIT (3) store up to 8 bits = minimum byte length?

Finally, why does the MySQL documentation say on the exact same page that:

Each BIT(M) column takes M bits of storage space. Although an individual BIT column is not 4-byte aligned, NDB reserves 4 bytes (32 bits) per row for the first 1-32 bits needed for BIT columns, then another 4 bytes for bits 33-64, and so on.

How does this jibe with the above, or are the storage requirements different because it is for NDB databases?

Note: I'm aware that MySQL no longer treats BIT as an alias for TINYINT (converting each bit into a byte)...

To clarify -- This is a programming question: I am trying to determine how many bits I should set aside in the limit for a bunch of NOT NULL bitmask fields in my SQL. (to allow for additional flags; I only use four or five flags.) What is the recommended per-byte limit? The additional flags will probably never be used, and I'd prefer to limit the number of possible values in the bitmask.

  • I would suggest taking this over to serverfault. – VikingBlooded Aug 16 '16 at 18:00
  • This is a programming question, though; see above. – Ber Aug 16 '16 at 18:06
  • 2
    I believe they meant DIV instead of the /, i.e. (M+7) DIV 8. – Andriy M Aug 16 '16 at 18:26
  • Personally, I find that the 1 bit for NULL to be so insignificant in the grand scheme that I ignore it. – Rick James Aug 16 '16 at 18:29
  • I'm more worried about accidentally getting junk data in my bitmask. If a bitmask has only 4 possible flags plus a few spare flag bits, and a query accidentally treats it as a bool or int or whatever, then it's easier to control for invalid combos. If BIT (3) actually allows 8 bits but simply truncates the output, it doesn't matter. – Ber Aug 16 '16 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.