0
Completed

MySQL: decimal/double/float precision boundaries

Jadaw1n 9 years ago updated by rahul kumar 5 years ago 4
Display an error if a field has a larger decimal place value than the whole number part.

CREATE TABLE test (
something decimal(2,3) NOT NULL
)
MySQL said:
#1427 - For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'something'). 
GOOD, I'M SATISFIED
Satisfaction mark by Jadaw1n 9 years ago
Planned
You're right. We'll add this validation.
Completed
The validation is now available in Vertabelo.

Precision is the main difference where double is a double precision (64 bit) floating point data type and decimal is a 128-bit floating point data type.



Double - 64 bit (15-16 digits)
Decimal - 128 bit (28-29 significant digits)



So Decimals have much higher precision and are usually used within monetary (financial) applications that require a high degree of accuracy. But in performance wise Decimals are slower than double and float types. Double Types are probably the most normally used data type for real values, except handling money. More about....Decimal vs Double vs FloatDecimal vs Double vs Float


Dell


+1

Decimals have much higher precision and are usually used within financial applications that require a high degree of accuracy. Decimals are much slower (up to 20X times in some tests) than a double/float. Decimals and Floats/Doubles cannot be compared without a cast whereas Floats and Doubles can. Decimals also allow the encoding or trailing zeros.


Float - 7 digits (32 bit)

Double-15-16 digits (64 bit)

Decimal -28-29 significant digits (128 bit)

The main difference is Floats and Doubles are binary floating point types and a Decimal will store the value as a floating decimal point type. So Decimals have much higher precision and are usually used within monetary (financial) applications that require a high degree of accuracy. But in performance wise Decimals are slower than double and float types.