Explain how to use the correct numeric type of MySQL Data Type

Whether in a small free database space or a large e-commerce website, it is necessary to reasonably design the table structure and make full use of the space. This requires us to fully understand the common MySQL Data Types in the database system. Next I will share some of my experiences with you.

MySQL Data Type-numeric type

The number types are classified into three types by my classification: integer, decimal, and number.

My so-called "number classes" refer to DECIMAL and NUMERIC. They are of the same type. Strictly speaking, it is not a numeric type, because they actually save numbers as strings; Each digit of his value (including the decimal point) occupies a byte storage space, therefore, this type consumes a lot of space. However, one of its outstanding advantages is that the number of digits in decimal places is fixed and will not be "distorted" in the computation ", therefore, it is suitable for "price" and "amount" fields with low precision requirements but high accuracy requirements.

Decimal type, that is, the floating point type. FLOAT and DOUBLE types are supported based on the precision. Their advantage is accuracy. FLOAT can indicate that the absolute value is very small, as small as about 1.17E-38 (0. 000... 0117, 37 digits after the decimal point), and DOUBLE can indicate that the absolute value is smaller than about 2.22E-308 (0. 000... 0222, with a decimal point followed by 307 zeros.

The storage space occupied by FLOAT and DOUBLE types is 4 bytes and 8 bytes respectively. If you need to use decimal fields, the precision requirement is not high. Of course, FLOAT is used. But to be honest, how does our "Civil" data require high accuracy? I have never used these two types-I have not encountered any examples that are suitable for using them.

The most commonly used, most cost-effective, is the integer type. From TINYINT, which occupies only one byte of storage space, to BIGINT, which occupies eight bytes, selecting a type that is "adequate" and occupies the smallest storage space should be considered during database design. The storage space occupied by TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT is 1 byte, 2 byte, 3 byte, 4 byte, and 8 byte, respectively. For unsigned integers, the maximum Integers of these types are 255, 65535, 16777215, 4294967295, and 18446744073709551615.

If it is used to save the user's age (for example, it is not advisable to store the age in the database), TINYINT is enough. In 9-city's vertical bar, skill values, SMALLINT is enough. If you want to use it as the IDENTIFY field of AUTO_INCREMENT for a table with no more than 16000000 rows, of course, MEDIUMINT is not used. Imagine that each row saves one byte, 16000000 rows can save more than 10 MB!