Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I was wondering what the difference between BigInt , MediumInt , and Int are... it would seem obvious that they would allow for larger numbers; however, I can make an Int(20) or a BigInt(20) and that would make seem that it is not necessarily about size.

Some insight would be awesome, just kind of curious. I have been using MySQL for a while and trying to apply business needs when choosing types, but I never understood this aspect.

They each accept no more and no fewer values than can be stored in their respective number of bytes. That means 2 ³² values in an INT and 2 ⁶⁴ values in a BIGINT .

The 20 in INT(20) and BIGINT(20) means almost nothing. It's a hint for display width. It has nothing to do with storage, nor the range of values that column will accept.

Practically, it affects only the ZEROFILL option:

CREATE TABLE foo ( bar INT(20) ZEROFILL );
INSERT INTO foo (bar) VALUES (1234);
SELECT bar from foo;
+----------------------+
| bar                  |
+----------------------+
| 00000000000000001234 |
+----------------------+
It's a common source of confusion for MySQL users to see INT(20) and assume it's a size limit, something analogous to CHAR(20). This is not the case.
                Wow, this post cleared up my confusion on this subject perfectly. Seems like an odd choice by the developers - as I would have guessed it was width + max value, or bits/etc.
– Sh4d0wsPlyr
                May 25, 2015 at 23:51
                I really wish that they had designed the syntax with the display with on ZEROFILL instead of INT.  Example: bar INT ZEROFILL(20). It would have been a lot more clear. But that decision was made a long time ago, and changing it now would break millions of database installations.
– Bill Karwin
                Jul 5, 2016 at 18:38
                I agree this is very confusing. I was under the impression that number was the limit this whole time. Even worried about making some numbers smaller when I knew the data would be within a certain range.
– wheeleruniverse
                Apr 23, 2018 at 16:16
                @jDub9, yes, and it's even more confusing that NUMERIC/DECIMAL take a precision argument that does affect the range of values and the size of the column.
– Bill Karwin
                Apr 23, 2018 at 17:30
The number in parentheses in a type declaration is display width, which is unrelated to the range of values that can be stored in a data type. Just because you can declare Int(20) does not mean you can store values up to 10^20 in it:
[...] This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. ...
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.
For a list of the maximum and minimum values that can be stored in each MySQL datatype, see here.
  
The "BIGINT(20)" specification isn't a digit limit.  It just means that when the data is displayed, if it uses less than 20 digits it will be left-padded with zeros. 2^64 is the hard limit for the BIGINT type, and has 20 digits itself, hence BIGINT(20) just means everything less than 10^20 will be left-padded with spaces on display.
                2^64 (unsigned) actually has 21 digits.  BIGINT(20) is dangerous.  People who use it seem to justify their usage on the idea that 2^64 fits in 20 decimal digits.  If that's the case, why specify a width limit at all?  As it turns out, that's also not correct.  21 digits are needed to properly display 2^64.
– Heath Hunnicutt
                May 17, 2016 at 22:21
                @HeathHunnicutt Unless I'm mistaken, 2^64 = 18446744073709551616, which has 20 digits. What makes you say it has 21?
– drmercer
                Jan 13, 2020 at 23:24
As far as I know, there is only one small difference is when you are trying to insert value which is out of range.
  
In examples I'll use 401421228216, which is 101110101110110100100011101100010111000 (length 39 characters)
If you have INT(20) for system this means allocate in memory minimum 20 bits. But if you'll insert value that bigger than 2^20, it will be stored successfully, only if it's less then INT(32) -> 2147483647 (or 2 * INT(32) -> 4294967295 for UNSIGNED)
Example:
mysql> describe `test`;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(20) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0,00 sec)
mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
Query OK, 1 row affected, 1 warning (0,06 sec)
mysql> SELECT * FROM `test`;
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
1 row in set (0,00 sec)
If you have BIGINT(20) for system this means allocate in memory minimum 20 bits. But if you'll insert value that bigger than 2^20, it will be stored successfully, if it's less then BIGINT(64) -> 9223372036854775807 (or 2 * BIGINT(64) -> 18446744073709551615 for UNSIGNED)
Example:
mysql> describe `test`;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0,00 sec)
mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
Query OK, 1 row affected (0,04 sec)
mysql> SELECT * FROM `test`;
+--------------+
| id           |
+--------------+
| 401421228216 |
+--------------+
1 row in set (0,00 sec)
Let's give an example for int(10) one with zerofill keyword, one not, the table likes that:
create table tb_test_int_type(
    int_10 int(10),
    int_10_with_zf int(10) zerofill,
    unit int unsigned
Let's insert some data:
insert into tb_test_int_type(int_10, int_10_with_zf, unit)
values (123456, 123456,3147483647), (123456, 4294967291,3147483647) 
select * from tb_test_int_type; 
# int_10, int_10_with_zf, unit
'123456', '0000123456', '3147483647'
'123456', '4294967291', '3147483647'
We can see that 
with keyword zerofill, num less than 10 will fill 0, but without zerofill it won't
Secondly with keyword zerofill, int_10_with_zf becomes unsigned int type, if you insert a minus you will get error Out of range value for column...... But you can insert minus to int_10. Also if you insert 4294967291 to int_10 you will get error Out of range value for column.....
Conclusion:
int(X) without keyword zerofill, is equal to int range -2147483648~2147483647
int(X) with keyword zerofill, the field is equal to unsigned int range 0~4294967295, if num's length is less than X it will fill 0 to the left
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.