What are the differences between int(1) and int(10) in mysql

Confusion

I recently encountered a problem. If I have a table, I need to add a user_id field. The user_id field may be very large, so I submitted a mysql work order alter table xxx ADD user_id int(1). After seeing my SQL work order, the leader said: I'm afraid the int(1) you set may not be enough, and then started to explain.

This is not the first time I have encountered this problem. Some people who have encountered this problem have been doing this work for more than 5 years. Including that I often see my colleagues using int(10) all the time. I feel that if int(1) is used, the upper limit of the field is limited. This is definitely not the case.

Data Talk

We know that int occupies 4 bytes in mysql, so for unsigned int, the maximum value is 2^32-1 = 4294967295, which is nearly 4 billion. Is it possible to use If int(1) is used, can't this maximum value be reached?

CREATE TABLE `user` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

The id field is an unsigned int(1), let me insert a maximum value to see.

mysql> INSERT INTO `user` (`id`) VALUES (4294967295);
Query OK, 1 row affected (0.00 sec)

You can see that it is successful, which means that the number after int does not affect the size supported by int itself. There is no difference between int(1), int(2)...int(10).

Zero filling

Generally, the number after int is only effective when used with zerofill. Let’s look at an example first:

CREATE TABLE `user` (
  `id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Note that a zerofill is added after int(4). Let’s insert 4 pieces of data first.

mysql> INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Insert 4 pieces of data, 1, 10, 100, and 1000 respectively, and then we query:

mysql> select * from user;
+------+
| id   |
+------+
| 0001 |
| 0010 |
| 0100 |
| 1000 |
+------+
4 rows in set (0.00 sec)

Through the data, we can find that int(4) zerofill realizes filling 0 for less than 4 digits. Phenomenon, int(4) alone is useless.
And for 0001, the underlying storage is still 1, but the displayed one will be filled with 0.

The above is the detailed content of What are the differences between int(1) and int(10) in mysql. For more information, please follow other related articles on the PHP Chinese website!