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, will be 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 will vary, but cannot exceed the maximum length defined. BINARY VARYING(n)

A BLOB is a collection of binary data stored as one entity. It is the data type of choice for images, audio, and other large multimedia data being stored.

A binary string is a sequence of octets (bytes). Binary strings are appropriate for storing data that the programmer thinks of as "raw bytes", whereas character strings are appropriate for storing text. Binary strings have no character set or collation, and sorting and comparison are done based on the numeric values of the stored bytes.

In comparisons of all types of Binary data types, all bytes are significant.

Note: Binary strings are stored within the entire data record for a row. BLOBs are stored in separate atomThe internal object structure representing all data in a NuoDB database. Atoms are self-coordinating objects that represent specific types of information (such as data, indexes or schemas).s. BLOBs are not versioned. So there is only one version at any one time for a BLOB field in a record. Binary strings have multiple versions tracking any changes to that particular field in a record. BLOBs are intended for large objects.

Binary Input Formats

Any type value can be inserted into a binary string field as any value can be cast to binary. Inserting an integer value of type SMALLINT, INTEGER, or BIGINT will store the bytes representing that numeric value in the binary string field. Inserting a character value of type STRING, CHAR, or CHAR VARYING will store the bytes representing that string into the binary string field. Inserting a fixed point value with precision and scale (NUMERIC or DECIMAL), a floating point value (DOUBLE), or a DATE/TIME type will insert the internal representation of that value.

Literals being inserted into a binary string field can be a quoted string, a numeric constant in decimal or hexadecimal format. The hexadecimal value is specified with a leading "0X" or "0x" and the rest of the string is interpreted as a series of two digit hexadecimal values, each representing one byte in the binary string. The hexadecimal values may be upper or lower case. When using the hexadecimal syntax, values that contain an odd number of digits are treated as having an extra leading 0. For example, 0xa and 0xaaa are interpreted as 0x0a and 0x0aaa.

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

Note: 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.

Binary Output

By default, all binary fields are printed as hexadecimal values. To print as another type, use CAST.


            CREATE TABLE bin_table (col_bin BINARY(3));
INSERT INTO bin_table VALUES ('abc');
SELECT * FROM bin_table;
INSERT INTO bin_table VALUES (0x646F67);
SELECT * FROM bin_table;
SELECT CAST(col_bin AS STRING) FROM bin_table;