Menu

MySQL TINYTEXT: 255-Byte Limit and DEFAULT Rules

Updated on

MySQL TINYTEXT stores nonbinary text in up to 255 bytes. That allows up to 255 single-byte characters; fewer characters fit when the chosen character set encodes them using multiple bytes. The type uses a one-byte length prefix. See MySQL’s string type syntax and BLOB and TEXT reference.

Define a TINYTEXT column

CREATE TABLE short_notes (
    note_id INT PRIMARY KEY,
    note_text TINYTEXT NOT NULL
);

Use TINYTEXT only if its byte capacity fits the valid content. For short values, VARCHAR is often a clearer choice: VARCHAR(M) declares a character limit, whereas TINYTEXT has a 255-byte limit. For larger text, compare TEXT, MEDIUMTEXT, and LONGTEXT.

Can TINYTEXT have a DEFAULT value?

Yes, in MySQL 8.0.13 and later, when the storage engine supports expression defaults. A TINYTEXT default must be written as a parenthesized expression, even when it is a string literal. For example, with InnoDB:

CREATE TABLE short_notes_with_default (
    note_id INT PRIMARY KEY,
    note_text TINYTEXT NOT NULL DEFAULT ('pending')
) ENGINE=InnoDB;

INSERT INTO short_notes_with_default (note_id) VALUES (1);

SELECT note_text
FROM short_notes_with_default
WHERE note_id = 1;

note_text
---------
pending

Writing DEFAULT 'pending' without parentheses is not permitted for a TEXT-family column. Before MySQL 8.0.13, TEXT and BLOB columns could not have explicit defaults. Some engines, including NDB, do not support expression defaults; check the engine and server version before using this syntax. See MySQL’s data type default rules and MySQL 8.0 version-specific rules.

Length limits and SQL mode

The largest value a client can transfer is also limited by available memory and communication buffers, including the client and server max_allowed_packet settings. If data exceeds the type limit, strict SQL mode rejects nonspace truncation; without strict mode, MySQL can truncate the value and issue a warning.