Types in MySQL BigInt 20 vs Int 20

Question

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

User · Accepted Answer

See http   dev mysql com doc refman 8 0 en numeric-types html   INT is a four-byte signed integer    BIGINT is an eight-byte signed integer     They each accept no more and no fewer values than can be stored in their respective number of bytes  That means 232 values in an INT and 264 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

User · Answer

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

User · Answer

I wanted to add one more point is  if you are storing a really large number like 902054990011312 then one can easily see the difference of INT 20  and BIGINT 20   It is advisable to store in BIGINT

User · Answer

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       Then   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

User · Answer

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  - gt  2147483647  or 2   INT 32  - gt  4294967295 for UNSIGNED    Example   mysql gt  describe  test    ------- ------------------ ------ ----- --------- -------    Field   Type               Null   Key   Default   Extra    ------- ------------------ ------ ----- --------- -------    id      int 20  unsigned   YES          NULL               ------- ------------------ ------ ----- --------- -------  1 row in set  0 00 sec   mysql gt  INSERT INTO  test    id   VALUES  401421228216   ERROR 1264  22003   Out of range value for column  id  at row 1  mysql gt  SET sql mode       Query OK  0 rows affected  1 warning  0 00 sec   mysql gt  INSERT INTO  test    id   VALUES  401421228216   Query OK  1 row affected  1 warning  0 06 sec   mysql gt  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  - gt  9223372036854775807  or 2   BIGINT 64  - gt  18446744073709551615 for UNSIGNED    Example   mysql gt  describe  test    ------- --------------------- ------ ----- --------- -------    Field   Type                  Null   Key   Default   Extra    ------- --------------------- ------ ----- --------- -------    id      bigint 20  unsigned   YES          NULL               ------- --------------------- ------ ----- --------- -------  1 row in set  0 00 sec   mysql gt  INSERT INTO  test    id   VALUES  401421228216   Query OK  1 row affected  0 04 sec   mysql gt  SELECT   FROM  test    --------------    id              --------------    401421228216    --------------  1 row in set  0 00 sec

User · Answer

Quote      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

[mysql] Types in MySQL: BigInt(20) vs Int(20)

Examples related to mysql

Examples related to types