Friday, 2 November 2018

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

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

 Answers


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:
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:
  • tinyint1 byte, -128 to +127 / 0 to 255 (unsigned)
  • smallint2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned)
  • mediumint3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned)
  • int/integer4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned)
  • bigint8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)
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 unsignedand has a value range of 0 to 255



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



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

0 comments:

Post a Comment