Three common types of MySQL number types

Number Type

The MySQL number type is divided into three categories according to my classification method: Integer class, Decimal class, and number class.

MySQL number type one of my so-called "number Classes"

Refers to DECIMAL and NUMERIC, which are the same type. It is strictly not a numeric type, because they actually save the number as a string, and each bit of his value (including the decimal point) occupies one byte of storage space, so this type is much more space-consuming. However, it is a prominent advantage is that the number of decimal places fixed, in the operation will not be "distorted", so it is more suitable for "price", "amount" such as the accuracy of the requirements of the very high accuracy requirements of the field.

MySQL number type two decimal class

That is, floating-point number type, according to the different precision, there are float and DOUBLE two kinds. Their advantage is precision, and FLOAT can represent a very small absolute value, small to about 1.17E-38 (0.000...0117, with a number of PNs after the decimal point of 0), and a DOUBLE can represent an absolute value of less than about 2.22E-308 ( 0.000...0222, a decimal number with 307 0 after the decimal point.

The FLOAT type and DOUBLE type occupy storage space of 4 bytes and 8 bytes, respectively. If you need to use a decimal field, the accuracy is not high, of course, with FLOAT. However, to say a word, we "civilian" data, which requires a high degree of precision? These two types have not been used so far-I have not yet encountered cases that are appropriate to use them.

MySQL Number type three integer class

The most used, the most prudent, is the integer type. From TINYINT, which accounts for only one byte of storage, to the 8-byte BIGINT, picking a type that is "enough" and taking up the least amount of storage space should be considered when designing the database. TINYINT, SMALLINT, Mediumint,INT , and BIGINT occupy storage spaces of 1 bytes, 2 bytes, 3 bytes,4 bytes , and 8 bytes, respectively, for unsigned integers. The maximum integers that these types can represent are 255, 65535, 16777215,4294967295 , and 18446744073709551615, respectively.

If it is used to save the user's age (for example, it is not advisable to save the age in the database), the TINYINT is sufficient; in the nine-city "aspect", the skill value, with SMALLINT also enough; If you want to use it as a auto_ for a table that is definitely not more than 16000000 rows INCREMENT IDENTIFY field, of course, with Mediumint without INT, imagine, each row savings of one byte, 16000000 rows can save 10 trillion more.

Three common types of MySQL number types