alexander7567 alexander7567 - 1 year ago 50
MySQL Question

Data truncated for column when multiplying numbers

I am using the below query on the below data. However, when I do this I am getting the error "Data truncated for column 'strength' at row 1" for every column and row. I researched it a little, and as far as I can tell, most people are getting this error because they are trying to use text or char. I have never seen this warning before and I am getting the expected results, but with 4,700 warnings.

UPDATE userstats
SET strength = (strength * .999),
agility = (agility * .999),
guard = (guard * .999),
labour = (labour * .999),
IQ = (IQ * .999)
WHERE gym_train_since_cron = 0

Database columns
Database rows

Any help would be greatly appreciated!

Answer Source

strength * .999 is resulting in a number with more than 4 decimal places. That goes for all of these calculations.

To avoid the warnings, you can either ROUND or TRUNCATE the result of the calculation. For example: SET strength = ROUND(strength * .999, 4) or SET strength = TRUNCATE(strength * .999, 4).

You may wonder what the difference between the two functions are; it's the rounding behavior. For TRUNCATE it will round a number towards 0, whereas ROUND, depending on the numeric data type (exact or approximate) which in your case is decimal (an exact type), the following occurs (taken from Rounding Behavior):

For exact-value numbers, ROUND() uses the “round half up” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

To demonstrate, here's an example using the strength value for user 4898 from your sample data:

strength * .999 = 16331143.7521566 -- Over 4 decimal places, hence the warning
ROUND(strength * .999, 4) = 16331143.7522 -- Rounds up
TRUNCATE(strength * .999, 4) = 16331143.7521 -- Rounds down