How to insert float numbers from mysqldump

Question

I have sql dump file coming from FirebirdSQL. Looks same as exported from MySQL (MariaDB). In the said file there are table creation statements one of which are the following:

CREATE TABLE VALIORS (
....,
    STK_SUM      FLOAT DEFAULT 0,
    TWO_PERC     FLOAT,
....,
);

Later in the file are the following insert statements:

INSERT INTO VALIORS (...., SZK_ND,TWO_PERC, ....) values (...., 10039.349609375,200.78999328613281, ....);
INSERT INTO VALIORS (...., SZK_ND,TWO_PERC, ....) values (...., 10039.349609375,200.78999328613281, ....);

The whole file is bigger than 500 MB, so I only import it through command line on windows machine with xampp installed.

Everything else is imported correctly, but all float numbers are being rounded. My problem is I need them exact. Is there anyway to achieve it? And where the problem comes from?

After about 7 significant digits, the rest is noise. Those values probably came from 10039.35 and 200.79. — Rick James, May 14 '19 at 14:55

Salman A · Accepted Answer · 2019-04-17T13:39:33.863

3

Use DOUBLE datatype to store values with more precision; but that still does not guarantee that the values will be stored as-is. If you want to store the values as-is then use DECIMAL(m, d) datatype.

CREATE TABLE test(
    str VARCHAR(100),
    flt FLOAT,
    dbl DOUBLE,
    dcm DECIMAL(26, 16)
);
INSERT INTO test(str, flt, dbl, dcm) VALUES
    ('10039.349609375',    10039.349609375,    10039.349609375,    10039.349609375),
    ('200.78999328613281', 200.78999328613281, 200.78999328613281, 200.78999328613281);
SELECT * FROM test;

Result:

+--------------------+---------+-------------------+------------------------+
| str                | flt     | dbl               | dcm                    |
+--------------------+---------+-------------------+------------------------+
| 10039.349609375    | 10039.3 |   10039.349609375 | 10039.3496093750000000 |
| 200.78999328613281 |  200.79 | 200.7899932861328 |   200.7899932861328100 |
+--------------------+---------+-------------------+------------------------+

edited Apr 17 '19 at 13:39

answered Apr 17 '19 at 13:26

Salman A

262,204
82
430
521

Question then becomes is FirebirdSQL FLOAT type with greater prescision than MariaDB's? – Tsefo Apr 17 '19 at 13:28
One way to tell is to look at the documentation! – Salman A Apr 17 '19 at 13:31
Thank you! DOUBLE is still less precise than the exact, but works well enough. Tried DECIMAL with 20 precision and works like a charm :) As of Documentation - both Firebird and MariaDB states FLOAT is 4 bytes, but I suppose the DB creating the dump is with different type, so it dumps it with higher precision than expected. – Tsefo Apr 17 '19 at 14:02
When a float value is dumped it is converted to an approximate decimal representation of the actual value. The approximation when converted back to float creates additional error. – Salman A Apr 17 '19 at 14:10
1

Correlary: For currency, use `DECIMAL`, not `FLOAT` or `DOUBLE`. – Rick James May 14 '19 at 14:39
made "double" and that helped to solve the same issue, that was quite strange for me, that 'float' and 'decimal' round to integer – Green Joffer Jun 03 '21 at 16:27

How to insert float numbers from mysqldump

1 Answers1