What is the difference between tinyint smallint mediumint bigint and int in MySQL

Question

What is the difference between tinyint  smallint  mediumint  bigint and int in MySQL   In what cases should these be used

User · Answer

When it gets to real world usage of these datatypes  it is very important that you understand that using certain integer types could just be an overkill or under used  For example  using integer datatype for employeeCount in a table say employee could be an overkill since it supports a range of integer values from   negative 2 billion to positive 2 billion or zero to approximately 4 billion  unsigned   So  even if you consider one of the US biggest employer such as Walmart with roughly about 2 2 million employees using an integer datatype for the employeeCount column would be unnecessary  In such a case you use mediumint  that supports from 0 to 16 million  unsigned   for example  Having said that if your range is expected to be unusually large you might consider bigint which as you can see from Daniel s notes supports a range larger than I care to decipher

User · Answer

The difference is the amount of memory allocated to each integer  and how large a number they each can store

User · Answer

The size of storage required and how big the numbers can be  On SQL Server   tinyint  1 byte  0 to 255 smallint 2 bytes  -215  -32 768  to 215-1  32 767  int 4 bytes  -231  -2 147 483 648  to 231-1  2 147 483 647  bigint 8 bytes  -263  -9 223 372 036 854 775 808  to 263-1  9 223 372 036 854 775 807   You can store the number 1 in all 4  but a bigint will use 8 bytes  while a tinyint will use 1 byte

User · Answer

Data type   Range   Storage  bigint  -2 63  -9 223 372 036 854 775 808  to 2 63-1  9 223 372 036 854 775 807     8 Bytes int -2 31  -2 147 483 648  to 2 31-1  2 147 483 647     4 Bytes smallint    -2 15  -32 768  to 2 15-1  32 767   2 Bytes tinyint 0 to 255    1 Byte   Example  The following example creates a table using the bigint  int  smallint  and tinyint data types  Values are inserted into each column and returned in the SELECT statement   CREATE TABLE dbo MyTable     MyBigIntColumn bigint   MyIntColumn  int   MySmallIntColumn smallint   MyTinyIntColumn tinyint     GO  INSERT INTO dbo MyTable VALUES  9223372036854775807  214483647 32767 255    GO SELECT MyBigIntColumn  MyIntColumn  MySmallIntColumn  MyTinyIntColumn FROM dbo MyTable

User · Answer

They take up different amounts of space and they have different ranges of acceptable values   Here are the sizes and ranges of values for SQL Server  other RDBMSes have similar documentation    MySQL Postgres Oracle  they just have a NUMBER datatype really  DB2   Turns out they all use the same specification  with a few minor exceptions noted below  but support various combinations of those types  Oracle not included because it has just a NUMBER datatype  see the above link                   SQL Server    MySQL   Postgres    DB2 --------------------------------------------------- tinyint            X           X                 smallint           X           X         X        X mediumint                      X int integer        X           X         X        X  bigint             X           X         X        X   And they support the same value ranges  with one exception below  and all have the same storage requirements                 Bytes    Range  signed                                Range  unsigned  -------------------------------------------------------------------------------------------- tinyint       1 byte   -128 to 127                                  0 to 255 smallint      2 bytes  -32768 to 32767                              0 to 65535 mediumint     3 bytes  -8388608 to 8388607                          0 to 16777215 int integer   4 bytes  -2147483648 to 2147483647                    0 to 4294967295 bigint        8 bytes  -9223372036854775808 to 9223372036854775807  0 to 18446744073709551615    The  unsigned  types are only available in MySQL  and the rest just use the signed ranges  with one notable exception  tinyint in SQL Server is unsigned and has a value range of 0 to 255

User · Answer

Those seem to be MySQL data types  According to the documentation they take   tinyint   1 byte smallint   2 bytes mediumint   3 bytes int   4 bytes bigint   8 bytes  And  naturally  accept increasingly larger ranges of numbers

[mysql] What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

Examples related to mysql

Examples related to integer