Binary String Types

BINARY

BINARY is a fixed-length string type in SingleStore. BINARY requires LENGTH bytes of memory. binary(0) not null allocates 1 byte.

Data Type	Size	Size (Not Null)
BINARY(LENGTH)	LENGTH + 1 bytes	LENGTH bytes

CHAR is another fixed-length string type in SingleStore. For more details, refer to String Types.

VARBINARY

VARBINARY is a variable-length string type in SingleStore. The data for this data type is stored in memory for rowstore tables and the main table structure holds pointers to the memory locations.

VARBINARY fields store data verbatim, regardless of whether they are valid Unicode strings. Sorting and comparing a VARBINARY uses the exact binary representation.

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.

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

VARCHAR and LONGTEXT are other variable-length string types in SingleStore. For more details, refer to String Types.

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.

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.

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.

Binary String Types

On this page

BINARY

VARBINARY

BLOB Types

Was this article helpful?

On this page

Was this article helpful?