Length value of mysqlint type _ MySQL

Length value of mysqlint type bitsCN.com

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.

Let's take the int type as an example:

Int type, which occupies 4 bytes. if you have learned computer principles, you should know that bytes are not the smallest unit of computer storage, and are smaller than bytes, that is, bit. a single bit represents a 0 or 1. eight digits constitute a byte. generally, bytes are represented by uppercase B, while digits are represented by lowercase B.

Computer storage unit conversion:

1B = 8b

1KB = 1024B

1 MB = 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 );

Next, let's talk about what the field length is when we create a 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, 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 included in the type;

This sentence does not seem easy to understand, because there is a keyword here that is easy to confuse, "maximum display width" Our first reaction is that the maximum allowed width of the value of 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.

Now we can see that my number field, length (M) = 5, property = unsigned zerofill (UNSIGNED, fill the digits with 0 ), after setting this attribute, when I insert data to the table, the system will automatically fill the number field M with 0 on the left if it is not 5 characters long. The effect is as follows:

BitsCN.com