0

I am trying to change default value of float data type in mysql I have many tables with float value but wherever my data is more than 5 values it is rounding the values.

For Example I have this value:

2254798

And it is converting it to:

2254800

I have many table with these problems is there any way I can retain exact value. When I am changing float length to 25 it is working fine. Is there any way I can set the default float value to 25 length for all my table in my database.

Nilay Singh
  • 2,201
  • 6
  • 31
  • 61
  • You won't be able to do it in a single query using just mysql. As you will need to query the information_schema table to get all the tables and columns and than run `ALTER TABLE` script based on that result. One way would be using a stored procedure and/or a cursor if you want to do it in just mysql. Or use [PHP example here](https://stackoverflow.com/a/10490243/2911633) – Igor Ilic Feb 12 '19 at 07:45

1 Answers1

0

Floating point numbers are not exact in MySQL. If you require exact storage, consider using DECIMAL with some default value:

CREATE TABLE yourTable (
    some_col DECIMAL(10,2) DEFAULT 123.45
    ...
)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • For all table is there any way I can change float to decimal for all table. I have more than 200 tables. – Nilay Singh Feb 12 '19 at 07:33
  • Look into using `ALTER TABLE` to change the column type. I don't know of a way to do this for 200 tables, not without writing a script. – Tim Biegeleisen Feb 12 '19 at 07:37