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!

CodePudding user response：

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 16777216 (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.

CodePudding user response：

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)