Databases Reference
In-Depth Information
Real Numbers
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 floating-
point 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 significantly 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
DOUBLE
s 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 than
FLOAT
. As with integers, you're
choosing only the storage type; MySQL uses
DOUBLE
for its internal calculations on
floating-point 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. But in some high-volume cases it actually makes sense to use a
BIGINT
instead, and store the data as some multiple of the smallest fraction of currency
