CODEWITHSUNDEEP
mysqlblobclob
Jasper N. Brouwer
Jasper N. Brouwer

Reputation: 21817

What is the maximum declared column length of MySql TEXT / BLOB types?

Prelude

When consulting the MySql docs here: http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html, I can derive 2 answers for my question...

1) The more obvious answer:

TINYBLOB   : 2 ^  8 - 1 = 255
BLOB       : 2 ^ 16 - 1 = 65535
MEDIUMBLOB : 2 ^ 24 - 1 = 16777215
LONGBLOB   : 2 ^ 32 - 1 = 4294967295

2) The bit more complicated answer:

TINYBLOB   : 2 ^  8 - 1 = 255
BLOB       : 2 ^ 16 - 2 = 65534
MEDIUMBLOB : 2 ^ 24 - 3 = 16777213
LONGBLOB   : 2 ^ 32 - 4 = 4294967292

MySql stores the size of the actual data along with that data. And in order to store that size it will need:

So to store the data plus the size of the data, and prevent it from exceeding 256 / 64K / 16M / 4G bytes of needed storage, you will need the -1 / -2 / -3 / -4 factor when determining the maximum declared column length (not -1 / -1 / -1 / -1). I hope this makes sense :)

The question

Which of these 2 answers is correct? (Assuming one of them is.)

Upvotes: 2

Views: 4389

Answers (1)

Mat
Mat

Reputation: 206659

It's answer 1.

From the doc you link to:

These correspond to the four BLOB types and have the same maximum lengths and storage requirements. See Section 11.6, “Data Type Storage Requirements”.

That other page has a table with those constraints. For LONGBLOB, the storage required is:

L + 4 bytes, where L < 232

L represents the actual length in bytes of a given string value.

As for the maximum declared column length, just try it out yourself:

mysql> create table foo(a blob(4294967295));
Query OK, 0 rows affected (0.08 sec)

mysql> create table bar(a blob(4294967296));
ERROR 1439 (42000): Display width out of range for column 'a' (max = 4294967295)

(You can't declare a size for the other three blob types.)

Upvotes: 2

Related Questions