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

User · Answer

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

User · Answer

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

User · Answer

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 lt  lt 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  amp  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

User · Answer

float  and double  represents binary fractions  decimal represents decimal fractions

User · Answer

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

User · Answer

mysql gt  CREATE TABLE num id int  fl float dc dec 5 2    Query OK  0 rows affected  0 00 sec    mysql gt  INSERT INTO num VALUES 1 13 75 13 75   Query OK  1 row affected  0 00 sec   mysql gt  INSERT INTO num VALUES 2 13 15 13 15   Query OK  1 row affected  0 00 sec   mysql gt  SELECT   FROM num WHERE fl   13 15  Empty set  0 00 sec   mysql gt  SELECT   FROM num WHERE dc   13 15   ------ ------- -------    id     fl      dc       ------ ------- -------       2   13 15   13 15    ------ ------- -------  1 row in set  0 00 sec   mysql gt  SELECT SUM fl   SUM dc   FROM num   -------------------- ---------    SUM fl               SUM dc     -------------------- ---------    26 899999618530273     26 90    -------------------- ---------  1 row in set  0 00 sec    mysql gt  SELECT   FROM num WHERE ABS fl -  13 15  lt 0 01   ------ ------- -------    id     fl      dc       ------ ------- -------       2   13 15   13 15    ------ ------- -------  1 row in set  0 00 sec

User · Answer

This is what I found when I had this doubt   mysql gt  create table numbers  a decimal 10 2   b float   mysql gt  insert into numbers values  100  100   mysql gt  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   Hope this helps

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  CREATE TABLE t1  i INT  d1 DOUBLE  d2 DOUBLE   mysql gt  INSERT INTO t1 VALUES  1  101 40  21 40    1  -80 00  0 00       - gt   2  0 00  0 00    2  -13 20  0 00    2  59 60  46 40       - gt   2  30 40  30 40    3  37 00  7 40    3  -29 60  0 00       - gt   4  60 00  15 40    4  -10 60  0 00    4  -34 00  0 00       - gt   5  33 00  0 00    5  -25 80  0 00    5  0 00  7 20       - gt   6  0 00  0 00    6  -51 40  0 00    mysql gt  SELECT i  SUM d1  AS a  SUM d2  AS b     - gt  FROM t1 GROUP BY i HAVING a  lt  gt  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 gt  SELECT i  SUM d1  AS a  SUM d2  AS b FROM t1     - gt  GROUP BY i HAVING ABS a - b   gt  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 gt  SELECT i  SUM d1  AS a  SUM d2  AS b FROM t1     - gt  GROUP BY i HAVING ABS a - b   lt   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

User · Answer

Hard  amp  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

[mysql] Difference between float and decimal data type

Examples related to mysql