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.

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

Name Description Syntax

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)`

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)

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