Varbinary vs Blob in MySQL

mysql binary blob
Jimmyb picture Jimmyb · Dec 12, 2011 · Viewed 42.3k times · Source

I have about 2k of raw binary data that I need to store in a table, but don't know whether to choose the Varbinary or Blob type. I have read through the descriptions in the MySQL docs but didn't find any contract and compare descriptions. I also read that varbinary only supports up to 255 characters, but I successfully created a varbinary(2048) field, so I'm a bit confused.

The binary data does not need to be indexed, nor will I need to query on it. Is there an advantage to using one type over the other from PHP?

Thanks!

Answer

Romain picture Romain · Dec 12, 2011

VARBINARY is bound to 255 bytes on MySQL 5.0.2 and below, to 65kB on 5.0.3 and above.

BLOB is bound to 65kB.

Ultimately, VARBINARY is virtually the same as BLOB (from the perspective of what can be stored in it), unless you want to preserve compatibility with "old" versions of MySQL. The MySQL Documentation says:

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like.

Related questions

Inserting and selecting UUIDs as binary(16)

I don't understand why SELECT UUID(); Returns something like: 3f06af63-a93c-11e4-9797-00505690773f But if I insert it into a binary(16) field (the UUID() function) with for instance a BEFORE INSERT trigger and run a select, …

Read More
What effects does using a binary collation have?

While answering this question, I became uncertain about something that I didn't manage to find a sufficient answer to. What are the practical differences between using the binary utf8_bin and the case insensitive utf8_general_ci collations? I can …

Read More
Storing MySQL GUID/UUIDs

This is the best way I could come up with to convert a MySQL GUID/UUID generated by UUID() to a binary(16): UNHEX(REPLACE(UUID(),'-','')) And then storing it in a BINARY(16) Are there any implications of doing …

Read More