Introduction to MySQL MEDIUMINT Data Type

MEDIUMINT is a type of integer in MySQL. It occupies 3 bytes of storage and can store signed integers ranging from -8388608 to 8388607. Using MEDIUMINT type can save space compared to INT type and it is also faster. It is typically used in integer fields where space is a concern.

Syntax

The syntax for MEDIUMINT is as follows:

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

where M represents the display width, with a maximum value of 9. If M is not specified, the default value is 8.

If the UNSIGNED option is added, the field will only store non-negative numbers. If the ZEROFILL option is used, MySQL will pad the field with zeros when the display width is not met.

Use Cases

The use cases for MEDIUMINT type typically involve integer fields that need to save space, such as:

• Storing dates and timestamps
• Storing large numbers of integers in a table
• Using as an index key

Examples

Here are two examples of using MEDIUMINT type.

Example 1

Suppose there is a table users that stores user information, and we need to store the age of users. We can use MEDIUMINT type for storing the age. The table creation statement would be as follows:

CREATE TABLE users (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age MEDIUMINT UNSIGNED
);

Here is an example of inserting some data into the users table:

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25);
INSERT INTO users (name, age) VALUES ('Charlie', 40);

Then, we can use the following query to retrieve data from the table:

SELECT * FROM users;

The output will be as follows:

+----+---------+-----+
| id | name    | age |
+----+---------+-----+
| 1  | Alice   | 30  |
| 2  | Bob     | 25  |
| 3  | Charlie | 40  |
+----+---------+-----+

Example 2

Another example is storing a large number of integers in a table. Suppose we need to store a student table with a large number of student IDs, we can use MEDIUMINT type for storing the student IDs. The table creation statement would be as follows:

CREATE TABLE students (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    student_id MEDIUMINT UNSIGNED
);

The above SQL statement creates a table named students, where the id column is an auto-incrementing primary key of MEDIUMINT UNSIGNED type, the name column is of VARCHAR(50) type, and the student_id column is of MEDIUMINT UNSIGNED type. The UNSIGNED is used to ensure that the stored values are positive integers, as student IDs are typically positive integers.

Here is an example of inserting some data into the students table:

INSERT INTO students (name, student_id) VALUES ('Alice', 1001);
INSERT INTO students (name, student_id) VALUES ('Bob', 1002);
INSERT INTO students (name, student_id) VALUES ('Charlie', 1003);

These data have been inserted into the ‘students’ table, with each row containing a student’s name and student ID. In this example, we used the ‘INSERT INTO’ statement to insert data into the table.

Conclusion

‘MEDIUMINT’ data type is a type of data type suitable for storing integer values. It uses less storage space compared to ‘INT’ type but more storage space compared to ‘SMALLINT’ type. If you need to store a large amount of integer data, you may consider using the ‘MEDIUMINT’ data type.