Everybody is good! Just stepped into the learning journey of MySQL, in order to conveniently operate MySQL database, we in addition to learning the composition of MySQL, but also need to master the operation of its language, since there is a language, then there must be data types to indicate the type of data, so that the system can better judge whether the input statement is legitimate.

Since data types are so important, floating point types are a big deal, so let’s take a look at them today.

What is a floating point number?

In addition to integers, numbers can also be expressed as decimals. In daily life, the representation of numbers is D.D(where D refers to integers). That’s right, floating point numbers are data types constructed to store decimals! The floating point types in MySQL include FLOAT and DOUBLE. The properties of these types are shown in the following figure.

How are floating point types represented at the physical level?

Because of the extra decimal point, the representation of numbers is completely different. We all know that the numbers stored in the computer are binary representation, because of the difference in the position of the decimal point at the same length, the representation of the number is ambiguous, and to avoid ambiguity, it seems that extra space is needed to record the position of the decimal point. But in terms of cost, storing the same data in a database takes up less space, the better.

MySQL designers, therefore, in the form of the scientific notation to represent: a x 2 ⁿ, of which 1 | | or less a < 2, called the decimal point behind the number mantissa, called the n index. By this convention, we only need to represent the mantissa and the exponent to represent a decimal, and of course, since decimals are positive and negative, we also need the first digit to represent the sign.

By default, FLOAT has an accuracy of 23 bits and DOUBLE has an accuracy of 53 bits and is stored as shown in the figure below.

It is also important to note that there is a loss of precision most of the time when converting from decimal to binary. For example, 0.3, converted to a binary decimal, is an infinite decimal, but we can only represent this decimal in 4 or 8 bytes, so we have to do some rounding to approximate it, which is why floating point representations of computers are sometimes inaccurate.

Precision customization

We said in front of the data type is used for the data constraints, MySQL introduces two parameters for that, at the time of setting floating-point data types can be behind the type with two parameters, respectively is M and D, which M says the decimal number of decimal effective most need D said the decimal number of decimal digits after the decimal point. Attention attention attention! Is a valid decimal number!

According to the above definition, when D is the same, the larger M is, the larger the value range can be represented. With the same M, the larger D is, the smaller the number that can be represented. Also, the values of M and D are not infinite, M ranges from 1 to 255 and D ranges from 0 to 30, and D must be less than M. It is suggested to set the table according to the business value, so that the internal mechanism of MySQL can be used to limit the data format and improve the robust of the system.

Some of you may be wondering, isn’t the benefit that the data takes up less space, so you can store more data?

No, single-precision floating-point types FLOAT (M,D) always take up 4 bytes, and DOUBLE (M,D) always takes up 8 bytes. The amount of storage they take up does not change with the values of M and D.

Ok, that’s all for today’s introduction. Of course, there is a remaining problem, that is, what if the data needs to be stored without losing precision? See you next time for a solution to this problem!

This article is part of the “Gold Nuggets For Free!” Event, click to view details of the event