What is MEDIUMINT in MySQL: Definition and Usage

MEDIUMINT in MySQL is a numeric data type used to store medium-sized integers. It uses 3 bytes of storage and can hold values from -8,388,608 to 8,388,607 for signed, or 0 to 16,777,215 for unsigned integers.

How It Works

Think of MEDIUMINT as a container that holds numbers using 3 bytes of space. This is like having a medium-sized box compared to smaller or bigger boxes for numbers. It fits numbers bigger than SMALLINT but smaller than INT.

Because it uses 3 bytes, it can store a range of numbers from -8,388,608 to 8,388,607 if signed, or from 0 to 16,777,215 if unsigned. This helps save space when you know your numbers won't be very large but need more room than smaller types.

Example

This example shows how to create a table with a MEDIUMINT column and insert values within its range.

sql

CREATE TABLE example_mediumint (
  id MEDIUMINT NOT NULL,
  description VARCHAR(50)
);

INSERT INTO example_mediumint (id, description) VALUES
(8388607, 'Max signed MEDIUMINT'),
(0, 'Zero value'),
(16777215, 'Max unsigned MEDIUMINT');

SELECT * FROM example_mediumint;

Output

id       | description
---------|-----------------------
8388607  | Max signed MEDIUMINT
0        | Zero value
16777215 | Max unsigned MEDIUMINT

When to Use

Use MEDIUMINT when you need to store integer numbers that are larger than what SMALLINT can hold but smaller than the full range of INT. This helps save storage space and can improve performance.

For example, if you are storing user IDs, product quantities, or counts that you know will not exceed 16,777,215, MEDIUMINT is a good choice. It balances storage efficiency and range well.

Key Points

• Storage: Uses 3 bytes per value.
• Range: Signed: -8,388,608 to 8,388,607; Unsigned: 0 to 16,777,215.
• Use case: Medium-sized integer values to save space.
• Comparison: Larger than SMALLINT, smaller than INT.

Key Takeaways

MEDIUMINT stores medium-sized integers using 3 bytes in MySQL.

It supports signed values from -8,388,608 to 8,388,607 and unsigned up to 16,777,215.

Use MEDIUMINT to save space when your numbers fit within its range.

It is a good middle ground between SMALLINT and INT for integer storage.