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
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.
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.
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.
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:
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.
|Type||Length (in bits)||Minimum Value (signed)||Maximum Value (signed)|
|BIGINT||64||-2 63||2 63 - 1|
Notice how the
TINYINT range is
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:
|Type||Maximum Value (unsigned)|
|BIGINT||2 64 - 1|
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
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 definition||Stored value||Returned value|
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.
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: