Shulou(Shulou.com)06/01 Report--

This article mainly introduces the MySQL int type length value handout, hoping to supplement and update some knowledge, if you have other questions you need to know, you can continue to follow my updated article in the industry information.

What does the length after the int type represent when creating the table? Is the maximum width of the column allowed to store values? Why am I set to int (1), and I can also save 10pm 100pm 1000.

Although I knew int (1) at the time, this length 1 did not represent the width of the allowed storage, but there was no reasonable explanation. In other words, there is no real research on what this length means. We usually use int (11), and I don't know why I want 11 digits. So I looked up some information on the Internet and carefully read the mysql manual about int data type.

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

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

Let's take the int type as an example:

Int type, the number of bytes occupied is 4byte, students who have studied computer principles should know that byte is not the smallest unit of computer storage, there are smaller units than bytes (byte), that is, bits (bit), a bit represents a 0 or 1; 8 bits constitute a byte; general bytes use uppercase B to represent byte, bits use lowercase b 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 in the int type, we can convert the minimum storage value of the int UNSIGNED (unsigned) type to 0 and the maximum value to 4294967295 (that is, 4B=32b, the maximum value is 32 1s).

Next, let's talk about the length of the field when we build the table.

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

Taking the number field of the test table as an example, you can see that I built int (5).

This length / value in the mysql manual is expressed as "M". Careful friends should have noticed that there is such a sentence in the mysql manual: M indicates the maximum display width. The maximum valid display width is 255. The display width is independent of the range of values contained in the storage size or type.

This sentence does not seem easy to understand, because there is a keyword that is easy to confuse. "maximum display width" our first reaction is the maximum width of the value of the field that can be stored. I thought that if we built int (1), we couldn't store data 10, but that's not what I meant.

We can simply understand that we set up this length to tell the MYSQL database that the width of the data stored in our field is 5 digits, of course, if you are not 5 digits (as long as it is within the storage range of this type) MYSQL can also be stored normally, which can explain the above red.

Let's change the property of this field to UNSIGNED ZEROFILL to see the effect.

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

There is also a saying in the manual, "when mysql generates temporary tables for some complex join, you may encounter problems, because in this case, mysql trusts that all values are appropriate for the original column width." This also makes me wonder how to set this width in the end.

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 numeric number you store.

These are the details of the length value of the MySQL int type, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.