I have a table contain column float(10,5) and decimal(10,5)
I update the table to be:
update table1
set decimaltesting = 12311123123123123.456789,
floattesting = 12311123123123123.456789;
The result become:
decimal = 99999.99999
float = 100000.00000
but if I change column to float(6,3) and decimal(6,3) and update it with the same value as above
The result become:
float = 999.999
decimal = 999.999
Why for the first scenario, float value will be 100000.00000?
Garbage in - garbage out
Remember this simple rule. Your statement will not be correct in common case - since, for example, with strict sql mode it will fail with error. But since you're getting results as you've described, I assume you have allowed such behavior. The correct point is - that your application is aware about it's models data types - and so such things are under control - and you're not passing garbage to your DBMS, resulting with garbage as well.
Always about precision
Your sample is a good demonstration about - what's the difference between fixed-point and floating-point data types. First thing - according to manual page:
When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.
-that is why you're seeing that 999.999 and so on. But - what about strange 100000.00000? The hint is - that you can see this only with floating point field. Why? Because MySQL will convert your out-of-range value to maximum allowed by your field, but, since floating point values are stored approximately, there's no guarantee that final value will hold precision - and, therefore, it may result in your 100000.00000 value. Note, that such thing will never happen with fixed-point data type.
