1

Right now I'm trying to learn the details of MySQL. The type BINARY needs as many storage bytes as provided via its parameter, so for example, if I define a column as BINARY(8) it consumes 8 bytes.

On the site https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings, there is a table mapping the types to their storage requirements. And it says that I can define a BINARY(0). But in my opinion, it does not make sense. BINARY(0) would mean that I can store 0 bytes - so nothing. Do I miss a thing? What use does it have? Or what is the reason for that?

On the other hand, I cannot define a bigger BINARY-column than one with 255 bytes. I always thought the reason for 255 is that you start counting at 0. But when you don't need a BINARY(0) you could define a BINARY(256) without problems...

Improve this question
4
  • Please don't ask two questions in one post. Commented Apr 28, 2019 at 12:54
  • Keep in mind the column can be nullable. Commented Apr 28, 2019 at 12:59
  • 3
    Related: whats-the-purpose-of-varchar0 Commented Apr 28, 2019 at 13:13
  • "I cannot define a bigger BINARY-column than one with 255 bytes." If you need bigger simply use BLOB (65,535 bytes max) instead. Commented Apr 28, 2019 at 13:14

1 Answer 1

Reset to default
1

I had to poke around on this one, because I didn't know myself. From this link, we can see that BINARY(0) can store two types of values:

  • NULL
  • empty string

So, you could use a BINARY(0) column much in the same way you would use a non nullable BIT(1) column, namely as a true/false or yes/no column. However, the storage requirement of BINARY(0) is just one bit, which requires no additional storage beyond the boundary for nullable columns.

Since the non NULL state of the BINARY(0) column would be empty string, which translates to zero, you could find all such records using:

SELECT *
FROM yourTable
WHERE bin_zero_column = 0;

The unmarked NULL records could find found using WHERE bin_zero_column IS NULL.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

BIT(1) can be 0, 1 or NULL. Also you didn't mention the theoretical storage size of 0 bytes for BINARY(0).
Hmm .... if you can distinguish between NULL and '', then it is impossible for the storage cost to be zero. It requires at least one bit of state on physical storage to represent NULL versus ''. (If not, then we have the basis for a magical compression mechanism that can represent data with zero storage ... using lots of BINARY(0) columns.)
Tim - I'm not a database expert either. But this is basic information theory.
It isn't the fact of whether a column is null in a row that consumes the 1 bit, it's whether it is nullable. A nullable column has one bit allocated per row to indicate whether it is null in that row, so each 8 nullable columns require 1 byte of storage per row to store the flag of whether the column is null. A nullable BINARY(0) thus consumes 1 bit of storage per row but as long as this doesn't push you past the next mod 8 boundary of the number of nullable columns, it rides for free, because rows consume storage in whole bytes, not bits.
@armin.miedl I believe the specifics of BIT are at the discretion of the storage engine, where InnoDB stores each column separately in an integral number of bytes, but MyISAM packs them together.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.