Is there a difference in using INT(1) vs TINYINT(1) in MySQL?

MySQLMySQLi Database

The number 1 used in parenthesis is only for width display. The INT(1) and TINYINT(1) does not influence the storage.

The TINYINT takes 1 byte that means it has range -128 to +127 while int takes 4 bytes; it has range -2147483648 to +2147483647

To understand the width display, let us create a table −

mysql> create table intAndTinyint
   −> (
   −> FirstNumber int(1) zerofill,
   −> SecondNumber tinyint(1) zerofill
   −> );
Query OK, 0 rows affected (0.52 sec)

Now you can insert records in the table. The query is as follows −

mysql> insert into intAndTinyint values(1,1);
Query OK, 1 row affected (0.32 sec)

mysql> insert into intAndTinyint values(12,12);
Query OK, 1 row affected (0.26 sec)

mysql> insert into intAndTinyint values(123,123);
Query OK, 1 row affected (0.14 sec)

Display all records from the table with select statement. The query is as follows −

mysql> select *from intAndTinyint;

The following is the output −

+-------------+--------------+
| FirstNumber | SecondNumber |
+-------------+--------------+
|           1 |            1 |
|          12 |           12 |
|         123 |          123 |
+-------------+--------------+
3 rows in set (0.00 sec)

You will understand this when number 1 of parenthesis will be increased to more than 1 with zerofill. Let us see an example only for INT to understand the concept of zerofill for width.

Create a table. The following is the query to create a table −

mysql> create table intVsIntAnyThingDemo
   −> (
   −> Number1 int(11) unsigned zerofill,
   −> Number int(13) unsigned zerofill
   −> );
Query OK, 0 rows affected (1.17 sec)

Now you can insert record in the table with the help of insert command. Here, we have set different width for INT. The query is as follows −

mysql> insert into intVsIntAnyThingDemo values(12345,6789);
Query OK, 1 row affected (0.44 sec)

mysql> insert into intVsIntAnyThingDemo values(3,2);
Query OK, 1 row affected (0.20 sec)

mysql> insert into intVsIntAnyThingDemo values(12,89);
Query OK, 1 row affected (0.15 sec)

mysql> insert into intVsIntAnyThingDemo values(123,6789);
Query OK, 1 row affected (0.17 sec)

mysql> insert into intVsIntAnyThingDemo values(1234,6789);
Query OK, 1 row affected (0.14 sec)

Display all records with the help of select statement. The query is as follows −

mysql> select *from intVsIntAnyThingDemo;

The following is the output displaying different widths and zerofill

+-------------+---------------+
| Number1     | Number        |
+-------------+---------------+
| 00000012345 | 0000000006789 |
| 00000000003 | 0000000000002 |
| 00000000012 | 0000000000089 |
| 00000000123 | 0000000006789 |
| 00000001234 | 0000000006789 |
+-------------+---------------+
5 rows in set (0.00 sec)
raja
Published on 11-Jan-2019 14:51:46
Advertisements