SQL TINYTEXT

What is the SQL TINYTEXT data type?

TINYTEXT is a MySQL text data type that stores up to 255 bytes of variable-length character 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 TINYTEXT:

SQL TINYTEXT Full Explanation

TINYTEXT is the smallest of MySQL’s TEXT family (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT). It can hold from 0 to 255 bytes, using a 1-byte length prefix that records the actual string length. Because storage is byte-based, the character limit varies with the column’s character set (e.g., up to 255 ASCII characters but fewer multibyte UTF-8 characters). TINYTEXT values are stored off-row, with only a pointer kept in the base table row, which can reduce row size but adds one extra lookup when reading. Unlike VARCHAR, a TINYTEXT column cannot have a default value other than NULL and cannot be indexed for its full length without specifying a prefix length. It obeys the current or declared CHARACTER SET and COLLATE rules, supports all standard string functions, and participates in BLOB/TEXT specific behaviors such as implicit temporary tables when used in GROUP BY or ORDER BY.

SQL TINYTEXT Syntax

column_name TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name];

SQL TINYTEXT Parameters

CHARACTER SET (string) - Sets the character set for the column
COLLATE (string) - Sets the collation for the column

Example Queries Using SQL TINYTEXT

-- Create a table using TINYTEXT
CREATE TABLE products (
  id INT PRIMARY KEY,
  short_description TINYTEXT CHARACTER SET utf8mb4
);

-- Insert a short string
INSERT INTO products (id, short_description)
VALUES (1, 'Lightweight hiking backpack');

-- Attempt to insert longer than 255 bytes (will error)
INSERT INTO products (id, short_description)
VALUES (2, REPEAT('A', 300));

-- Index the first 100 characters
CREATE INDEX idx_desc_prefix ON products(short_description(100));

Expected Output Using SQL TINYTEXT

The first INSERT succeeds and stores the text off-row
The second INSERT fails with Error 1406: Data too long for column
The CREATE INDEX statement builds a prefix index on the first 100 characters

Use Cases with SQL TINYTEXT

Store small free-text notes or descriptions under 255 bytes
Keep row size small when many optional short strings are present
Rapidly prototype when the precise maximum length is unknown but assumed short

Common Mistakes with SQL TINYTEXT

Assuming 255 characters instead of 255 bytes
Forgetting that multibyte UTF-8 characters reduce the actual character capacity
Expecting to set a non-NULL default value (not allowed)
Creating an index without specifying a prefix length, which fails
Using TINYTEXT when VARCHAR(255) would be faster for in-row retrieval

First Introduced In

MySQL 3.23

Frequently Asked Questions

What is the maximum length of TINYTEXT?

TINYTEXT holds up to 255 bytes of data. With single-byte character sets this equals 255 characters, while multibyte sets reduce the character count.

When should I prefer TINYTEXT over VARCHAR?

Choose TINYTEXT if you need flexible, short free-text storage without default values and want to keep row size small via off-row storage. If you need default values or full-length indexing, VARCHAR is usually preferable.

Can I index a TINYTEXT column?

Yes. Provide a prefix length in the index definition, such as CREATE INDEX idx_txt ON t(col(80)). MySQL cannot index the entire TINYTEXT field without a length.

Does the 255 limit refer to bytes or characters?

It refers to bytes. In multibyte encodings like utf8mb4, the maximum character count can be less than 255 if any characters use more than one byte.