CODEWITHSUNDEEP
mysqlsqldatatypes
armin.miedl
armin.miedl

Reputation: 1130

What use has mysql data type 'BINARY(0)'?

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...

Upvotes: 1

Views: 1521

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521179

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.

Upvotes: 1

Related Questions