MySQL TEXT: 65,535-Byte Text Limit and Indexing

MySQL TEXT stores nonbinary text up to 65,535 bytes. The effective character count is smaller when the column’s character set uses multiple bytes per character. TEXT values use a character set and collation, unlike binary BLOB values. See MySQL’s BLOB and TEXT reference and string type syntax.

Define a TEXT column

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL
);

Can TEXT have a DEFAULT value?

Yes, in MySQL 8.0.13 and later, when the storage engine supports expression defaults. For TEXT, even a literal default must be written as a parenthesized expression:

CREATE TABLE article_drafts (
    article_id INT PRIMARY KEY,
    body TEXT NOT NULL DEFAULT ('draft')
) ENGINE=InnoDB;

INSERT INTO article_drafts (article_id) VALUES (1);
SELECT body FROM article_drafts WHERE article_id = 1;
-- Returns: draft

DEFAULT 'draft' without parentheses is not valid for a TEXT column. Before MySQL 8.0.13, TEXT columns could not have explicit defaults. Some storage engines do not support expression defaults; check the server version and engine before using this syntax. See the MySQL data type default rules and the related TINYTEXT example.

Indexing TEXT

A regular B-tree index on a TEXT column requires an index prefix length. For example, to index the first 100 characters of body:

CREATE INDEX idx_articles_body_prefix ON articles (body(100));

The prefix length is measured in characters for nonbinary TEXT columns. A prefix index does not index the full value; choose a length that fits your queries and index-size limits. FULLTEXT indexes are a different kind of index and have separate rules.

Large TEXT values can increase memory use and data transfer when selected. However, in modern MySQL the default TempTable engine can handle TEXT and BLOB in memory; including TEXT does not by itself mean every internal temporary table must be on disk. See internal temporary table behavior.

Choose a TEXT size

Use TINYTEXT for the 255-byte limit, TEXT for 65,535 bytes, MEDIUMTEXT for 16,777,215 bytes, or LONGTEXT for the largest text capacity. Character capacity varies by charset. Actual transfer size can be lower due to available memory and the client/server max_allowed_packet settings.

In strict SQL mode, MySQL rejects nonspace data that exceeds the type’s maximum. Without strict mode, MySQL can truncate an overlength value and issue a warning. Check @@SESSION.sql_mode if application behavior depends on this validation.