MySQL data type optimization-integer type optimization selection

Original: http://bbs.landingbj.com/t-0-240002-1.html

When designing a database, the use of integer types is unavoidable, such as ID, type, and so on.

When selecting integers, the main consideration is the data range, such as whether it is signed, the maximum stored value.

First, the wrong sign

The unsigned unsigned type is one-fold more than the signed integer.

Such as

Tinyint range -128~127

tinyint unsigned range 0~255

The tinyint unsigned here is one more than the maximum number of tinyint, and the other is the same.

If we have a range of 1-200, if using a signed integer tinyint is not sufficient, use only a wider selection of fields

However, the use of tinyint unsigned can be satisfied.

Second, integer range

tinyint ~ Hundred

smallint ~ million

Mediumint ~ million

int ~ billion

bigint ~ 1 billion x10 billion

Data Detail Range

Tinyint 8 bits 0 ~ 255-128 ~ 127
smallint bits 0 ~ 65535-32768 ~ 32767
Mediumint bits 0 ~ 16777216-8388608 ~ 8388607
int bits 0 ~ 4294967295-2147483648 ~ 2147483647
bigint bits 0 ~ 18446744073709551615-9223372036854775808 ~ 9223372036854775807

Third, how to choose

The data here is mainly block letters data range such as about 100 with tinyint, sometimes we need to pass the calculation, such as we publish a piece of news

Statistics its traffic, such as: 1000 (daily traffic) *365 (days) *10 (year) =36500000 (TENS) This is the data is greater than Mediumint, and in the Int range class

But if we think about it, a piece of news can reach the daily visit to 1000 and last 10 years, if you can then choose Int, if the probability is not mediumint may be the best choice.

Sometimes we all figure out the analysis of these ranges, anyway, as long as they are integers, either int or bigint. Save design analysis without compromising performance.

At that time there is a design error in fact with an int (x) as an integer range, the original published the corresponding blog database-integer type width-int (3) and int (11) The difference between the argument!

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

MySQL data type optimization-integer type optimization selection