MySQL supports all of the standard SQL numeric data types. These
types include the exact numeric data types
(INTEGER, SMALLINT,
DECIMAL, and NUMERIC), as
well as the approximate numeric data types
(FLOAT, REAL, and
DOUBLE PRECISION). The keyword
INT is a synonym for
INTEGER, and the keyword DEC
is a synonym for DECIMAL. For numeric type
storage requirements, see Section 11.5, “Data Type Storage Requirements”.
As of MySQL 5.0.3, a BIT data type is available
for storing bit-field values. (Before 5.0.3, MySQL interprets
BIT as TINYINT(1).) In MySQL
5.0.3, BIT is supported only for
MyISAM. MySQL 5.0.5 extends
BIT support to MEMORY,
InnoDB, and BDB.
As an extension to the SQL standard, MySQL also supports the
integer types TINYINT,
MEDIUMINT, and BIGINT. The
following table shows the required storage and range for each of
the integer types.
| Type | Bytes | Minimum Value | Maximum Value |
| (Signed/Unsigned) | (Signed/Unsigned) | ||
TINYINT
|
1 |
-128
|
127
|
0
|
255
|
||
SMALLINT
|
2 |
-32768
|
32767
|
0
|
65535
|
||
MEDIUMINT
|
3 |
-8388608
|
8388607
|
0
|
16777215
|
||
INT
|
4 |
-2147483648
|
2147483647
|
0
|
4294967295
|
||
BIGINT
|
8 |
-9223372036854775808
|
9223372036854775807
|
0
|
18446744073709551615
|
Another extension is supported by MySQL for optionally specifying
the display width of an integer value in parentheses following the
base keyword for the type (for example,
INT(4)). This optional display width
specification is used to left-pad the display of values having a
width less than the width specified for the column.
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as 00004.
Note that if you store larger values than the display width in an
integer column, you may experience problems when MySQL generates
temporary tables for some complicated joins, because in these
cases MySQL assumes that the data fits into the original column
width.
Note: The
ZEROFILL attribute is stripped when a column is
involved in expressions or UNION queries.
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only non-negative numbers in a column and you need a
larger upper numeric range for the column. For example, if an
INT column is UNSIGNED, the
size of the column's range is the same but its endpoints shift
from -2147483648 and
2147483647 up to 0 and
4294967295.
Floating-point and fixed-point types also can be
UNSIGNED. As with integer types, this attribute
prevents negative values from being stored in the column. However,
unlike the integer types, the upper range of column values remains
the same.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED attribute
to the column.
For floating-point data types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
The FLOAT and DOUBLE data
types are used to represent approximate numeric data values. For
FLOAT the SQL standard allows an optional
specification of the precision (but not the range of the exponent)
in bits following the keyword FLOAT in
parentheses. MySQL also supports this optional precision
specification, but the precision value is used only to determine
storage size. A precision from 0 to 23 results in a four-byte
single-precision FLOAT column. A precision from
24 to 53 results in an eight-byte double-precision
DOUBLE column.
MySQL allows a non-standard syntax:
FLOAT(M,D)
or
REAL(M,D)
or DOUBLE
PRECISION(M,D).
Here,
“(M,D)”
means than values are displayed with up to
M digits in total, of which
D digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009 into a FLOAT(7,4)
column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension).
MySQL also treats REAL as a synonym for
DOUBLE PRECISION (a non-standard variation),
unless the REAL_AS_FLOAT SQL mode is enabled.
For maximum portability, code requiring storage of approximate
numeric data values should use FLOAT or
DOUBLE PRECISION with no specification of
precision or number of digits.
The DECIMAL and NUMERIC data
types are used to store exact numeric data values. In MySQL,
NUMERIC is implemented as
DECIMAL. These types are used to store values
for which it is important to preserve exact precision, for example
with monetary data.
As of MySQL 5.0.3, DECIMAL and
NUMERIC values are stored in binary format.
Previously, they were stored as strings, with one character used
for each digit of the value, the decimal point (if the scale is
greater than 0), and the ‘-’ sign
(for negative numbers). See Chapter 21, Precision Math.
When declaring a DECIMAL or
NUMERIC column, the precision and scale can be
(and usually is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and
2 is the scale. The precision represents the
number of significant digits that are stored for values, and the
scale represents the number of digits that can be stored following
the decimal point. If the scale is 0, DECIMAL
and NUMERIC values contain no decimal point or
fractional part.
Standard SQL requires that the salary column be
able to store any value with five digits and two decimals. In this
case, therefore, the range of values that can be stored in the
salary column is from
-999.99 to 999.99. MySQL
enforces this limit as of MySQL 5.0.3. Before 5.0.3, on the
positive end of the range, the column could actually store numbers
up to 9999.99. (For positive numbers, MySQL
5.0.2 and earlier used the byte reserved for the sign to extend
the upper end of the range.)
In standard SQL, the syntax
DECIMAL(M) is
equivalent to
DECIMAL(M,0).
Similarly, the syntax DECIMAL is equivalent to
DECIMAL(M,0), where
the implementation is allowed to decide the value of
M. MySQL supports both of these variant
forms of the DECIMAL and
NUMERIC syntax. The default value of
M is 10.
The maximum number of digits for DECIMAL or
NUMERIC is 65 (64 from MySQL 5.0.3 to 5.0.5).
Before MySQL 5.0.3, the maximum range of
DECIMAL and NUMERIC values
is the same as for DOUBLE, but the actual range
for a given DECIMAL or
NUMERIC column can be constrained by the
precision or scale for a given column. When such a column is
assigned a value with more digits following the decimal point than
are allowed by the specified scale, the value is converted to that
scale. (The precise behavior is operating system-specific, but
generally the effect is truncation to the allowable number of
digits.)
As of MySQL 5.0.3, the BIT data type is used to
store bit-field values. A type of
BIT(M) allows for
storage of M-bit values.
M can range from 1 to 64.
To specify bit values,
b'value' notation
can be used. value is a binary value
written using zeros and ones. For example,
b'111' and b'10000000'
represent 7 and 128, respectively. See
Section 9.1.5, “Bit-Field Values”.
If you assign a value to a
BIT(M) column that
is less than M bits long, the value is
padded on the left with zeros. For example, assigning a value of
b'101' to a BIT(6) column
is, in effect, the same as assigning b'000101'.
When asked to store a value in a numeric column that is outside
the data type's allowable range, MySQL's behavior depends on the
SQL mode in effect at the time. For example, if no restrictive
modes are enabled, MySQL clips the value to the appropriate
endpoint of the range and stores the resulting value instead.
However, if the mode is set to TRADITIONAL,
MySQL rejects a value that is out of range with an error, and the
insert fails, in accordance with the SQL standard.
In non-strict mode, when an out-of-range value is assigned to an
integer column, MySQL stores the value representing the
corresponding endpoint of the column data type range. If you store
256 into a TINYINT or TINYINT
UNSIGNED column, MySQL stores 127 or 255, respectively.
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.
Conversions that occur due to clipping when MySQL is not operating
in strict mode are reported as warnings for ALTER
TABLE, LOAD DATA INFILE,
UPDATE, and multiple-row
INSERT statements. When MySQL is operating in
strict mode, these statements fail, and some or all of the values
will not be inserted or changed, depending on whether the table is
a transactional table and other factors. For details, see
Section 5.2.5, “The Server SQL Mode”.