This is the third day of my participation in the August More text Challenge. For details, see: August More Text Challenge

confused

Alter table XXX ADD user_id int(1) alter table XXX ADD user_id int(1) alter table XXX ADD user_id int(1) The leader saw my SQL work order, so said: this int(1) afraid is not enough, the next is a one to explain. In fact, this is not the first time I have encountered such a problem, some of them have more than five years of working drivers. I often see my colleagues use int(10) all the time. I feel that if I use int(1), the upper limit of the field will be limited, which is definitely not the case.

Data to talk

Int (1) = 2^32-1 = 4294967295 (1) = 4294967295 (2^32-1 = 4294967295)

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

The id field is an unsigned int(1). Let me insert a maximum value.

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

Int (1), int(2), int(1), int(2)… Int of 10 doesn’t make any difference.

Zero padding

Generally, the number following an int is only valid when used with a Zerofill. Here’s 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;
Copy the code

Note that int(4) is followed by zerofill, so let’s insert four 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
Copy the code

Insert 4 pieces of data (1, 10, 100, 1000), and then let’s query:

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

Int (4) + Zerofill implements the phenomenon of less than 4 bits complement 0. Int (4) alone is useless. And in the case of 0001, the underlying storage is still 1, only the display will add 0.

conclusion

Int (num) + zerofill The role of Zerofill can be used in some number related numbers, such as the student number 001 002… If mysql does not have zero padding, but you want to format the output number of the same length, then you have to do it yourself.