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:

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.

* Position roll-over

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.

* Position roll-over

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:

32-bit binary:     00000000000000000000010001110101
Decimal:           1141

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.

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:

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:

A note about integer width

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

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:

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 application 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.

Further learning

If you'd like to learn more about data types in MySQL, we have an article on the JSON data type and one on the VARCHAR data type that you may find useful.

We also have short videos on the following data types:

• Integers
• Decimals
• Strings
• Binary Strings
• Long Strings
• Enums
• Dates
• JSON