Difference between mysql int (3) and int (11), mysqlint

Difference between mysql int (3) and int (11), mysqlint

Int (M) zerofill. After zerofill is added, M shows a little effect,

For example, if you insert int (3) zerofill to 10 in the database, the actual insert is 010, that is, add a 0

If int (3) and int (10) do not add zerofill, there is no difference between them. M is not used to limit the number of int.

The maximum and minimum values of int (M) are related to undesigned, as shown in:

1 bytes = 8 bit. A single byte can represent a maximum of 8 power of 2, which is-128 to 127 in the computer.

Tinyint: one byte, smallint, MEDIUMINT, int, and BIGINT.

BIT [M] field type. M indicates the number of digits of each value, ranging from 1 to 64. If M is ignored, the default value is 1.

 

TINYINT [(M)] [UNSIGNED] [ZEROFILL] M is 4 by default

A small integer. The signed range is-128 to 127.

The unsigned range is 0 to 255.

 

BOOL, BOOLEAN

Is a synonym for TINYINT (1. The zero value is considered false. Non-zero values are considered as true

 

SMALLINT [(M)] [UNSIGNED] [ZEROFILL] M is 6 by default

A small integer. The signed range is-32768 to 32767.

The unsigned range is 0 to 65535.

 

MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL] M is 9 by default

An integer of medium size. The signed range is-8388608 to 8388607.

The unsigned range is 0 to 16777215.

 

INT [(M)] [UNSIGNED] [ZEROFILL] M is 11 by default

An integer of the normal size. The signed range is-2147483648 to 2147483647.

The unsigned range is 0 to 4294967295.

 

BIGINT [(M)] [UNSIGNED] [ZEROFILL] M is 20 by default

A large integer. The signed range is-9223372036854775808 to 9223372036854775807.

The unsigned range is 0 to 18446744073709551615.

 

Note: Here M represents not the specific length stored in the database. In the past, I always mistakenly thought that int (3) can only store three numbers in length, int (11) it stores 11 length numbers, which is a big mistake.

In fact, when we select the int type, whether it is int (3) or int (11), it stores 4 bytes in the database,

If you input 10 in int (3) zerofill

That is to say, this 3 represents a default length. When you have less than three digits, it will help you complete the problem. When you have more than three digits, there will be no impact (data storage, extracted and displayed are not affected ).

The day before yesterday, the Group Administrator asked me what is the difference between int (10) and int (11). I thought it was the difference in length. Now, apart from a slight difference in storage,

There is no difference in our use. Int (10) can also represent the value of 2147483647 int (11.

To check the effect, remember to add the zerofill value when creating the type, indicating to fill it with 0; otherwise, the effect will not be displayed.

We usually do not add this option when creating databases, so there is no difference between them.

 

+ ------------------------------------------ Gorgeous split line ------------------------------------------------- +

Tinyint (1) is no different from tinyint (3). It occupies one byte and has the same storage range!

Tinyint (1) and tinyint (3) can be stored at 123 (both can be retrieved and displayed normally ),

If tinyint (3) zerofill is inserted, the inserted value 12 will store 012, and zerofill will automatically add 0 to the left, which is the limit on the display length.

Int (3) zerofill. When the inserted data is less than three digits, the left side is automatically set to zero. When the inserted data is more than three digits, there is no impact (data is stored, retrieved, and displayed without any impact ).