Differences between int, bigint, smallint, and tinyint in MySQL

In MySQL, the value range of tinyint is-128 to 127. The unsigned range is 0 to 255 (see the official MySQL 5.1 reference manual http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#numeric-types ).

Tinyint occupies 1 byte of storage space, that is, 8 bits ). So how does tinyint value range come from? Let's first look at the unsigned situation. The unsigned minimum value means that all eight bits are 0, and the value is 0 in decimal format. Therefore, the minimum value of the unsigned tinyint is 0. the maximum unsigned value means that all 8 bits are 255, which is in decimal format. this is easy to understand.

How does a signed tinyint value range come from? In a computer, the highest bit is used as the symbol. 0 indicates positive, 1 indicates negative, and the rest indicates numerical values. The minimum value of a signed 8-bit is

1 1 1 1 1 1 1 1 1 =-127

Negative value

Maximum value:

0 1 1 1 1 1 1 1 = + 127

Positive Value

How can the minimum signed value be-127 instead of-128? This is the key point of this article. In the computer, the negative value is indicated by positive code, reverse code, and complement code.

Why is the minimum value of a signed tinyint-128? Although "-0" is also "0", the "-0" complement code is different from "+ 0" according to the positive, reverse, and complement system, in this case, two supplementary codes represent a value. In order to match the complement code with numbers one by one, "0" is always represented by "+ 0. At the same time, in order to make full use of resources, the original "-0" complement code should be set to represent-128.

Use the exact numeric data type of integer data.

Bigint

Integer Data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807) (All numbers ). The storage size is 8 bytes.

Int

Integer Data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647) (All numbers ). The storage size is 4 bytes.IntThe SQL-92Integer.

Smallint

Integer Data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767. The storage size is 2 bytes.

Tinyint

Integer Data from 0 to 255. The storage size is 1 byte.

Note

Supported by integerBigintData type. However,BigintUsed in some special cases, when the integer value exceedsIntYou can useBigint. In SQL Server,IntThe data type is the main integer data type.

In the data type priority table,BigintLocated inSmallmoneyAndInt.

Only when the parameter expression isBigintThe function returns the data type.Bigint. SQL Server does not automatically convert other integer data types (Tinyint,SmallintAndInt)Bigint.