The company has been working on a transaction system, and certainly a transaction system is going to be dealing with money, so it’s kind of a natural thing to think about using float, but when you use float to store the amount of money you’re doing it’s an approximation. If you lose money by using float, we’ll deduct it from your salary

Why can’t FLOAT be used to store amounts

Let’s start with an example: FloatTest. Java

public class FloatTest {
    public static void main(String[] args) {
        floatF1 = 6.6 f;floatF2 = 1.3 f; System.out.println(f1 + f2); }}Copy the code

Results: 7.8999996 and their oral calculation of the value is actually different

Computers only recognize zeros and ones, and all types of calculations are first converted to binary calculations

The process of calculating 6.6 + 1.3 from a computer binary point of view

Float underlying storage

The computation is done by the CPU, which indicates that the floating-point number is made up of three parts: the sign bit, the exponent, and the valid part (fraction, mantissa). Float occupies 32 bits, the symbol bit, the index part, and the valid part, respectively

Binary conversion

For real numbers, the conversion to binary is divided into two parts, the first part is the integer part, and the second part is the decimal part. The integer part of computing binary is familiar.

Integer part calculation: 6 to binary
Divided by 2 The results of The decimal part
6 3 0
3 1 1
1 0 1

So 6 ends up being 110 in binary

Calculation of fractional parts

Multiply the decimal by 2, take the integer part as the binary value, then multiply the decimal by 2 again, take the integer part again, and repeat

0.6 is converted to binary
2 times The integer part The decimal part
1.2 1 0.2
0.4 0 0.4
0.8 0 0.8
1.6 1 0.6
1.2 1 0.2

. Enter the loop, the body of the loop is 1001 so 0.6 converts to binary 0.10011001… 6.6 Converting binary To 110.10011001…

Statute,

To reduce decimals to regular form, similar to scientific notation, is to ensure that the decimal point is preceded by a significant number. In binary, the whole number is guaranteed to be a 1. 110.10011001 is normalized to 1.1010011001*2^2

Exponential offset

Index offset = fixed value + normalized index value fixed value =2^(e-1)-1, where e is the number of bits to store the index part and float is 8 bits. The binary value of 6.6 is 1.1010011001*2^2, the index is 2, so the offset is 127+2=129, and the binary value is 10000001.

Stitching 6.6

6.6 is a positive number, the sign bit is 0, the index part is the offset binary 10000001, and the effective part is the decimal part in the regular form. The first 23 digits of the decimal are 10100110011001100110011. Last stitching together or 01000000110100110011001100110011 here has not been accurate can be roughly know why float, first in the store when it comes to precision loss, here is the decimal part of a binary cycle, but still can take before 23. The same is true for double precision loss

sum

So that’s it

You can’t use float so what type do you use to store money?

  • Use int database store is the amount of points, display when converted into yuan
  • Use decimal The use of the decimal storage type in mysql 
     column_name  decimal(P,D);
Copy the code

    D: indicates the number of digits after the decimal point. P: indicates the accuracy of the number of significant digits. The decimal point also counts as one digit.

    CREATE TABLE `test_decimal` ( `id` int(11) NOT NULL, 'amount' decimal(10,2) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4Copy the code

    The corresponding DAO layer code is testDecimaldao.java

    /** * @description DAO layer ** @author JoyHe * @date 2018/11/05 * @version 1.0 */ @repository public interface TestDecimalDao { @Select("select * from test_decimal where id = #{id}")
    TestDecimal getTestDecimal(int id);
}
Copy the code

    Test class: TestDecimalDaotest.java

    /** * @description Test class ** @author JoyHe * @date 2018/11/05 * @version 1.0 */ BaseTest { @Resource private TestDecimalDaotestDecimalDao;

    @Test
    public void test() {
        TestDecimal testDecimal1 =   testDecimalDao.getTestDecimal(1);
        TestDecimal testDecimal2 =   testDecimalDao.getTestDecimal(2);
        BigDecimal result =   testDecimal1.getAmount().add(testDecimal2.getAmount()); System.out.println(result.floatValue()); }}Copy the code

    JdbcType = decimal; javaType = BigDecimal;

    It was in line with expectations at 7.9

Disadvantages of using decimal storage types

  • Occupies storage space. Floating-point types typically use less space than Decimal to store the same range of values
  • Using Decimal is not efficient

Because of the time and space overhead of using Decimal, int is a suitable database storage format to avoid both the imprecision of floating-point storage calculations and the drawbacks of Decimal. For storing large numbers or reserving large numbers, the database storage structure can be bigINT, which avoids both inaccurate floating-point storage and costly DECIMAL calculation











Reference: 1. “Floating point calculation precision loss causes” 2. “High Performance MySQL”