The length value of the Mysqlint type what does the length of the int type represent when the table is under construction?

Explanation of length value of MySQL int type

MySQL when building a table the length of the int type represents what is the maximum width that the column allows to store the value? Why do I set an int (1) to save 10,100,1000 as well?

While I knew Int (1), this length of 1 does not represent the width of the allowed storage, but it does not have a reasonable explanation. Or there is no real study of the length of what is represented in the end, usually with int (11), do not know why the 11-bit. So I looked up some information on the Internet and read the MySQL manual about the int data type.

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

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

There are four columns in a table: The field type, the number of bytes used, the minimum value allowed to be stored, and the maximum value allowed for storage.

Let's take the int type as an example:

int type, take up the number of bytes is 4byte, learn the computer principle of the classmate should know that byte (byte) is not the smallest unit of computer storage, there is a smaller than the byte (byte) unit, that is, bit (bit), a bit represents a 0 or 1; 8 bits make up one byte; The general byte is denoted by the uppercase B to byte, and the bit in lowercase b to indicate the bit.

Conversion of computer storage units:

1b=8b

1kb=1024b

1mb=1024kb

Then the number of bytes allowed to be stored according to the type of int is 4 bytes, we can convert int UNSIGNED (unsigned) type can store the minimum value is 0, the maximum value is 4294967295 (that is, 4b=32b, the maximum value is 32 1 components);

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) Not NULL
) ENGINE = MYISAM;

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

This length/value in the MySQL manual is denoted by "M". Attentive friends should have noticed that the MySQL handbook has such a sentence: M indicates the maximum display width. The maximum effective display width is 255. The display width is independent of the range of values that the storage size or type contains;

This is not easy to understand, because there is a key word to confuse us, "Maximum display width" Our first reaction is that the value of the field can be the maximum allowable value of the width of the store. Think we built an int (1), we can not store data 10, in fact, this is not the meaning.

This m=5 we can simply understand that we have built this length to tell MySQL database that the data stored in this field is 5 digits wide, and of course if you are not a 5-digit (as long as it is within the storage range of that type), MySQL can also be stored normally, which can explain the above red words.

Let's Change the "Properties" of this field to unsigned zerofill look at the effect.

We see now my Number field, Length (M) = 5, property =unsigned zerofill (unsigned, filled with zeros), after setting this property, when I insert data into the table, the system will automatically fill the Number field M less than 5 bits on the left with the number of zeros; The effect is as follows

There's a saying in the Handbook. "You may encounter problems when MySQL generates temporary tables for certain complex joins, because in this case, MySQL trusts 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.

The length value of the Mysqlint type what does the length of the int type represent when the table is under construction?