What is the length of the int type after mysql creates a table? Is this column the maximum width allowed to store values? Why can I set it to int (1) and save 10,100,100 0.
At that time, although I knew int (1), the length of 1 does not mean that the storage width is allowed, but there is no reasonable explanation. in other words, I have not really studied what this length actually represents. I usually use int (11), and I don't know why I need 11 characters. so I read some information online and carefully read the mysql Manual's statement about int data type. the storage and range of each integer type are as follows (from 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 |
A table contains four columns: field type, number of bytes occupied, minimum value allowed for storage, and maximum value allowed for storage. taking the int type as an example: the int type occupies 4 bytes. If you have learned computer principles, you should know that bytes are not the smallest unit of computer storage, there are also smaller units than bytes (bytes), that is, bit, a single bit represents a 0 or 1; eight digits constitute a byte; generally, byte is represented by uppercase B, and bit is represented by lowercase B. computer storage unit conversion: 1B = 8b1KB = 1024B1MB = 1024KB
So the number of bytes allowed to be stored for the int type is 4 bytes, so we can calculate that the minimum value that can be stored for the int UNSIGNED type is 0, the maximum value is 4294967295 (that is, 4B = 32b, and the maximum value is composed of 32 1). Let's talk about the length of the field during table creation. create table 'test '(
'Id' INT (11) not null AUTO_INCREMENT primary key,
'Number' INT (5) NOT NULL
) ENGINE = MYISAM; take the number field of the test table as an example. We can see that I created an int (5)
In the mysql manual, this length/value is represented by "M". Careful friends should have noticed the following sentence in the mysql Manual: M indicates the maximum display width. The maximum valid display width is 255. The display width has nothing to do with the storage size or the range of values in the type; this sentence does not seem easy to understand, because here there is a keyword that is easy for us to confuse, "Maximum display width" our first response is the width of the maximum value allowed for storing this field. we thought we had built int (1), so we could not store data 10. we can simply understand this M = 5. We established this length to tell the MYSQL database that the data stored in this field is 5 digits in width, of course, if you are not a 5-digit MYSQL (as long as it is within the storage range of this type), it can be properly stored, which can explain the above marked red. modify the "attribute" of this field to unsigned zerofill to see the effect.
The Manual also says, "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 the width appropriately?
However, after reading this document, you should know clearly that the length of M has nothing to do with the size of the number of numeric types you store.
From: http://www.bkjia.com/database/201208/150865.html
