SQL LONGTEXT

What is SQL LONGTEXT?

LONGTEXT is a MySQL character data type that can store up to 4,294,967,295 bytes (4 GB) of variable-length text.

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 LONGTEXT: Supports: MySQL, MariaDB Closest equivalents: PostgreSQL text, SQL Server varchar(max), Oracle CLOB, SQLite TEXT

SQL LONGTEXT Full Explanation

LONGTEXT is the largest of MySQL’s four text column types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT). It holds any character data such as large documents, HTML, JSON, or logs that exceed MEDIUMTEXT’s 16 MB cap, topping out at roughly 4 GB. LONGTEXT columns use 4 bytes for length metadata plus the stored characters, so total space equals data size + 4 bytes. They respect the table’s CHARACTER SET and COLLATION; UTF-8 may reduce maximum character count because each character can occupy up to 4 bytes. LONGTEXT cannot be indexed in full, but you can prefix-index the first N bytes or use FULLTEXT indexes (InnoDB 5.6+). LONGTEXT behaves like other BLOB/TEXT types: it ignores default values in most MySQL versions prior to 8.0.13, cannot be part of a primary key, and requires the value to be supplied on INSERT unless the column is NULLable. Be mindful of memory and network overhead when selecting LONGTEXT, and use streaming functions when handling very large rows.

SQL LONGTEXT Syntax

CREATE TABLE table_name (
    column_name LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] [NULL | NOT NULL]
);

-- Cast/convert
SELECT CAST(longtext_col AS CHAR) FROM table_name;

SQL LONGTEXT Parameters

Example Queries Using SQL LONGTEXT

-- 1. Table with LONGTEXT for storing raw JSON payloads
CREATE TABLE api_calls (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payload LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Insert a large JSON document
INSERT INTO api_calls (payload)
VALUES (LOAD_FILE('/path/to/large_payload.json'));

-- 3. Retrieve first 500 characters of the LONGTEXT
SELECT SUBSTRING(payload, 1, 500) AS preview
FROM api_calls
WHERE id = 1;

-- 4. Prefix index for faster filtering on the first 255 bytes
ALTER TABLE api_calls
    ADD INDEX payload_preview_idx (payload(255));

Expected Output Using SQL LONGTEXT

Table is created with a LONGTEXT column.
Row is inserted; MySQL stores the full JSON document.
Query returns the first 500 characters of the stored JSON.
Index creation succeeds, enabling faster LIKE or equality searches on the first 255 bytes.

Use Cases with SQL LONGTEXT

Storing large HTML pages or Markdown documents
Archiving application or server logs
Persisting raw JSON or XML payloads for audit purposes
Keeping historical email bodies or chat transcripts
Saving user-generated content that exceeds typical TEXT limits

Common Mistakes with SQL LONGTEXT

Assuming LONGTEXT is fully indexable. Only prefix or FULLTEXT indexes are allowed.
Forgetting that UTF-8 characters may consume up to 4 bytes, lowering maximum character count.
Trying to set a default string before MySQL 8.0.13, which raises an error.
Selecting entire LONGTEXT columns without need, causing heavy memory and network usage.
Using LONGTEXT when MEDIUMTEXT or TEXT would suffice, wasting storage and cache bandwidth.

First Introduced In

MySQL 3.23

Frequently Asked Questions

How large is the LONGTEXT limit?

LONGTEXT can store up to 4,294,967,295 bytes, or roughly 4 GB, of character data.

Can I index a LONGTEXT column?

You cannot create a full B-tree index on LONGTEXT. Use prefix indexes (e.g., column_name(255)) or FULLTEXT indexes on InnoDB 5.6+.

Does LONGTEXT support default values?

Before MySQL 8.0.13, TEXT and BLOB types could not have defaults. Starting in 8.0.13, LONGTEXT columns can specify a default string.

When should I choose LONGTEXT over other text types?

Pick LONGTEXT when you need to hold text larger than 16 MB (MEDIUMTEXT’s limit) such as large HTML pages, logs, or big JSON blobs.