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 |
---|---|---|
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 ( |
|
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
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
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