Difference between MySQL BigInt(20) and Int(20)?

The int type takes 4 byte signed integer i.e. 32 bits ( 2³² values can be stored). The BigInt type takes 8 byte signed integer i.e. 64 bits (2⁶⁴ values can be stored).

Let us see an example.

Creating a table with zerofill, that would add leading zeros.

mysql> create table IntandBigint20Demo
   -> (
   -> Number int(20) zerofill,
   -> Code BigInt(20) zerofill
   -> );
Query OK, 0 rows affected (0.58 sec)

After creating a table, we will insert records into the table.

mysql> insert into IntandBigint20Demo values(987,987);
Query OK, 1 row affected (0.16 sec)

Now we can display all records with the help of select statment. The query is as follows −

mysql> select *from IntandBigint20Demo;

The following is the output.

+----------------------+----------------------+
| Number               | Code                 |
+----------------------+----------------------+
| 00000000000000000987 | 00000000000000000987 |
+----------------------+----------------------+
1 row in set (0.00 sec)

Look at the sample output, in the beginning, 0 gets filled. This itself states that 20 is the width in let’s say.

Number int(20) zerofill

Arjun Thakur

Published on 30-Nov-2018 15:24:32