SQL MIDDLEINT

What does the SQL MIDDLEINT data type do?

MIDDLEINT is a MySQL-specific integer data type that stores whole numbers in 3 bytes, ranging from ‑8,388,608 to 8,388,607 (or 0 to 16,777,215 when UNSIGNED).

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 MIDDLEINT: Supported: MySQL, MariaDB (as synonym for MEDIUMINT). Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake.

SQL MIDDLEINT Full Explanation

MIDDLEINT (often written MEDIUMINT) is a non-standard, MySQL-specific numeric type that sits between SMALLINT and INT in storage size. It occupies 3 bytes per value and is ideal when SMALLINT’s range is too small but INT’s 4-byte footprint is wasteful. Signed MIDDLEINT supports values from ‑8,388,608 to 8,388,607, while UNSIGNED extends the positive range to 16,777,215. Optional ZEROFILL pads displayed values with leading zeros.Although accepted by MySQL, MIDDLEINT is simply a synonym for MEDIUMINT; other database engines do not recognize it. Mixing it with cross-database code can therefore cause portability issues. Display width (deprecated since MySQL 8.0.17) never affects storage, only the default formatting in certain clients. Always choose MIDDLEINT only when your numeric domain safely fits inside its bounds.

SQL MIDDLEINT Syntax

column_name MIDDLEINT[(display_width)] [UNSIGNED] [ZEROFILL]

SQL MIDDLEINT Parameters

display_width (Integer) - (Deprecated) Number of digits MySQL should display
UNSIGNED (Keyword) - Stores only non-negative numbers, doubling upper range
ZEROFILL (Keyword) - Left-pads displayed numbers with zeros to the display_width

Example Queries Using SQL MIDDLEINT

CREATE TABLE orders (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  status_code  MIDDLEINT UNSIGNED,
  qty          MIDDLEINT ZEROFILL
);

-- Insert within signed range
INSERT INTO orders (status_code, qty)
VALUES (200, 42);

-- Attempting to exceed range triggers an error
INSERT INTO orders (status_code, qty)
VALUES (17000000, 5);

Expected Output Using SQL MIDDLEINT

The table is created with 3-byte columns for status_code and qty
The first INSERT succeeds
The second INSERT fails with "Out of range value for column" because 17,000,000 exceeds the UNSIGNED limit (16,777,215)

Use Cases with SQL MIDDLEINT

Storing HTTP status codes, limited counters, or enum-like numbers that never exceed 16 million.
Reducing disk usage compared with INT when billions of rows are expected.
Optimizing in-memory tables where every byte matters.

Common Mistakes with SQL MIDDLEINT

Assuming MIDDLEINT exists in PostgreSQL, SQL Server, or Oracle.
Forgetting the smaller signed limit (8,388,607) when UNSIGNED is omitted.
Expecting display_width to constrain input values (it does not).

First Introduced In

MySQL 3.23 (as MEDIUMINT; MIDDLEINT accepted as alias)

Frequently Asked Questions

What is the difference between MIDDLEINT and MEDIUMINT?

They are identical in MySQL; MIDDLEINT is simply an accepted alias for MEDIUMINT.

Can I use MIDDLEINT in PostgreSQL?

No. PostgreSQL does not recognize MIDDLEINT or MEDIUMINT. Use SMALLINT or INTEGER instead.

How much storage does MIDDLEINT consume?

Exactly 3 bytes per row, regardless of display_width, UNSIGNED, or ZEROFILL options.

Is MIDDLEINT affected by MySQL’s strict SQL mode?

Yes. In strict mode, inserting a value outside the valid range raises an error instead of truncating the value.