SQL MEDIUMTEXT

What is SQL MEDIUMTEXT?

MEDIUMTEXT is a MySQL text data type that can store up to 16,777,215 bytes (16 MB) 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 MEDIUMTEXT: Supported: MySQL, MariaDB, Aurora MySQL, TiDB. Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake.

SQL MEDIUMTEXT Full Explanation

MEDIUMTEXT is one of the four MySQL TEXT family types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT). It is designed for medium-sized free-form character data such as blog posts, HTML documents, or JSON blobs that exceed the 65,535-byte limit of TEXT but do not require the 4-GB capacity of LONGTEXT.Storage and memory:- Each MEDIUMTEXT value uses L + 3 bytes, where L is the length of the stored string in bytes. The extra three bytes hold the length information.- The maximum length is 16,777,215 bytes (2^24 − 1).Character set and collation:- MEDIUMTEXT follows the column or table default character set unless explicitly overridden with CHARACTER SET and COLLATE clauses.- UTF-8 encoded MEDIUMTEXT can hold roughly 5.5 million 3-byte characters.Indexing:- MEDIUMTEXT columns cannot be used as a PRIMARY KEY.- When indexing, you must specify a prefix length (e.g., INDEX (column_name(255))).Limitations and caveats:- MEDIUMTEXT columns are not stored inline in some storage engines; large values may be stored off-page, which can affect performance.- MEDIUMTEXT is case-sensitive or case-insensitive depending on the chosen collation.- MEDIUMTEXT is not available in PostgreSQL, SQL Server, Oracle, or SQLite; equivalent types include TEXT, CLOB, or VARCHAR(max).

SQL MEDIUMTEXT Syntax

column_name MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

SQL MEDIUMTEXT Parameters

Example Queries Using SQL MEDIUMTEXT

-- Create a table with a MEDIUMTEXT column
CREATE TABLE blog_posts (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT CHARACTER SET utf8mb4,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a large article (simplified)
INSERT INTO blog_posts (title, body)
VALUES ('Galaxy Launch Recap', REPEAT('Great product! ', 100000));

-- Retrieve a post
SELECT id, LEFT(body, 200) AS excerpt FROM blog_posts LIMIT 1;

Expected Output Using SQL MEDIUMTEXT

The table is created successfully, the long text is stored without truncation, and the SELECT returns the first 200 characters of the body column

Use Cases with SQL MEDIUMTEXT

Storing medium-sized articles, blog posts, or documentation
Persisting JSON payloads larger than 64 KB but smaller than 16 MB
Archiving email bodies, logs, or chat transcripts that exceed TEXT limits
Holding HTML templates or Markdown files within an application database

Common Mistakes with SQL MEDIUMTEXT

Assuming MEDIUMTEXT exists in non-MySQL databases
Forgetting to limit index prefix length on MEDIUMTEXT columns, leading to errors
Using MEDIUMTEXT when TEXT or VARCHAR would suffice, unnecessarily increasing storage overhead
Overlooking character set size when calculating maximum storable characters

First Introduced In

MySQL 3.23 (1998)

Frequently Asked Questions

What is the size limit of MEDIUMTEXT?

MEDIUMTEXT can store up to 16,777,215 bytes. That equals 16 MB of data or roughly 5.5 million UTF-8 characters.

When should I choose MEDIUMTEXT over TEXT or LONGTEXT?

Use MEDIUMTEXT when your content regularly exceeds the 65 KB limit of TEXT but rarely surpasses 16 MB. If you expect larger data, pick LONGTEXT.

Can I set MEDIUMTEXT as a PRIMARY KEY?

No. MEDIUMTEXT cannot serve as a PRIMARY KEY because MySQL does not permit keys on columns that may exceed index length limits.

How do I index a MEDIUMTEXT column?

Specify a prefix length: `CREATE INDEX idx_body ON blog_posts (body(255));`. Full-column indexing is not allowed.