MySQL data type optimization-integer type optimization selection _ MySQL

MySQL data type optimization-integer type optimization when designing a database, it is inevitable to use the integer type, such as ID and type.

When an integer is selected, the main consideration is the data range, such as whether it is signed or not, and the maximum storage value.

I. incorrect symbols

The unsigned type is twice the unsigned integer.

For example

Tinyint range-128 ~ 127

Tinyint unsigned range: 0 ~ 255

Here, tinyint unsigned is twice the maximum number of tinyint.

For example

If our range is 1-200, if the signed integer tinyint cannot be used, only a wider field is selected.

However, tinyint unsigned can be used.

II. integer range

Tinyint ~ Hundred

Smallint ~ 10 thousand

Mediumint ~ 10 million

Int ~ 1 billion

Bigint ~ 1 billion x million

Data details range

Tinyint 8 bits 0 ~ 255-128 ~ 127

Smallint 16 bits 0 ~ 65535-32768 ~ 32767


Mediumint 24 bits 0 ~ 16777216-8388608 ~ 8388607


Int 32 bits 0 ~ 4294967295-2147483648 ~ 2147483647


Bigint 64 bits 0 ~ 18446744073709551615-9223372036854775808 ~ 9223372036854775807

3. how to choose

The data here is mainly a large data range, such as about 100 using tinyint, sometimes we need to calculate, such as a piece of news we publish.

Count its access volume, for example: 1000 (daily access volume) * 365 (day) * 10 (year) = 36500000 (10 million) this is the data is greater than mediumint, and in the int range class

However, if we consider that a piece of news can reach 1000 daily traffic and last for 10 years, we should choose int if we can. mediumint may be the best choice if it is unlikely.

Sometimes we analyze the ranges shown in the diagram, as long as it is an integer, either int or bigint. Saves design analysis when the performance is not significantly affected.

At that time, there was another design misunderstanding in which int (x) was used as the integer range. Previously, the corresponding blog database-Integer width-int (3) and int (11) were released) differences have been demonstrated!

Address: http://blog.yi18.net/articles/2014/04/06/1396777140197.html