SQL Reference · Constraints and Data Types
SQL BLOB and binary types
Reference for SQL binary types: BLOB, BYTEA, VARBINARY and RAW, hex literals, byte length versus character length, and why large files rarely belong in a table.
A binary type stores raw bytes rather than text: image thumbnails, hashes, encrypted values, compressed data or fixed-size identifiers. The standard name is BLOB (binary large object). Databases also offer fixed or limited variants such as BINARY(n) and VARBINARY(n).
Unlike text, a blob has no character set and no collation. Comparing two blobs compares their bytes one by one, and LENGTH() returns a number of bytes. SQLite writes blob literals as x'...' with pairs of hex digits.
CREATE TABLE attachments ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, content BLOB );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
BLOB BINARY(n) VARBINARY(n) x'48656C6C6F' -- hexadecimal blob literal (SQLite, MySQL, standard SQL)
| Type | Meaning |
|---|---|
BLOB |
Binary data of any length. |
BINARY(n) |
Fixed n bytes, padded with zero bytes (not in SQLite). |
VARBINARY(n) |
Up to n bytes (not enforced in SQLite). |
Returns: bytes. Most clients show them as hexadecimal; use hex() (SQLite, MySQL, Oracle) or encode(x, 'hex') (PostgreSQL) to print them.
Dialect differences
| Database | Type name | Literal | Notes |
|---|---|---|---|
| SQLite | BLOB |
x'4A5B' |
Any value can be stored as a blob. zeroblob(n) and randomblob(n) build them. |
| PostgreSQL | BYTEA |
'\x4a5b' |
Hex format by default. Large objects are a separate feature. |
| MySQL | BLOB, MEDIUMBLOB, LONGBLOB, BINARY, VARBINARY |
x'4A5B' |
BLOB types are limited to 64 KB, 16 MB and 4 GB in that order. |
| SQL Server | BINARY(n), VARBINARY(n), VARBINARY(MAX) |
0x4A5B |
IMAGE is deprecated. |
| Oracle | BLOB, RAW(n) |
HEXTORAW('4A5B') |
RAW is for short values up to a few thousand bytes. |
-- PostgreSQL SELECT '\x48656c6c6f'::bytea, encode('\x48656c6c6f'::bytea, 'escape'); -- SQL Server SELECT 0x48656C6C6F, CONVERT(VARCHAR(10), 0x48656C6C6F); -- Oracle SELECT RAWTOHEX(HEXTORAW('48656C6C6F')) FROM dual;
Usage notes
- Bytes, not characters.
length(CAST('café' AS BLOB))counts UTF-8 bytes. Uselength()on text if you want characters. - Blobs are not text. In SQLite,
'A' = x'41'is false because a text value and a blob value are different storage classes, even if their bytes match. Cast explicitly when you compare. - Large files: database rows get slow and backups large when they hold megabytes of file data. Store big files in object storage and keep a path or URL in the table. Small values (hashes, thumbnails under a few KB) are fine.
- Hashes and fixed-size ids: a 16-byte
BLOBis half the size of the 32-character hex text for the same value. See UUIDs. - Reading in application code returns a byte array (
bytes,Buffer,byte[]), not a string. Use parameters instead of building hex literals by string concatenation. - No sensible sorting. Ordering by a blob column sorts by raw bytes, which is rarely what a user expects.
- Indexes on blobs work but get large quickly. Index a hash of the content instead.
Related
- UUIDs,
HEX(),CAST,LENGTH(), SQLite type affinity - Lessons: Data types, Types and CAST