Difference between float and decimal data type

Question

What difference does it make when I use float and decimal data types in MySQL?.

When should I use which?

Answer 1

This is what I found when I had this doubt.

mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
  @a := (a/3): 33.333333333
  @b := (b/3): 33.333333333333
@a + @a + @a: 99.999999999000000000000000000000
@b + @b + @b: 100

The decimal did exactly what's supposed to do on this cases, it truncated the rest, thus losing the 1/3 part.

So for sums the decimal is better, but for divisions the float is better, up to some point, of course. I mean, using DECIMAL will not give you a "fail proof arithmetic" in any means.

Answer 2

A "float" in most environments is a binary floating-point type. It can accurately store base-2 values (to a certain point), but cannot accurately store many base-10 (decimal) values. Floats are most appropriate for scientific calculations. They're not appropriate for most business-oriented math, and inappropriate use of floats will bite you. Many decimal values can't be exactly represented in base-2. 0.1 can't, for instance, and so you see strange results like 1.0 - 0.1 = 0.8999999.

Decimals store base-10 numbers. Decimal is an good type for most business math (but any built-in "money" type is more appropriate for financial calculations), where the range of values exceeds that provided by integer types, and fractional values are needed. Decimals, as the name implies, are designed for base-10 numbers - they can accurately store decimal values (again, to a certain point).

Answer 3

MySQL recently changed they way they store the DECIMAL type. In the past they stored the characters (or nybbles) for each digit comprising an ASCII (or nybble) representation of a number - vs - a two's complement integer, or some derivative thereof.

The current storage format for DECIMAL is a series of 1,2,3,or 4-byte integers whose bits are concatenated to create a two's complement number with an implied decimal point, defined by you, and stored in the DB schema when you declare the column and specify it's DECIMAL size and decimal point position.

By way of example, if you take a 32-bit int you can store any number from 0 - 4,294,967,295. That will only reliably cover 999,999,999, so if you threw out 2 bits and used (1<<30 -1) you'd give up nothing. Covering all 9-digit numbers with only 4 bytes is more efficient than covering 4 digits in 32 bits using 4 ASCII characters, or 8 nybble digits. (a nybble is 4-bits, allowing values 0-15, more than is needed for 0-9, but you can't eliminate that waste by going to 3 bits, because that only covers values 0-7)

The example used on the MySQL online docs uses DECIMAL(18,9) as an example. This is 9 digits ahead of and 9 digits behind the implied decimal point, which as explained above requires the following storage.

As 18 8-bit chars: 144 bits

As 18 4-bit nybbles: 72 bits

As 2 32-bit integers: 64 bits

Currently DECIMAL supports a max of 65 digits, as DECIMAL(M,D) where the largest value for M allowed is 65, and the largest value of D allowed is 30.

So as not to require chunks of 9 digits at a time, integers smaller than 32-bits are used to add digits using 1,2 and 3 byte integers. For some reason that defies logic, signed, instead of unsigned ints were used, and in so doing, 1 bit gets thrown out, resulting in the following storage capabilities. For 1,2 and 4 byte ints the lost bit doesn't matter, but for the 3-byte int it's a disaster because an entire digit is lost due to the loss of that single bit.

With an 7-bit int: 0 - 99

With a 15-bit int: 0 - 9,999

With a 23-bit int: 0 - 999,999 (0 - 9,999,999 with a 24-bit int)

1,2,3 and 4-byte integers are concatenated together to form a "bit pool" DECIMAL uses to represent the number precisely as a two's complement integer. The decimal point is NOT stored, it is implied.

This means that no ASCII to int conversions are required of the DB engine to convert the "number" into something the CPU recognizes as a number. No rounding, no conversion errors, it's a real number the CPU can manipulate.

Calculations on this arbitrarily large integer must be done in software, as there is no hardware support for this kind of number, but these libraries are very old and highly optimized, having been written 50 years ago to support IBM 370 Fortran arbitrary precision floating point data. They're still a lot slower than fixed-sized integer algebra done with CPU integer hardware, or floating point calculations done on the FPU.

In terms of storage efficiency, because the exponent of a float is attached to each and every float, specifying implicitly where the decimal point is, it is massively redundant, and therefore inefficient for DB work. In a DB you already know where the decimal point is to go up front, and every row in the table that has a value for a DECIMAL column need only look at the 1 & only specification of where that decimal point is to be placed, stored in the schema as the arguments to a DECIMAL(M,D) as the implication of the M and the D values.

The many remarks found here about which format is to be used for various kinds of applications are correct, so I won't belabor the point. I took the time to write this here because whoever is maintaining the linked MySQL online documentation doesn't understand any of the above and after rounds of increasingly frustrating attempts to explain it to them I gave up. A good indication of how poorly they understood what they were writing is the very muddled and almost indecipherable presentation of the subject matter.

As a final thought, if you have need of high-precision floating point computation, there've been tremendous advances in floating point code in the last 20 years, and hardware support for 96-bit and Quadruple Precision float are right around the corner, but there are good arbitrary precision libraries out there if manipulation of the stored value is important.

Answer 4

