MySQL 8: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT data types - explained with examples - Sling Academy (2024)

Introduction

MySQL is a widely used open-source relational database management system (RDBMS), and it supports a variety of data types to store numerical values of different ranges. In this tutorial, we will explore the integer data types offered in MySQL 8: INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT. Understanding these data types is essential for designing database schemas that are both efficient and appropriate for the data they will store.

Integer Data Types Overview

MySQL provides several data types for storing integers, which are whole numbers without fractional components. The choice of data type depends on the range of values you need to store, as each type has a different storage size and range of allowable values. Let’s take a quick look at these data types:

• TINYINT: A very small integer with a range from -128 to 127 (signed) or 0 to 255 (unsigned).
• SMALLINT: A small integer with a range of -32768 to 32767 (signed) or 0 to 65535 (unsigned).
• MEDIUMINT: A medium-sized integer with a range from -8388608 to 8388607 (signed) or 0 to 16777215 (unsigned).
• INT or INTEGER: A standard-size integer with a range of -2147483648 to 2147483647 (signed) or 0 to 4294967295 (unsigned).
• BIGINT: A large integer with a range of -9223372036854775808 to 9223372036854775807 (signed) or 0 to 18446744073709551615 (unsigned).

Now that we have a general understanding of the integer data types, let’s delve into each one with examples.

TINYINT Data Type

TINYINT is suitable for storing small data ranges such as age, number of items in a limited collection, or similar use cases. Here is an example of how to use TINYINT:

CREATE TABLE example_tinyint ( id INT AUTO_INCREMENT PRIMARY KEY, rating TINYINT NOT NULL);INSERT INTO example_tinyint (rating) VALUES (10), (127), (-128);SELECT * FROM example_tinyint;

This will produce the following output:

+----+--------+| id | rating |+----+--------+| 1 | 10 || 2 | 127 || 3 | -128 |+----+--------+

SMALLINT Data Type

SMALLINT is useful when you need a wider range than TINYINT but not as large as INT. It’s commonly used for quantities where the maximum number is known not to exceed the limit, such as the number of participants in an event divided into different sessions. Here’s an example:

CREATE TABLE example_smallint ( id INT AUTO_INCREMENT PRIMARY KEY, participants SMALLINT NOT NULL);INSERT INTO example_smallint (participants) VALUES (3000), (32767), (-32768);SELECT * FROM example_smallint;

Output:

+----+--------------+| id | participants |+----+--------------+| 1 | 3000 || 2 | 32767 || 3 | -32768 |+----+--------------+

MEDIUMINT Data Type

MEDIUMINT offers a good compromise between storage size and range and is ideal for larger quantities that do not require the full INT range. For instance, you can use it to store the population of cities or the number of views of a very popular video. Example:

CREATE TABLE example_mediumint ( id INT AUTO_INCREMENT PRIMARY KEY, views MEDIUMINT NOT NULL);INSERT INTO example_mediumint (views) VALUES (150000), (8388607), (-8388608);SELECT * FROM example_mediumint;

Output:

+----+---------+| id | views |+----+---------+| 1 | 150000 || 2 | 8388607 || 3 | -8388608|+----+---------+

INT or INTEGER Data Type

The INT data type is the most commonly used integer type and is the default choice for many applications. It’s used for identifiers, counters, and other use cases where a large range might be necessary. Here’s how to use INT:

CREATE TABLE example_int ( id INT AUTO_INCREMENT PRIMARY KEY, total_orders INT NOT NULL);INSERT INTO example_int (total_orders) VALUES (1000), (2147483647), (-2147483648);SELECT * FROM example_int;

Output:

+----+-------------+| id | total_orders|+----+-------------+| 1 | 1000 || 2 | 2147483647 || 3 | -2147483648 |+----+-------------+

BIGINT Data Type

BIGINT is used for very large integers and is often seen in databases requiring a vast numerical range, such as for high-volume financial applications or scientific computations. Here’s an example at play:

CREATE TABLE example_bigint ( id INT AUTO_INCREMENT PRIMARY KEY, stars_count BIGINT NOT NULL);INSERT INTO example_bigint (stars_count) VALUES (9000000000), (9223372036854775807), (-9223372036854775808);SELECT * FROM example_bigint;

Output:

+----+---------------------+| id | stars_count |+----+---------------------+| 1 | 9000000000 || 2 | 9223372036854775807 || 3 | -9223372036854775808|+----+---------------------+

Working with Unsigned Integers

Each integer type can be set to unsigned, which means they can only store positive numbers and zero, effectively doubling their maximum value. When declaring an unsigned column, you would do so as follows:

CREATE TABLE example_unsigned_int ( id INT AUTO_INCREMENT PRIMARY KEY, positive_smallint SMALLINT UNSIGNED NOT NULL, positive_bigint BIGINT UNSIGNED NOT NULL);INSERT INTO example_unsigned_int (positive_smallint, positive_bigint) VALUES (65535, 18446744073709551615);SELECT * FROM example_unsigned_int;

Output

+----+-------------------+--------------------+| id | positive_smallint | positive_bigint |+----+-------------------+--------------------+| 1 | 65535 | 18446744073709551615|+----+-------------------+--------------------+

Choosing the Right Integer Type

It’s crucial to select the most appropriate integer type to optimize storage space and performance. If a TINYINT is sufficient for your needs, there’s no reason to consume more space with an INT. Proper indexing and choice of data types can have a substantial impact on database performance, especially with large datasets. Use exact-size types when you know the range of your values beforehand and use INT as a default when you are unsure.

Conclusion

To sum up, integer data types in MySQL 8 are powerful tools in a database designer’s toolkit. Understanding the differences between INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT will enable you to make better choices for your database schema and greatly affect how efficiently you store and access numerical data.