Databases Reference
In-Depth Information
MEDIUMINT[(
width
)] [UNSIGNED] [ZEROFILL]
Stores integer (whole number) values in the range -8,388,608 to 8,388,607. The
width
,
UNSIGNED
, and
ZEROFILL
options behave as for
INT
. When
UNSIGNED
is used,
a column can store values in the range 0 to 16,777,215. A
MEDIUMINT
column re-
quires three bytes of storage space.
BIGINT[(
width
)] [UNSIGNED] [ZEROFILL]
Stores integer (whole number) values in the range -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807. The
width
,
UNSIGNED
, and
ZEROFILL
options behave as
for
INT
. When
UNSIGNED
is used, a column can store values in the range 0 to
18,446,744,073,709,551,615. A
BIGINT
column requires eight bytes of storage
space.
Other rational number types
In “Common column types,” we discussed the fixed-point
DECIMAL
type. There are two
other types that support decimal points:
DOUBLE
(also known as
REAL
) and
FLOAT
. They're
designed to store approximate numeric values rather than the exact values stored by
DECIMAL
. Why would you want approximate values? The answer is that many numbers
with a decimal point are approximations of real quantities. For example, suppose you
earn $50,000 per annum and you want to store it as a monthly wage. When you convert
it to a per-month amount, it's $4,166 plus 66 and 2/3rds cents. If you store this as
$4,166.67, it's not exact enough to convert to a yearly wage (since 12 multiplied by
$4,166.67 is $50,000.04). However, if you store 2/3rds with enough decimal places,
it's a closer approximation, and you'll find that it is accurate enough to correctly mul-
tiply to obtain the original value in a high-precision environment such as MySQL.
That's where
DOUBLE
and
FLOAT
are useful: they let you store values such as 2/3rds or
pi with a large number of decimal places, allowing accurate approximate representa-
tions of exact quantities.
Let's continue the previous example using
DOUBLE
. Suppose you create a table as follows:
mysql>
CREATE TABLE wage (monthly DOUBLE);
Query OK, 0 rows affected (0.09 sec)
You can now insert the monthly wage using:
mysql>
INSERT INTO wage VALUES (50000/12);
Query OK, 1 row affected (0.00 sec)
When you multiply it to a yearly value, you get an accurate approximation:
mysql>
SELECT monthly*12 FROM wage;
+------------+
| monthly*12 |
+------------+
| 50000 |
+------------+
1 row in set (0.00 sec)
Here are the details of the
DOUBLE
and
FLOAT
types:
