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

Mysql convert FLOAT to DECIMAL

I have mysql DB with important financial data, currently the data is stored as float type and I get incorrect data due to float rounding, I want to store it as DECIMAL.

What is the safe way to convert the data in the DB without change existing data? or any another idea to solve that issue?

EDIT: Does converting from FLOAT to VARCHAR and than from VARCHAR to DECIMAL is a safe way?

Thanks in advance!

Tags:

2

Answers


  1. 0

    There is no safe way. Due to how floats work, 32 bit floats greater than 16777216 (or less than -16777216) need to be even, greater than 33554432 (or less than -33554432) need to be evenly divisibly by 4, greater than 67108864 (or less than -67108864) need to be evenly divisibly by 8, etc.

    Login or Signup to reply.
  2. 0

    13815500 is exactly representable in FLOAT. But you are close to what Andrew talks about — 16777217 is not exactly representable; it will be off by 1 Euro or dollar or whatever.

    If you have no decimal places, your choices are

    • FLOAT, which messes up above 16,777,216.
    • DECIMAL(9,0) which can handle numbers up to about 1 billion. Caveat: If you need decimal places, say so!_
    • INT which peaks at about 2 billion.
    • INT UNSIGNED – limit about 4 billion (non-negative values only).

    Each of the above datatypes mentioned above takes 4 bytes. All but the last allow for negative values. FLOAT will keep going, but lose bits at the bottom; the others "overflow".

    Other options: DECIMAL(m,0) with bigger numbers (m<=64), DOUBLE (huge floating range), BIGINT (huge integral range); each take more space.

    The syntax is

    ALTER TABLE tablename
    MODIFY col_name NEW_DATATYPE [NOT NULL];

    (There is no need, and may be harm, in stepping through VARCHAR.)

    General rule: Use DECIMAL for money because it is "exact"; use FLOAT for measurements (such as sensors, distance, etc)

    More

    If the max value is 13815500, then DECIMAL(64,56) will hold any of your numbers, and handle up to 56 decimal places. Furthermore, you can do basic arithmetic exactly on those numbers. Caution: If you will be SUMming a thousand such numbers, you need an extra 3 digits before the decimal point: DECIMAL(64,53). For summing a million numbers: DECIMAL(64,50).

    If your current data is sitting in a FLOAT column, then you only have about 7 significant digits; the rest was lost as the numbers were stored. Can you recover the lost precision? If so, start over with a suitable DECIMAL. If not, then a numerical analyst will argue that you may as well stick with FLOAT. A SUM will still be good to about 6-7 significant digits. This is good enough for most uses.

    You now have virtually all the knowledge of MySQL and numerical analysis; you decide what to do.

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

Back To Top
Search