Explanation of length value problem for MySQL int type
Chase _something My friend seaside ask me MySQL what is the length of int type when building a table? Is this column allowed to store the maximum width of a value? Why I set it to int (1), also can save 10,100,1000.
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. We take the int type as an example: int type, takes up the byte number is 4byte, the classmate who learns the computer principle should know, the byte (byte) is not the smallest unit that the computer stores, There is a smaller unit than byte (byte), which is the bit (bit), and a bit represents a 0 or 1; 8 bits make up one byte; The general byte represents byte in uppercase B, and bits in lowercase b to indicate bit. Conversion of computer storage units: 1B=8B1KB=1024B1MB=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 for example, 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 sentence doesn't seem easy to understand, because there's a key word that's easy to confuse, "Maximum display width" Our first response is that the value of the field can be the maximum allowable value for the width of the store. Think we built an int (1), we can not store data 10, in fact, this is not the meaning. "Focus:" This m=5 we can simply understand to become, we build this length is to tell the MySQL database we store this field the width of the data is 5 digits, of course if you are not 5 digits ( As long as the storage range of this type of MySQL can also be stored normally, this 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
The manual also has the phrase "when MySQLyou may encounter problems when generating temporary tables for some complex joins (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 up properly?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.
About the number (forward) after int in the INT (1) in MySQL
