BLOB & Binary Types
In this page:
BLOB Types
BLOB (Binary Large Object) stores raw binary data of variable size — images, audio clips, encrypted payloads — as an opaque byte sequence MySQL doesn't try to interpret or validate.
Example: BLOB Types
CREATE TABLE files (id INT, content BLOB);VARBINARY Type
VARBINARY is the binary counterpart to VARCHAR: it stores exact byte sequences rather than text, which makes it the right fit for short, fixed-format binary values like hashes or tokens rather than large files.
Example: VARBINARY Type
CREATE TABLE tokens (hash VARBINARY(64));BLOB Sizes
MySQL offers four BLOB sizes — TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB — differing only in their maximum storage capacity, so you pick based on the largest file size you realistically expect to store.
Example: BLOB Sizes
CREATE TABLE uploads (
thumbnail TINYBLOB,
photo BLOB,
video MEDIUMBLOB,
archive LONGBLOB
);Retrieving BLOB Data
Because BLOB data isn't human-readable, functions like HEX() let you inspect its raw bytes as a hexadecimal string, which is often the only practical way to eyeball binary content from a query result.
Example: Retrieving BLOB Data
CREATE TABLE files (content BLOB);
INSERT INTO files VALUES (0x48656C6C6F);
SELECT HEX(content) FROM files;BLOB Restrictions
BLOB columns can't have a DEFAULT value, and indexing one requires specifying a prefix length (indexing only the first N bytes) since indexing an entire large binary object outright isn't supported.
Example: BLOB Restrictions
-- BLOB columns cannot have a DEFAULT value
CREATE TABLE files (content BLOB, INDEX idx_content (content(100)));Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: