Three common types of MySQL numeric types

The following articles mainly describe three common types of MySQL numbers. We all know that whether it is a small free MySQL database space or a relatively large e-commerce website, for related databases, it is necessary to reasonably design the table structure and make full use of the space.

This requires us to fully understand and apply common data types of database systems. Next we will introduce some experiences and experiences to you, hoping to help you think more rationally when creating a database.

Numeric type

MySQL numeric types are classified into integer, decimal, and numeric types by my classification method.

One of the MySQL numeric types is my so-called "number class"

DECIMAL and NUMERIC 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.

MySQL decimal type

That is, the floating point type. FLOAT and DOUBLE types are available 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.

MySQL numeric type 3 integer

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.