I don’t have to spell smart people, I just need to spell those lazy people I will surpass most people! Geek xiaojun @Nuggets first original article personal blog: 👉 cnblogs.com 👈

You probably already know that int(1) has a length of 1 that does not represent the allowable storage width!

But a lot of people have not really studied what this length represents, today I will give a simple analysis!

Let’s start with a simple example of creating a table:

create table test(
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  uid int(3) NOT NULL,
  PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

In this case, for the UID field, we have int(3)

So the question is: if we set int(3), we can’t store 1234?

You can test it by typing the following SQL statement

insert into `test` (`uid`) VALUES(1234);
insert into `test` (`uid`) VALUES(12345678);
Copy the code

The results are as follows:

Insert data 1234, insert data 1234, insert data 1234, insert data 1234 Why is that? Look at the following

Here’s why:

Int (n) = n (int(n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n) = n (n)

So we can create table test2 from new, and this time we will add the uid field constraints: unsigned and Zerofill

The == field constraint is covered in more detail below

MySQL > select * from MySQL where MySQL > MySQL

create table test2(
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  uid int(3) unsigned zerofill NOT NULL,
  PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Now my UID field: length (n)=3, field constraint =unsigned and zerofill standby: (unsigned constraint and fill the bit constraint with 0)

When you insert data into the table, the system automatically fills in the left side of the UID field with 0 if the uid field is less than 3 bits

You can test the code by inserting a number of 33 into the UID field

insert into `test2` (`uid`) VALUES(33);
Copy the code

The results are as follows:

Are you surprised to find that when the length is less than 3, it really fills with 0 from the left! Ha, ha, ha

So: now it should be clear that the length n after int has nothing to do with the size of the numeric type you store!

== int = int = int = int = int = int = int = int = int; Zerofill constraint and unsigned unsigned constraint must be applied to this field if 0 must be added to the left!

Today to share here!!

If you like my article please 👉 “like” “comment” “follow” 👈 a key three connect, everyone’s support is the power that I stick to!

If there are any errors or inaccuracies in any of the above, please leave a comment below 👇, or if you have a better idea, please join us