32

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!

Jimmyb
  • 860
  • 1
  • 8
  • 22
  • This might be why I get `org.hibernate.HibernateException: Wrong column type in ... for column .... Found: blob, expected: longblob` – Sridhar Sarnobat Feb 02 '18 at 20:59

5 Answers5

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.

Romain
  • 12,679
  • 3
  • 41
  • 54
  • 1
    Re "varbinary is virtually same as blob"... please cite a source. – Pacerier Jul 10 '12 at 21:20
  • @Pacerier: Edited answer to include link to the (sic) MySQL Documentation page that hints at this fact. – Romain Jul 11 '12 at 10:09
  • But as shown by the other post below, they are actually effectively very different things altogether. – Pacerier Jul 11 '12 at 11:49
  • @Pacerier from the standpoint of "advantages to using one type over the other **in PHP**", I don't think the differences are meaningful. From a PHP-centric point of view, they're the same (bar white-space trimming). Performance of it isn't related to PHP. – Romain Jul 12 '12 at 13:06
  • Since when did PHP come in.. Of course performance isn't related to PHP, this is a MySQL question. – Pacerier Jul 12 '12 at 13:28
  • 1
    @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 Jul 12 '12 at 13:49
  • This is not so uncommon, the question is badly phrased. But regardless, this is obviously a MySQL related question. – Pacerier Jul 12 '12 at 15:48
  • 1
    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" – Romain Jul 16 '12 at 07:31
  • My point is, "ultimately, VARBINARY is virtually the same as BLOB" although correct, is misleading and can be considered harmful. – Pacerier Jul 16 '12 at 16:49
  • 11
    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. – Christophe Fondacci Aug 20 '13 at 01:00
  • To add to what @ChristopheFondacci said: MySQL also has a [max. row size of 65535 bytes](https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html). This limit is shared across *all* columns, so too many or too large `VARBINARY` (or `BINARY`) columns could get dangerously close. – Tim Čas Jul 13 '16 at 11:24
17

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)

Manikandan S
  • 902
  • 1
  • 8
  • 18
Fernando Gonzalez Sanchez
  • 3,315
  • 4
  • 32
  • 45
  • 1
    So when we have like just a 2KB column is it more beneficial to use blob (out-row) or varbinary (in-row)? – Pacerier Jul 10 '12 at 21:22
14

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.

Foobarista
  • 141
  • 1
  • 2
7

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

matt
  • 4,614
  • 1
  • 29
  • 32
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).

akostadinov
  • 17,364
  • 6
  • 77
  • 85