Real Numbers – MySQL Data Type

Real numbers are numbers that have a fractional part. However, they aren’t just for fractional numbers; you can also use DECIMAL to store integers that are so large they don’t fit in BIGINT. MySQL supports both exact and inexact types.

The FLOAT and DOUBLE types support approximate calculations with standard floatingpoint math. If you need to know exactly how floating-point results are calculated, you will need to research your platform’s floating-point implementation.

The DECIMAL type is for storing exact fractional numbers. In MySQL 5.0 and newer, the DECIMAL type supports exact math. MySQL 4.1 and earlier used floating-point math to perform computations on DECIMAL values, which could give strange results because of loss of precision. In these versions of MySQL, DECIMAL was only a “storage type.”

The server itself performs DECIMAL math in MySQL 5.0 and newer, because CPUs don’t support the computations directly. Floating-point math is somewhat faster, because the CPU performs the computations natively.

Both floating-point and DECIMAL types let you specify a precision. For a DECIMAL column, you can specify the maximum allowed digits before and after the decimal point. This influences the column’s space consumption. MySQL 5.0 and newer pack the digits
into a binary string (nine digits per four bytes). For example, DECIMAL(18, 9) will store nine digits from each side of the decimal point, using nine bytes in total: four for the digits before the decimal point, one for the decimal point itself, and four for the digits after the decimal point.

A DECIMAL number in MySQL 5.0 and newer can have up to 65 digits. Earlier MySQL versions had a limit of 254 digits and stored the values as unpacked strings (one byte per digit). However, these versions of MySQL couldn’t actually use such large numbers in computations, because DECIMAL was just a storage format; DECIMAL numbers were converted to DOUBLEs for computational purposes,

You can specify a floating-point column’s desired precision in a couple of ways, which can cause MySQL to silently choose a different data type or to round values when you store them. These precision specifiers are nonstandard, so we suggest that you specify the type you want but not the precision.

Floating-point types typically use less space than DECIMAL to store the same range of values. A FLOAT column uses four bytes of storage. DOUBLE consumes eight bytes and has greater precision and a larger range of values. As with integers, you’re choosing only the storage type; MySQL uses DOUBLE for its internal calculations on floatingpoint types.

Because of the additional space requirements and computational cost, you should use DECIMAL only when you need exact results for fractional numbers—for example, when storing financial data.

Source of Information : OReIlly High.Performance MySQL Second.Edition


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner