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

The number 1 used in brackets is for width display only. INT(1) and TINYINT(1) do not affect storage.

TINYINT occupies 1 byte, which means its range is -128 to 127, while int occupies 4 bytes; its range is -2147483648 to 2147483647

To understand the width Show, let's 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)

Use the select statement to display all records in the table. 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 the number 1 of the bracket increases to greater than 1 by padding with zeros. Let's look at an example just for INT to understand the concept of width zero padding.

Create a table. Following is the query to create the 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 records in the table with the help of insert command. Here, we set different widths 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 showing different widths and zero padding

+-------------+---------------+
| Number1     | Number        |
+-------------+---------------+
| 00000012345 | 0000000006789 |
| 00000000003 | 0000000000002 |
| 00000000012 | 0000000000089 |
| 00000000123 | 0000000006789 |
| 00000001234 | 0000000006789 |
+-------------+---------------+
5 rows in set (0.00 sec)

The above is the detailed content of Is there a difference between using INT(1) and TINYINT(1) in MySQL?. For more information, please follow other related articles on the PHP Chinese website!