There are two numeric types in MySQL, integers and real numbers (that is, numbers with decimals). An integer can be TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT, corresponding to 8 -, 16 -, 24 -, 32 -, or 64-bit storage space respectively. Real numbers can be represented by FLOAT, DOUBLE, and DECIMAL, where FLOAT and DOUBLE are commonly referred to as floating-point numbers, which give approximate results due to the precision of computer binary storage.

Integer field type design

Integers can optionally be UNSIGNED, which disallows negative numbers, and can double the maximum value that can be stored. For example, a TINYINT UNSIGNED field can store values in the range 0-255 instead of the signed -128-127. The use of unsigned or unsigned depends on the scope of the field type, but if the field is determined to be unsigned, the unsigned type is preferred because doubling the maximum value somewhat avoids the use of integer types with more storage space.

MySQL allows us to specify the width of an integer, such as INT(11). This doesn’t make a lot of sense for most applications — MySQL doesn’t actually limit the range of valid values, meaning that even if you specify INT(11), you can actually store integers larger than the set width as long as there’s enough storage space for numeric types. For example, an INT(12) number might also be stored in a field of type INT(11). INT(1) and INT(20) are equivalent for storage and computation.

If performance is a real concern, use integer field types based on the true value range of the business. The rule is to choose the lowest storage length integer type possible, as long as the field type does not exceed the maximum value of the business system. If it is an unsigned value, use unsigned attributes whenever possible. For example, if an integer is used instead of an enumeration, UNSIGNED TINYINT is preferred if the number of enumerations does not exceed 255.

The set of real Numbers

Real numbers can be used not only to store floating-point numbers, but actually to store values beyond BIGINT using the DECIMAL type. For floating-point numbers, MySQL supports exact floating-point types and imprecise floating-point numbers.

The FLOAT and DOUBLE types support approximations of standard mathematical operations, and the accuracy of the actual result of a floating-point number depends on the platform on which it is implemented. The DECIMAL type is used to store exact floating-point numbers, and after MySQL 5.0, DECIMAL also supports exact mathematical operations (earlier versions actually used floating-point numbers for DECIMAL operations). However, because the CPU itself cannot directly evaluate floating-point numbers accurately, DECIMAL data types are slower than floating-point numbers.

Both floating-point and DECIMAL support specified precision. The DECIMAL type can specify the largest number of digits before and after the DECIMAL point, which affects the storage footprint of the data column. After MySQL5.0, numeric bits are stored in binary form (4 bytes for every 9 bits). For example DECIMAL(18, 9) will have 9 digits on either side of the DECIMAL point, and counting the DECIMAL point (which is one byte), a total of 9 bytes are required to store. # 1426-too-big precision 66 specified for ‘number’. Maximum is 65:

CREATE TABLE t_numbers ( 
  id INT(11) AUTO_INCREMENT PRIMARY KEY, 
  number DECIMAL(66.1));Copy the code

For FLOAT and DOUBLE types, integer and DECIMAL digits can be specified in a similar manner to DECIMAL to determine storage range and precision. Different lengths cause MySQL to choose different data types by default and use approximations to store data. The storage length of FLOAT is fixed at 4 bytes and that of DOUBLE is fixed at 8 bytes. Precision is uncertain and specifying precision does not help the storage space, so it is recommended not to specify precision in terms of calculation accuracy. For internal calculations, MySQL chooses to use DOUBLE to evaluate data of type FLOAT.

Because DECIMAL takes up more space and consumes more computing resources, it is recommended that you choose to use DECIMAL only when you need to accurately represent numerical values (such as financial data, such as amounts). If you are concerned about computing performance, you can also consider using BIGINT to store precise floating point numbers. For example, the amount of money is uniformly multiplied by a fixed multiple and converted to BIGINT for calculation. This method is less efficient and less storage space.