Is there a difference between using INT(1) and TINYINT(1) in MySQL?-Mysql Tutorial-php.cn

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

PHPz

Release： 2023-08-30 08:13:08

forward

875 people have browsed it

在 MySQL 中使用 INT(1) 与 TINYINT(1) 有区别吗？

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
   &minus;> (
   &minus;> FirstNumber int(1) zerofill,
   &minus;> SecondNumber tinyint(1) zerofill
   &minus;> );
Query OK, 0 rows affected (0.52 sec)

Copy after login

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)

Copy after login

Use the select statement to display all records in the table. The query is as follows -

mysql> select *from intAndTinyint;

Copy after login

The following is the output -

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

Copy after login

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
   &minus;> (
   &minus;> Number1 int(11) unsigned zerofill,
   &minus;> Number int(13) unsigned zerofill
   &minus;> );
Query OK, 0 rows affected (1.17 sec)

Copy after login

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)

Copy after login

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

mysql> select *from intVsIntAnyThingDemo;

Copy after login

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)

Copy after login

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!