BLOB Types

The BLOB type is a form of VARBINARY string type. (The BLOB type has no connection to columnstore segments, also known as blobs. See Understanding Sort Key Selection for more information on columnstore blobs.) The data for this data type is stored in memory for rowstore tables and the main table structure holds pointers to the memory locations.

The total space consumed by a particular string value is:

  • The byte length of the string.

  • An 8-byte overhead for the pointer.

  • A 4-byte overhead for storing the length. There is no 4-byte overhead if the field is nullable.

Note

One exception is values less than 7 bytes long. Those small values are stored directly in the pointer area.

While BLOB data types operate the same for most English-language alphanumeric strings, there are several important differences in their behavior. For example, BLOB fields store data verbatim, regardless of whether they are valid Unicode strings. Sorting and comparing a BLOB uses the exact binary representation.

To reduce memory fragmentation, memory for variable-sized data is allocated from a fixed list in multiples of eight (16, 32, 40, etc.), but not every multiple of eight. Variable data of other lengths are rounded up to the next nearest multiple of eight within the list. For example, a ten (10) byte string would be allocated sixteen (16) bytes.

Data Type

Max Length

VARBINARY

65533 bytes

Note

There are several variants of the BLOB type, which are listed below. Aside from their maximum lengths, there is no practical difference between these types in SingleStore, including performance or memory efficiency. They are implemented solely for backward compatibility with MySQL syntax.

Name

Max Length

Description

LONGBLOB

4GB

VARBINARY - While the DDL will allow you to specify a size of up to 4GB, there is an internal limit applied when assigning a value to a LONGBLOB field that caps the maximum size of a single value to max_allowed_packet. This is 100MB by default and can be set to up to 1GB.

MEDIUMBLOB

16 MB

VARBINARY

BLOB

65,535 bytes

VARBINARY

TINYBLOB

225 bytes

VARBINARY

Unlike variable-length TEXT types, comparison operators treat trailing spaces differently when used with a variable-length BLOB type. Also, BLOB type uses the binary character set while TEXT uses UTF8 by default, but can be changed.

Last modified: August 29, 2024

Was this article helpful?