Open topic with navigation
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:
|BLOB||Binary Large Object||
|Binary||Fixed length binary data of length specified in bytes by parameter (
|Variable Binary||Variable Binary data types must be defined with a maximum length parameter (
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 don't request the contents of the
BLOB field don't load it, and changes to other fields in the row don't cause the
BLOB value to be rewritten. However, because it's 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 VARYING (
VARBINARY) values are stored along with the rest of the row in the table. When you select a row, the
BINARY VARYING value is read into memory. If you update any field in the row, the entire row is rewritten, including the
BINARY VARYING field (this is how MVCC ensures transactional consistency).
BLOB type is intended to store large values, particularly the large values which are not always selected, and even more so the large values in rows where other fields are often updated.
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
BIGINT will store the bytes representing that numeric value in the binary string field. Inserting a character value of type
CHAR VARYING will store the bytes representing that string into the binary string field. Inserting a fixed point value with precision and scale (
DECIMAL), a floating point value (
DOUBLE), or a
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,
0xaaa are interpreted as
Inserting a literal into a binary field has the following behaviors:
unsigned int64, will result in an error.
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.
By default, all binary fields are printed as hexadecimal values. To print as another type, use
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;--- abc dog