SQL MEDIUMINT

What is SQL MEDIUMINT?

MEDIUMINT is a MySQL numeric data type that stores 3-byte signed or unsigned integers.

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 MEDIUMINT: Supported: MySQL, MariaDB, Percona Server, TiDB. Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Standard SQL.

SQL MEDIUMINT Full Explanation

MEDIUMINT is a MySQL-specific integer type that occupies 3 bytes (24 bits) per row. In signed mode it stores values from -8,388,608 to 8,388,607. With the UNSIGNED attribute the range shifts to 0–16,777,215. MEDIUMINT is useful when SMALLINT’s 2-byte range is too small but INT’s 4-byte storage is excessive. You may specify an optional display width (M) for legacy applications; it does not affect the stored range and is ignored in MySQL 8.0 and newer when the ZEROFILL attribute is absent. MEDIUMINT can be combined with ZEROFILL to left-pad displayed values with zeros and implicitly add UNSIGNED. MEDIUMINT columns can be AUTO_INCREMENT and participate in indexes. Because MEDIUMINT is not part of the SQL standard, portability to other RDBMSs is limited. Overflow inserts or updates throw an out-of-range error unless strict SQL mode is disabled, in which case the value is clipped and a warning is issued.

SQL MEDIUMINT Syntax

column_name MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

SQL MEDIUMINT Parameters

M (Integer) - Optional display width used only for formatting prior to MySQL 8.0.
UNSIGNED (Keyword) - Shifts the range to non-negative values (0|||16,777,215).
ZEROFILL (Keyword) - Pads displayed numbers with leading zeros to the display width and automatically sets UNSIGNED. If M is omitted, the default width is 10.

Example Queries Using SQL MEDIUMINT

-- Create a table using MEDIUMINT
CREATE TABLE accounts (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    balance MEDIUMINT NOT NULL DEFAULT 0
);

-- Insert a value within the signed range
INSERT INTO accounts (balance) VALUES (250000);

-- Attempt to insert an out-of-range value (will error in strict mode)
INSERT INTO accounts (balance) VALUES (20000000);

-- Modify a column to UNSIGNED MEDIUMINT
ALTER TABLE accounts MODIFY balance MEDIUMINT UNSIGNED;

-- Query rows with large balances
SELECT id, balance FROM accounts WHERE balance > 50000;

Expected Output Using SQL MEDIUMINT

CREATE TABLE executes and defines the columns with 3-byte storage each
First INSERT succeeds and stores 250000
Second INSERT fails with ERROR 1264 – Out of range value for column 'balance' (strict mode) or is clipped with a warning in non-strict mode
ALTER TABLE changes the column to UNSIGNED, extending its positive range
SELECT returns all rows whose balance exceeds 50000

Use Cases with SQL MEDIUMINT

Storing counters, identifiers, or quantities that exceed SMALLINT but never reach INT ranges.
Optimizing storage for medium-sized lookup tables or fact tables with millions of rows.
AUTO_INCREMENT primary keys in resource-constrained systems where 4-byte INT wastes space.
Replacing older TINYINT/SMALLINT columns that began to overflow.

Common Mistakes with SQL MEDIUMINT

Assuming MEDIUMINT exists in all databases – it is MySQL-specific.
Forgetting UNSIGNED when negative numbers are impossible, wasting half the positive range.
Misunderstanding display width (M) – it affects only formatting, not storage or range.
Allowing silent overflow by running in non-strict SQL mode, which may silently clip data.
Expecting ZEROFILL to pad values in client libraries that format numbers independently.

First Introduced In

MySQL 3.23

Frequently Asked Questions

What range does MEDIUMINT support?

Signed MEDIUMINT ranges from -8,388,608 to 8,388,607. With UNSIGNED the range is 0–16,777,215.

Does MEDIUMINT save space compared to INT?

Yes. MEDIUMINT uses 3 bytes per row, while INT uses 4 bytes. For large tables this can translate to significant disk and memory savings.

Can a MEDIUMINT column be AUTO_INCREMENT?

Absolutely. MEDIUMINT can serve as a primary key with AUTO_INCREMENT, provided the sequence will not exceed its numeric limit.

What happens if I insert a value outside the MEDIUMINT range?

In strict SQL mode MySQL raises ERROR 1264 and the row is not inserted. In non-strict mode MySQL clips the value to the nearest boundary and issues a warning.