MySQL data type optimization-integer type optimization selection

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.