Question 261033 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – What's difference between float, double and decimal in sql?

I was asked to add a column containg the bonuses of salary of employees, examples of this values were give such as: 1099.99 or 2.00. Task also asked me to add appropriate data type but I was confused which one to use.

I tried using both double and float and couldn’t differenciate between the results. So, help me choose appropriate datatype.

Tags:

2

Answers


  1. 0

    Regarding to the official documentation, we have the next diferences:

    • FLOAT – Floating-Point Types (Approximate Value), uses four bytes
    • DOUBLE – Floating-Point Types (Approximate Value), uses eight bytes
    • DECIMAL – Fixed-Point Types (Exact Value)

    You can ask about differences between floating and fixed datatypes, but this is not so easy to describe, try to read the same official documentation, or this article, for example.

    Login or Signup to reply.
  2. 0

    Computer scientist Brian Kernighan wrote:

    10.0 times 0.1 is hardly ever 1.0.

    FLOAT and DOUBLE are both IEEE 754 format values. They store a wide range of floating-point value, including very small or very large values, but they have the disadvantage that they have rounding errors.

    For examples and details on that, read:

    FLOAT stores 32 bits and DOUBLE stores 64 bits. This just means you can store more significant digits with a DOUBLE, but it still has the same rounding behavior.

    These data types are good for scientific measurements like temperature, where the difference between 39 degrees and 38.999999997 degrees won’t make a significant difference, especially if you’re only doing things like averages or standard deviations.

    Because of the rounding behavior, FLOAT and DOUBLE should never be used to store values of currency.

    For currency values (e.g. the salaries you need to store), use DECIMAL.

    DECIMAL and NUMERIC are exact scaled numerics. You define a column with a specific number of digits, and a specific number of digits to the right of the decimal point. There are no rounding problems; values you store come right back out exactly as you stored them. You can search the data for specific values.

    DECIMAL and NUMERIC are synonyms. There’s no difference. In fact, if you use NUMERIC as the type, MySQL silently replaces it with DECIMAL.

    You may also like to read the chapter "Rounding Errors" in my book, SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search