Detailed MySQL data type of the correct use of the number type

Either in the pitiful free database space or large e-commerce sites, reasonable design table structure, make full use of space is very necessary. This requires that we have a full understanding of the commonly used MySQL data types for database systems. Now I will write down my little experience to share with you.

The numeric type of the MySQL data type

According to my classification of digital types are divided into three categories: integer, decimal and digital categories.

My so-called "digital class" refers to DECIMAL and NUMERIC, which are the same type. It strictly speaking is not a type of numbers, because they are actually stored as a string of numbers; each of his values ​​(including the decimal point) accounts for a byte of storage space, so this type of space-consuming comparison Big. But one of its outstanding advantages is that the decimal places are fixed and will not be "distorted" in the calculation, so it is more suitable for fields such as "price" and "amount" that require less precision but require very high accuracy.

Fractional, that is, the types of floating-point, depending on the accuracy, there are two FLOAT and DOUBLE. Their advantage is accuracy. FLOAT can represent a decimal with a very small absolute value as small as about 1.17E-38 (0.000 ... 0117, with 37 zeros after the decimal point), and DOUBLE can represent an absolute value as small as about 2.22E-308 (0.000 ... 0222, 307 zeros after the decimal point).

FLOAT type and DOUBLE type of storage space is 4 bytes and 8 bytes. If you need to use the decimal field, the accuracy is not high, of course, with FLOAT. But to be honest, the data for our "civil" data are so high as to be required? Both types have not been used up to now - I have not encountered any examples that are suitable for using them.

Most used, the most cost-effective, is an integer type. From TINYINT, which occupies only one byte of storage, to BIGINT, which occupies eight bytes, choosing a type that is "good enough" and takes up the least amount of storage space should be considered when designing your database. TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT occupy 1 byte, 2 bytes, 3 bytes, 4 bytes and 8 bytes, respectively. For unsigned integers, the largest integers represented by these types are 255, 25535, 16777215, 4294967295 and 18446744073709551615.

If used to save the user's age (for example, the age of the database is not desirable to save), with TINYINT enough; Ninetowns "aspect", the skill value, with SMALLINT enough; if you want to use For a certainly not more than 16000000 rows table AUTO_INCREMENT IDENTIFY field, of course MEDINTINT without INT, imagine, save a byte per line, 16000000 lines can save 10 trillion!