SQL TINYBLOB

What is SQL TINYBLOB?

MySQL binary string type that stores up to 255 bytes of data.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TINYBLOB:

SQL TINYBLOB Full Explanation

TINYBLOB is one of MySQL’s four BLOB (Binary Large Object) data types. It can hold up to 255 bytes (2^8−1) of raw binary data, making it ideal for very small files, encryption keys, UUIDs, hash outputs, and other compact binary payloads. Like all BLOB types, TINYBLOB is stored outside the table row if it does not fit the row’s static size limit; the column itself only holds a 1-byte length prefix and a pointer to the data. TINYBLOB is case-sensitive, is not automatically character-set converted, and participates in comparisons byte by byte. Indexing is possible but must specify a prefix length because full-length keys cannot exceed MySQL’s index size limits. Conversions to and from other BLOB types are implicit when size allows, but attempting to store more than 255 bytes raises a truncation error unless SQL mode permits silent truncation.

SQL TINYBLOB Syntax

column_name TINYBLOB

SQL TINYBLOB Parameters

Example Queries Using SQL TINYBLOB

-- Create a table with a TINYBLOB column
CREATE TABLE api_keys (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  key_hash TINYBLOB NOT NULL
);

-- Insert a SHA-256 hash (32 bytes)
INSERT INTO api_keys (user_id, key_hash)
VALUES (42, UNHEX('9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08'));

-- Retrieve the hash in hex form
SELECT id, HEX(key_hash) AS key_hash_hex FROM api_keys;

-- Attempt to insert 300 bytes (will fail)
INSERT INTO api_keys (user_id, key_hash)
VALUES (7, REPEAT(0x41, 300));

Expected Output Using SQL TINYBLOB

The table is created
One row containing a 32-byte hash is inserted
The SELECT returns the hash as a 64-char hex string
The final INSERT fails with ERROR 1406: Data too long for column 'key_hash'

Use Cases with SQL TINYBLOB

Storing small binary tokens, salts, or encryption keys
Keeping short thumbnails or icons in binary form
Recording UUIDs as 16-byte binaries instead of 36-char strings
Caching compact compressed data blobs

Common Mistakes with SQL TINYBLOB

Confusing TINYBLOB with TINYTEXT; the latter is character data and uses character set collation
Assuming it can store 255 characters instead of 255 bytes
Forgetting to define a prefix length when indexing a TINYBLOB column
Attempting to use string functions (e.g., CONCAT) without CASTing to CHAR or BINARY as needed

First Introduced In

MySQL 3.23

Frequently Asked Questions

What is the storage limit of TINYBLOB?

TINYBLOB stores up to 255 bytes. If you try to insert more, MySQL raises a truncation error unless the SQL mode allows silent truncation.

When should I use TINYBLOB instead of VARBINARY?

Use TINYBLOB when you need a LOB that may be stored off-row and you prefer LOB semantics. Use VARBINARY when you want the data inline and know the maximum length at design time.

Can TINYBLOB be part of a primary key?

Technically yes, but you must define a prefix length (e.g., `PRIMARY KEY (hash(16))`). Doing so is rare; consider using a fixed-length binary column instead.

How do I convert TINYBLOB to readable text?

Use `SELECT CONVERT(tinyblob_col USING utf8mb4);` or `HEX(tinyblob_col)` if the data is truly binary and not encoded text.