Float,decimal Accuracy Comparison
Float,double easy to produce errors, high accuracy requirements, it is recommended to use decimal to save, decimal in MySQL memory is a string storage, used to define data with high accuracy in monetary requirements . In data migration, float (m,d) is a non-standard definition and should not be used in this way. M is precision and D is the scale. |
mysql> CREATE TABLE T1 (C1 float (10,2), C2 decimal (10,2), C3 float); 10 not including decimal points
mysql> INSERT INTO T1 values (1234567.23, 1234567.23,1234567.23);
Mysql> select * from T1;
The data display is related to the client tools used
1. In Toad for MySQL, the display is
C1 C2 C3
1234567 1234567.23 1234570
2. The display in MySQL Query browser is
C1 C2 C3
1234567.25 1234567.23 1.23457e+006
3. Remove data from the program
1234567 1234567.23 1234570
mysql> INSERT INTO T1 values (9876543.21, 9876543.12,9876543.21);
1. In Toad for MySQL, the display is
C1 C2 C3
9876543 9876543.12 9876540
2. The display in MySQL Query browser is
C1 C2 C3
9876543.00 9876543.12 9.87654e+006
3. Remove data from the program
9876543 9876543.12 9876540
mysql> INSERT into T1 values (1.21, 1.12,1.21);
1. In Toad for MySQL, the display is
C1 C2 C3
1.21 1.12 1.21
2. The display in MySQL Query browser is
C1 C2 C3
1.21 1.12 1.21
3. Remove data from the program
1.21 1.12 1.21
mysql> INSERT into T1 values (1.2, 1.2,1.2);
1. In Toad for MySQL, the display is
C1 C2 C3
1.2 1.20 1.2
2. The display in MySQL Query browser is
C1 C2 C3
1.20 1.20 1.2
3. Remove data from the program
1.2 1.20 1.2
mysql> INSERT INTO T1 values (9876543.216, 9876543.126,9876543.216);
1. In Toad for MySQL, the display is
C1 C2 C3
9876543 9876543.13 9876540
2. The display in MySQL Query browser is
C1 C2 C3
9876543.00 9876543.13 9.87654e+006
3. Remove data from the program
9876543 9876543.13 9876540
mysql> INSERT INTO T1 values (1.216, 1.126,1.216);
1. In Toad for MySQL, the display is
C1 C2 C3
1.22 1.13 1.216
2. The display in MySQL Query browser is
C1 C2 C3
1.22 1.13 1.216
3. Remove data from the program
1.22 1.13 1.216
When you do not define fload, double the precision and scale, the store is stored by the value given, which is related to the OS and the current hardware.
decimal defaults to decimal (10,0)
Because of the error problem, in the program, less use of floating-point to do = comparison, you can do range comparison. If numeric comparisons, it is best to use the decimal type.
In precision, the symbol does not count:
mysql> INSERT INTO T1 values (-98765430.21,-98765430.12);
Mysql> select * from T1;
C1 C2
-98765432.00-98765430.12
What storage is used for the latitude and longitude fields in MySQL (for the difference between the float and decimal for MySQL)