MySQL Integers: INT, BIGINT, and more
Brian Morrison II
Brian Morrison II
10/31/2022
Engineering5 min read

MySQL Integers: INT, BIGINT, and more

MySQL has a number of integer types, and while INT may seem like the right choice for most scenarios, it’s worth understanding what options you have so you can make the right choice when designing your database. In this article, we’ll take a look at the various integer types and take a deeper dive into how they are stored in MySQL.

An overview of the MySQL INT type#

An integer is simply a whole number. It can be positive, negative, or even zero. In MySQL, there are actually several different data types you can use to store integers, each with its own range of numbers. The standards INT type can store up to 4,294,967,296 values including 0, and MySQL permits negative numbers by default unless otherwise specified. Defining an INT column looks like this in a CREATE TABLE statement:

SQL
CREATE TABLE my_table (
	my_integer_col INT
);

Since MySQL defaults to allowing both negative and positive numbers, my_integer_col would be able to store whole numbers from -2,147,483,648 to 2,147,483,647. Without deeper computer science knowledge, these VERY specific numbers may appear strange. It all has to do with binary works and how the data is stored by the database engine.

MySQL INT types and the binary system#

Most of the modern world uses a base-10 number system, which means there are 10 possible values (0-9) available for a single position in a given number. Once a position reaches the maximum allowed value, the number will roll over and add another position to indicate that the value has increased.

How the base-10 number system works.

This probably feels like common sense. After all, you likely started learning this system from an early age. There are, however, different number systems. Binary is one of those number systems, and it is the one most commonly used by computers.

Binary is a base-2 number system. There are only two possible values available to a given position: 0 or 1. Regardless of the available values, the positions will still roll over once they have reached the maximum allowed value. This can make binary numbers look incredibly foreign. Notice in the image below how the positions roll over every other number for binary, as opposed to every 10 numbers in decimal.

Numbers in binary vs decimal.

So what’s all this got to do with integers in MySQL? The INT data type is a signed, 32-bit value. The “positions” are referred to as bits. This means there are 32 positions available for a 1 or 0 to be placed, with the left-most bit being used to represent if the number is positive or negative. Here is what an integer would look like to MySQL under the hood:

A 32-bit binary number compared to it’s decimal version.

Different integer data types#

There are more integer types than just INT, four more to be exact. The table below shows the available integer types, as well as their approximate ranges.

TypeLength (in bits)Minimum Value (signed)Maximum Value (signed)
BIGINT64-2 632 63 - 1
INT32-2,147,483,6482,147,483,647
MEDIUMINT24-8,388,6088,388,607
SMALLINT16-32,76832,767
TINYINT8-128127

Notice how the TINYINT range is -128 to 127. This is because 0 is included in the upper half of the range with signed integer values.

Signed vs unsigned integers#

In the section about binary, I noted that the leftmost bit is used to determine if the value is positive or negative. MySQL actually allows you to modify this behavior and include that bit in the stored value. This permits you to store much larger numbers in a given column, with the tradeoff that no negative numbers can be stored. Using the UNSIGNED keyword when creating a column, you can tell MySQL that the values should all be positive:

SQL
CREATE TABLE my_table (
	my_integer_col INT UNSIGNED
);

Below is the same table from the previous section, but updated to show the maximum value available to each integer type. The minimum value is omitted as it will always be 0:

TypeMaximum Value (unsigned)
BIGINT2 64 - 1
INT4,294,967,295
MEDIUMINT16,777,215
SMALLINT65,535
TINYINT255

A note about integer width#

Earlier in this article, I showed how you would create an INT column using the following syntax:

SQL
CREATE TABLE my_table (
	my_integer_col INT
);

There may be times you come across an integer column defined with a width as INT(5), with 5 being the width of the column. If you are familiar with other types such as VARCHAR, you may assume that this will change the allowed range or number of characters that MySQL will let you store in that column, but this is not the case with integers. When used with ZEROFILL keyword, MySQL will automatically left-pad the value with zeroes up to the defined width.

Here are some examples on how MySQL both stores and returns values based on the column definition:

Column definitionStored valueReturned value
INT123123
INT(5)123123
INT(5) ZEROFILL12300123
INT(5) ZEROFILL123456123456

As a best practice, I’d suggest avoiding using integer columns in this manner since it only affects the displayed value once the data is returned, and that kind of logic is best left up to the applciation using MySQL.

When to use the different MySQL integer types#

With integer types, it mostly comes down to the following two questions:

Do you need to store negative numbers?

If your answer is yes, then you'd want to use a signed version of any of the integer types described in this article, otherwise opt for the UNSIGNED variant as you automatically get to use higher numbers.

How large are the numbers you need to store?

Since the integer types all have a hard cap on the maximum value you can store, this will help determine which you should use when defining your schema. It’s also worth noting that larger integer types consume more disk space, so keep this in mind when considering which type to use.