Varbinary vs Blob in MySQL

Question

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!

This might be why I get org.hibernate.HibernateException: Wrong column type in ... for column .... Found: blob, expected: longblob — Sridhar Sarnobat
– Sridhar Sarnobat, Commented Feb 2, 2018 at 20:59

Romain · Accepted Answer · 2012-07-17 08:42:05Z

26

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.

edited Jul 17, 2012 at 8:42

answered Dec 12, 2011 at 15:57

Romain

12.9k3 gold badges43 silver badges54 bronze badges

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

11 Comments

Pacerier Over a year ago

Re "varbinary is virtually same as blob"... please cite a source.

Romain Over a year ago

@Pacerier: Edited answer to include link to the (sic) MySQL Documentation page that hints at this fact.

Romain Over a year ago

@Pacerier in-extenso quote from the OP (emphasis mine): "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?". Also note this is the only question mark in the entire OP.

Romain Over a year ago

Last comment from me on the topic: regardless of whether it's PHP ro anything, the question is obviously directed to the client-side viewed difference between types, which is about none. Performance adjustment can be tricky and very dependent on the actual use-case. My answer's geared towards "I need to store binary data, what should I use"

Christophe Fondacci Over a year ago

Be careful when you say that BLOB and VARBINARY are the same : VARBINARY is stored inline so you could seriously degrade select performances if you don't know what you're doing. You should only store compact data or data that you might want to index inline using VARBINARY. Optional information and large binary should use BLOB since its external storage will not impact select performances.

|

Manikandan S · Accepted Answer · 2018-04-06 11:34:55Z

18

Actually blob can be bigger (there are tinyblob, blob, mediumblob & longblob http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html) with up to 2^32 -1 on size limit.

Also blob storage grows "outside" of the row, while max varbinary size is tied by amount of free row size available (so it can actually be less than 64Kb).

There are some minor differences between both

1) With Index scripting (blob needs a prefix size on indexes, varbinary doesn't) http:/en/column-indexes.html
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

2) As already mentioned there are trailling space issues managed differently between varbinary & blob at MySql 5.0.x or earlier versions: http:///en/blob.html http:///en/binary-varbinary.html

(truncating the links, since stackoverflow thinks too many links are spam)

edited Apr 6, 2018 at 11:34

Manikandan S

9221 gold badge10 silver badges19 bronze badges

answered May 22, 2012 at 21:00

Fernando Gonzalez Sanchez

3,5356 gold badges36 silver badges47 bronze badges

1 Comment

Pacerier Over a year ago

So when we have like just a 2KB column is it more beneficial to use blob (out-row) or varbinary (in-row)?

Foobarista · Accepted Answer · 2012-08-08 00:20:56Z

15

One significant difference is blob types are stored in secondary storage, while varbinaries are stored inline in the row in the same way as varchars and other "simple" types.

This can have an impact on performance in a busy system, where the additional lookup to fetch and manipulate the blob data can be expensive.

answered Aug 8, 2012 at 0:20

Foobarista

1511 silver badge2 bronze badges

2 Comments

Pacerier Over a year ago

Is this true for 5.0.3 and up?

ch271828n Over a year ago

does it hold for mysql 8.0?

matt · Accepted Answer · 2013-06-07 18:41:21Z

7

It is worth to point that Memory storage engine does not support BLOB/TEXT but it works with VARBINARY.

answered Jun 7, 2013 at 18:41

matt

4,7541 gold badge32 silver badges34 bronze badges

Comments

akostadinov · Accepted Answer · 2013-03-29 20:31:30Z

1

I am just looking at a test app that stores around 5k binary data in a column. It initially used varbinary but since it is so slow I decided to try blob. Well I'm looking at disk write speed with atop and can't see any difference.

The only significant difference I read in mysql manual is that blobs are unsupported by the memory engine so any temporary tables you create with queries (see when mysql uses temp tables) will be created on-disk and that is much slower. So you better bet on varbinary/binary if it is a short enough to fit into a row (at the moment 64k total for all columns).

answered Mar 29, 2013 at 20:31

akostadinov

19k6 gold badges87 silver badges94 bronze badges

Collectives™ on Stack Overflow

Varbinary vs Blob in MySQL

5 Answers 5

11 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

11 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related