Detailed explanation of the length value problem of MySQL int type

mysql What does the length after the int type represent when creating a table? Is the maximum width the column is allowed to store values? Why can I set it to int (1) and still be able to store 10,100,1000?

At that time, although I knew that the length of int (1) did not represent the allowed storage width, I didn’t have a reasonable explain. In other words, I haven’t really studied what this length represents. I usually use int (11), and I don’t know why it needs 11 bits. So I checked some information on the Internet and carefully read what the mysql manual said about int data type.

Recommendation: "mysql tutorial"

The following are each The storage and range of an integer type (from the mysql manual)

The table has four columns: field type, number of bytes occupied, minimum value allowed to be stored, The maximum value allowed to be stored.

Let’s take the int type as an example:

int type occupies 4 bytes. Students who have studied computer principles should know that byte (byte) is not It is the smallest unit of computer storage. There is also a smaller unit than a byte, which is a bit. A bit represents a 0 or 1; 8 bits make up a byte; generally a byte is represented by a capital B. to represent byte, and bit to represent bit.

Conversion of computer storage units:

1B=8b

1KB=1024B

1MB= 1024KB

Then according to the number of bytes allowed to be stored by the int type, we can calculate that the minimum value that can be stored by the int UNSIGNED (unsigned) type is 0, and the maximum value is 4294967295 (i.e. 4B=32b, the maximum value is composed of 32 ones);

Next let’s talk about the field length when we build the table.

CREATE TABLE test (
id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
number INT( 5 ) NOT NULL
) ENGINE = MYISAM ;

The test table is Take the number field as an example. You can see that what I built is int (5)

. In the mysql manual, this length/value is represented by "M". Careful friends should have noticed this sentence in the mysql manual: M indicates the maximum display width. The maximum effective display width is 255. The display width has nothing to do with the storage size or the range of values ​​contained in the type;

This sentence seems not easy to understand, because there is a keyword here that easily confuses us, "maximum display width" Our first reaction It is the width of the maximum value that can be stored in this field. We thought that if we built int (1), we could not store the data 10. This is not the case.

We can simply understand this M=5. We created this length to tell the MYSQL database about our field. The width of the stored data is 5 digits. Of course, if it is not 5 digits (as long as it is within the storage range of this type), MYSQL can also store it normally. This can also explain the words marked in red above.

We change the "attribute" of this field to UNSIGNED ZEROFILL and see the effect.

We see that my number field now has length (M)=5, attribute= UNSIGNED ZEROFILL (unsigned, use 0 to fill the number of digits), After setting this attribute, when I insert data into the table, the system will automatically fill the number field M with less than 5 digits with 0 on the left side; the effect is as follows

There is also a sentence in the manual: "When mysql generates temporary tables for some complex joins, you may encounter problems, because in this case, mysql Trust that all values ​​are suitable for the original column width". This also makes me wonder how to set this width appropriately?

But one thing you should know clearly after reading this document is that the length M has nothing to do with the size of the numerical number you store.

The above is the detailed content of Detailed explanation of the length value problem of MySQL int type. For more information, please follow other related articles on the PHP Chinese website!