Whether you have a small free database space or a large e-commerce website, it is very necessary for the database to reasonably design the table structure and make full use of the space. This requires us to have a full understanding and reasonable application of common data types in database systems. Below we will introduce to you some experience and experience, hoping to help you think more rationally when building a database.
1. Number type
Numeric types are divided into three categories according to my classification method: integer types, decimal types and numeric types.
What I call "numeric classes" refers to DECIMAL and NUMERIC, which are the same type. It is not strictly a numeric type, because they actually store numbers in the form of strings; each digit of its value (including the decimal point) occupies one byte of storage space, so this type consumes space for comparison big. However, one of its outstanding advantages is that the decimal digits are fixed and will not be "distorted" during operations, so it is more suitable for fields such as "price" and "amount" that do not require high precision but require very high accuracy.
The decimal type, that is, the floating point number type, has two types, FLOAT and DOUBLE, depending on the precision. Their advantage is accuracy. FLOAT can represent decimals with very small absolute values, as small as about 1.17E-38 (0.000...0117, there are 37 zeros after the decimal point), while DOUBLE can represent absolute values as small as approximately 2.22E-308 (0.000...0222, 307 zeros after the decimal point) as a decimal. The storage space occupied by FLOAT type and DOUBLE type is 4 bytes and 8 bytes respectively. If you need to use decimal fields and the accuracy is not high, of course use FLOAT. But to be honest, how can our "civilian" data require such high accuracy? I have not used these two types so far - I have not encountered any cases suitable for using them.
The most used type, and the most worthy of careful calculation, is the integer type. From TINYINT, which only occupies one byte of storage space, to BIGINT, which occupies 8 bytes, choosing a type that is "enough" and occupies the smallest storage space should be considered when designing the database. TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT occupy storage space of 1 byte, 2 bytes, 3 bytes, 4 bytes and 8 bytes respectively. As for unsigned integers, the maximum integers that these types can represent are respectively are 255, 65535, 16777215, 4294967295 and 18446744073709551615. If it is used to save the user's age (for example, it is not advisable to save the age in the database), TINYINT is enough; in Jiucheng's "Zongheng", SMALLINT is enough for each skill value; if you want to use Make the IDENTIFY field of AUTO_INCREMENT in a table that will definitely not exceed 16,000,000 rows. Of course, use MEDIUMINT instead of INT. Just imagine, each row saves one byte. 16,000,000 rows can save more than 10 megabytes.