[FIXED] How to check if it's safe to reduce precision of a DECIMAL type in MySQL?

Issue

I have a MySQL table with columns of DECIMAL type, e.g. DECIMAL(20,4). I want to reduce the precision for this column to DECIMAL(10,2). Is there an easy way to verify that this will not cause any data loss (i.e. all existing values are within the precision)?

Solution

You are removing "mantissa" places. The new type won't be able to store values beyond 99999999.99.

Therefore, it's quite easy to verify if you'll be losing data. See if any value exceeds that limit using:

select * from my_table where my_column > 99999999.99

Edit for the decimal places:

For the 2 decimal places at the end you can check using:

select * 
from my_table
where round(my_column * 100) <> my_column * 100

or:

select
from my_table
where right(format(my_column, 4), 2) <> '00'

Answered By - The Impaler

Answer Checked By - Gilberto Lyons (PHPFixing Admin)