Not just specific to MySQL, the difference between float and decimal types is the way that they represent fractional values. Floating point types represent fractions in binary, which can only represent values as {m*2^n | m, n Integers} . values such as 1/5 cannot be precisely represented (without round off error). Decimal numbers are similarly limited, but represent numbers like {m*10^n | m, n Integers}. Decimals still cannot represent numbers like 1/3, but it is often the case in many common fields, like finance, that the expectation is that certain decimal fractions can always be expressed without loss of fidelity. Since a decimal number can represent a value like $0.20 (one fifth of a dollar), it is preferred in those situations.

Answer 5

decimal is for fixed quantities like money where you want a specific number of decimal places. Floats are for storing ... floating point precision numbers.

Answer 6

mysql> CREATE TABLE num(id int ,fl float,dc dec(5,2));
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO num VALUES(1,13.75,13.75);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO num VALUES(2,13.15,13.15);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM num WHERE fl = 13.15;
Empty set (0.00 sec)

mysql> SELECT * FROM num WHERE dc = 13.15;
+------+-------+-------+
| id   | fl    | dc    |
+------+-------+-------+
|    2 | 13.15 | 13.15 |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> SELECT SUM(fl) ,SUM(dc)  FROM num;
+--------------------+---------+
| SUM(fl)            | SUM(dc) |
+--------------------+---------+
| 26.899999618530273 |   26.90 |
+--------------------+---------+
1 row in set (0.00 sec)


mysql> SELECT * FROM num WHERE ABS(fl -  13.15)<0.01;
+------+-------+-------+
| id   | fl    | dc    |
+------+-------+-------+
|    2 | 13.15 | 13.15 |
+------+-------+-------+
1 row in set (0.00 sec)

Answer 7

I found this useful:

Generally, Float values are good for scientific Calculations, but should not be used for Financial/Monetary Values. For Business Oriented Math, always use Decimal.

Source: http://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/

Answer 8

If you are after performance and not precision, you should note that calculations with floats are much faster than decimals

Answer 9

Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column.

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

Problems with Floating-Point Values

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. The FLOAT and DOUBLE data types are subject to these issues. For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.

The following example uses DOUBLE to demonstrate how calculations that are done using floating-point operations are subject to floating-point error.

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
    -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
    -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
    -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
    -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
    -> (6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;

+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

The result is correct. Although the first five records look like they should not satisfy the comparison (the values of a and b do not appear to be different), they may do so because the difference between the numbers shows up around the tenth decimal or so, depending on factors such as computer architecture or the compiler version or optimization level. For example, different CPUs may evaluate floating-point numbers differently.

If columns d1 and d2 had been defined as DECIMAL rather than DOUBLE, the result of the SELECT query would have contained only one row—the last one shown above.

The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value. For example, if we agree that floating-point numbers should be regarded the same if they are same within a precision of one in ten thousand (0.0001), the comparison should be written to find differences larger than the tolerance value:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    6 | -51.4 |    0 |
+------+-------+------+
1 row in set (0.00 sec)

Conversely, to get rows where the numbers are the same, the test should find differences within the tolerance value:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) <= 0.0001;
+------+------+------+
| i    | a    | b    |
+------+------+------+
|    1 | 21.4 | 21.4 |
|    2 | 76.8 | 76.8 |
|    3 |  7.4 |  7.4 |
|    4 | 15.4 | 15.4 |
|    5 |  7.2 |  7.2 |
+------+------+------+
5 rows in set (0.03 sec)

Floating-point values are subject to platform or implementation dependencies. Suppose that you execute the following statements:

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;

On some platforms, the SELECT statement returns inf and -inf. On others, it returns 0 and -0.

An implication of the preceding issues is that if you attempt to create a replication slave by dumping table contents with mysqldump on the master and reloading the dump file into the slave, tables containing floating-point columns might differ between the two hosts.

https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

Answer 10

Hard & Fast Rule

If all you need to do is add, subtract or multiply the numbers you are storing, DECIMAL is best.

If you need to divide or do any other form of arithmetic or algebra on the data you're almost certainly going to be happier with float. Floating point libraries, and on Intel processors, the floating point processor itself, have TONs of operations to correct, fix-up, detect and handle the blizzard of exceptions that occur when doing typical math functions - especially transcendental functions.

As for accuracy, I once wrote a budget system that computed the % contribution of each of 3,000+ accounts, for 3,600 budget units, by month to that unit's consolidation node, then based on that matrix of percentages (3,000 + x 12 x 3,600) I multiplied the amounts budgeted by the highest organizational nodes down to the next 3 levels of the organizational nodes, and then computed all (3,000 + 12) values for all 3,200 detail units from that. Millions and millions and millions of double precision floating point calculations, any one of which would throw off the roll-up of all of those projections in a bottoms-up consolidation back to the highest level in the organization.

The total floating point error after all of those calculations was ZERO. That was in 1986, and floating point libraries today are much, much better than they were back then. Intel does all of it's intermediate calculations of doubles in 80 bit precision, which all but eliminates rounding error. When someone tells you "it's floating point error" it's almost certainty NOT true.

Answer 11

float (and double) represents binary fractions

decimal represents decimal fractions

Answer 12

declare @float as float(10)
declare @Decimal as decimal(10)
declare @Inetger as int

set @float =10.7
set @Decimal =10.7
set @Inetger=@Decimal

print @Inetger

in float when set value to integer print 10 but in decimal 11