Explanation of length value problem for MySQL int type

During an interview, the interviewer asked me, "What does the length of int type represent whenMySQL is building a table ?" is this column allowed to store the maximum width of the value? ?  Why I set it up as Int (1), as well as being able to save 10,100,1000 ? .

I didn't think about it before. The results came back and looked it up. as follows, the conclusion is: just tell the database that the normal length should be 1, not to say that only the length of 1.

While I knewInt (1),this length1does not represent the width of the allowed storage,but not a reasonable explanation.. or there's no real research on this length, what does that mean?, It 's usually used .Int (one),I don't know why I have to Onebit. so I read some information on the Internet., I watched it carefully .MySQLManuals aboutint data typethe argument.

The following is the storage and scope of each integer type ( from the mysql manual )

There are four columns in the table : field type , number of bytes , minimum allowable value for storage, maximum allowable value .

Type	Bytes	Minimum value	Maximum Value
		( signed / unsigned )	( signed / unsigned )
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
Mediumint	3	-8388608	8388607
		0	16777215
Int	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

Let's take the int type as an example :

Inttype, the number of bytes consumed is4byte,students who have learned the principles of computers should know, bytes(Byte)is not the smallest unit of computer storage, there are more bytes than(Byte)smaller units, is a bit(bit),a bit represents a0or1; 8The bits are made up of one byte; General bytes in uppercaseBto indicateByte,bit in lowercasebto indicatebit.

Conversion of computer storage units :

1b=8b

1kb=1024b

1mb=1024kb

so according to int 4 bytes ,  int  UNSIGNED ( unsigned ) 0,  Max is 4294967295 ( 4b=32b,  1 composed of );

Next we'll talk about what the length of the field is when we build the table .

CREATE TABLE ' test ' (' id ' int (one) ' NOT NULL auto_increment PRIMARY KEY, ' number ' int (5) is not null) ENGINE = MYISAM;

Take the Number field of the Test table as an example , and you see I built an int (5)

mysql / Span style= "font-family: the song Body;" The value is "M" to represent the .  Careful friends should have noticed mysql There is a sentence in the manual :   m indicates the maximum display width. The maximum effective display width is 255 display width is independent of the range of values that the storage size or type contains;

,  ,  " Our first reaction is that the value of the field can be the maximum allowable width of the stored value .  > thought we built a int (1), 10 the , 

this m=5 We can simply understand to be ,  We built this length to tell the mysql database the width of the stored data for this field is 5 ,   5 ( ) MYSQL can also be stored normally ,  

Let's change the " properties " of this field to UNSIGNED zerofill Look at the effect .

We see now that my NumberField, length(M) =5,Properties=unsigned Zerofill (No sign,with0to populate the number of digits),  after setting this property, I insert the data when I go to the table,the system will automatically NumberFieldMNot enough5bit on the left side with0to populate; The effect is as follows

There's a saying in the handbook " when mysql generates temporary tables for some complex joins (joins) , you may encounter problems because in this case, MySQL trust that all values are appropriate for the original column width ".  It also makes me wonder how this width is set to be more appropriate ?

But a little bit. After reading the document you should be aware that the length M is independent of the size of the number of numeric types you store .

Explanation of length value problem for MySQL int type