SQL Binary Types

All SQL Binary Types are sequences of octets. They are used for storing data considered to be 'raw' bytes, as opposed to the character strings that are appropriate for storing text. Binary type values have no character set or collation, and sorting and comparison are done based on the numeric values of the stored bytes. When comparing binary type values all bytes are significant.

Binary Type

The following table provides a brief description of, and syntax for, the different SQL binary types:

Name Description Syntax

BLOB

Binary Large Object

BLOB

Binary

Fixed length binary data of length specified in bytes by parameter ( n ). Binary with no length parameter ( n ) specified is defined as a single byte (same as binary(1)). Binary values are not padded when stored. For example, a field declared BINARY(4) but an item inserted is only 3 bytes long, is stored as 3 bytes, not 4.

BINARY [(n)]

Variable Binary

Variable Binary data types must be defined with a maximum length parameter ( n ). Variable Binary values are not padded when stored and their lengths vary, but cannot exceed the maximum length defined.

BINARY VARYING(n)
or
VARBINARY(n)

  • The maximum size of a value of type Binary or Variable Binary stored in a column must be less than 1.0 GB.

  • The maximum size of an individual BLOB value stored in a column must be less than 1.9 GB.

  • The combined length of the binary values (excluding BLOBs) of all columns in a row must be less than 1.9 GB. If the size exceeds 1.9 GB, the update is rejected, reporting "width of record being inserted is too large: size of <size_of_the_row> exceeds system limit of 1900000000".

Bytes Type

The following table provides a brief description and syntax for Bytes:

Name Description Syntax

Bytes

A NuoDB extension. NuoDB handles binary types of potentially unlimited length.

BYTES

  • The maximum size of a value of type Bytes stored in a column must be less than 1.0 GB.

  • The combined length of the Bytes values of all columns in a row must be less than 1.9 GB. If the size exceeds 1.9 GB, the update is rejected, reporting "width of record being inserted is too large: size of <size_of_the_row> exceeds system limit of 1900000000".

Storing Values for Binary Types

BLOB values are stored separately from the rest of the contents of their row, and only a reference to the BLOB value is kept in the row. SELECT statements which do not request the contents of the BLOB field do not load it, and changes to other fields in the row do not cause the BLOB value to be rewritten. However, because it is stored separately it does take more time to retrieve the value when it is required. Even though BLOB values are stored separately from the rest of the data in a row, they still participate in Multi-Version Concurrency Control (MVCC) and provide the same transactional consistency guarantees as other types.

BINARY and BINARY VARYING values are stored along with the rest of the row data in the table.

Binary Input Formats

Inserting a literal into a binary field has the following behaviors:

  • Inserting a integer literal (SMALLINT, INTEGER, BIGINT), either in base hex or decimal, will result in that number’s big endian binary representation. Only the minimum number of bytes required to represent the number are used.

  • Inserting a quoted string will result in the string of UTF-8 character values for each character in the string.

In numeric contexts, hexadecimal values act like integers. In string contexts (binary and char), hexadecimal values act like binary strings, where each pair of hex digits is converted to a character.

Example

CREATE TABLE bin_table (col_bin BINARY(3));
INSERT INTO bin_table VALUES ('abc');
SELECT * FROM bin_table;

 COL_BIN
 --------

  616263

INSERT INTO bin_table VALUES (0x646F67);
SELECT * FROM bin_table;

 COL_BIN
 --------

  616263
  646F67

SELECT CAST(col_bin AS STRING) FROM bin_table;

 [CAST]
 -------

   abc
   dog