Shulou(Shulou.com)06/01 Report--

This article is about the differences between int (1) and int (10) in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Confused

Recently encountered a problem, there is a table to add a user_id field, the user_id field may be very large, so I mentioned mysql work order alter table xxx ADD user_id int (1). The leader saw my sql work order and said: I'm afraid this int (1) is not enough, and then there's an explanation.

In fact, this is not the first time I have encountered such a problem, among which there are veteran drivers who have worked for more than 5 years. Including I often see colleagues have been using int (10), feel that the use of int (1), the upper limit of the field is limited, the real situation is certainly 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, nearly 4 billion. Can't you reach this maximum value with int (1)?

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 unsigned int (1). Let me insert a maximum value.

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

You can see the success, indicating that the numbers after int do not affect the size supported by int itself, and there is no difference between int (1) and int (2). Int (10).

Zero filling

Generally speaking, the numbers after int are only valid when used with zerofill. Let's look at an example:

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

Notice that int (4) is followed by a zerofill, so let's insert four pieces of data first.

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

Insert 1,10,100,100 and 1000 pieces of data respectively, and then let's query:

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

Through the data, we can find that int (4) + zerofill realizes the phenomenon of less than 4-bit complement 0, and int (4) alone is useless.

And for 0001, the underlying storage is still 1, only in the display will make up 0.

Thank you for reading! This is the end of the article on "what are the differences between int (1) and int (10) in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.