MySQL supports all 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 keywords
DEC
and FIXED
are synonyms for DECIMAL
. MySQL
treats DOUBLE
as a synonym for
DOUBLE PRECISION
(a nonstandard
extension). MySQL also treats REAL
as a synonym for DOUBLE PRECISION
(a nonstandard variation), unless the
REAL_AS_FLOAT
SQL mode is
enabled.
The BIT
data type stores bit-field
values and is supported for MyISAM
,
MEMORY
, InnoDB
, and
NDBCLUSTER
tables.
For information about numeric type storage requirements, see Section 10.5, “Data Type Storage Requirements”.
The data type used for the result of a calculation on numeric operands depends on the types of the operands and the operations performed on them. For more information, see Section 11.6.1, “Arithmetic Operators”.
For information about how MySQL handles assignment of out-of-range values to columns and overflow during expression evaluation, see Section 10.6, “Out-of-Range and Overflow Handling”.
Integer Types
MySQL supports the SQL standard integer types
INTEGER
(or
INT
) and
SMALLINT
. As an extension to the
standard, MySQL also supports the integer types
TINYINT
,
MEDIUMINT
, and
BIGINT
. The following table shows
the required storage and range for each integer type.
Type | Storage | Minimum Value | Maximum Value |
---|---|---|---|
(Bytes) | (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 |
Floating-Point (Approximate-Value) Types
The FLOAT
and
DOUBLE
types represent approximate
numeric data values. MySQL uses four bytes for single-precision
values and eight bytes for double-precision values.
For FLOAT
, the SQL standard permits
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 permits a nonstandard syntax:
FLOAT(
or
M
,D
)REAL(
or M
,D
)DOUBLE
PRECISION(
.
Here,
“M
,D
)(
”
means than values can be stored with up to
M
,D
)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
.
Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section C.5.5.8, “Problems with Floating-Point Values”
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.
Fixed-Point (Exact-Value) Types
The DECIMAL
and
NUMERIC
types store exact numeric
data values. These types are used when it is important to preserve
exact precision, for example with monetary data. In MySQL,
NUMERIC
is implemented as
DECIMAL
, so the following remarks
about DECIMAL
apply equally to
NUMERIC
.
MySQL 5.5 stores
DECIMAL
values in binary format.
See Section 11.18, “Precision Math”.
In a DECIMAL
column declaration,
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.
Standard SQL requires that DECIMAL(5,2)
be able
to store any value with five digits and two decimals, so values
that can be stored in the salary
column range
from -999.99
to 999.99
.
In standard SQL, the syntax
DECIMAL(
is
equivalent to
M
)DECIMAL(
.
Similarly, the syntax M
,0)DECIMAL
is
equivalent to
DECIMAL(
, where
the implementation is permitted to decide the value of
M
,0)M
. MySQL supports both of these variant
forms of DECIMAL
syntax. The
default value of M
is 10.
If the scale is 0, DECIMAL
values
contain no decimal point or fractional part.
The maximum number of digits for
DECIMAL
is 65, but the actual range
for a given DECIMAL
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 permitted 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
permissible number of digits.)
Bit-Value Type
The BIT
data type is used to store
bit-field values. A type of
BIT(
enables
storage of M
)M
-bit values.
M
can range from 1 to 64.
To specify bit values,
b'
notation
can be used. value
'value
is a binary value
written using zeros and ones. For example,
b'111'
and b'10000000'
represent 7 and 128, respectively. See
Section 8.1.6, “Bit-Field Values”.
If you assign a value to a
BIT(
column that
is less than M
)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'
.
Numeric Type Attributes
MySQL supports an extension for optionally specifying the display
width of integer data types in parentheses following the base
keyword for the type. For example,
INT(4)
specifies an
INT
with a display width of four
digits. This optional display width may be used by applications to
display integer values having a width less than the width
specified for the column by left-padding them with spaces. (That
is, this width is present in the metadata returned with result
sets. Whether it is used or not is up to the application.)
The display width does not constrain the
range of values that can be stored in the column. Nor does it
prevent values wider than the column display width from being
displayed correctly. For example, a column specified as
SMALLINT(3)
has the usual
SMALLINT
range of
-32768
to 32767
, and values
outside the range permitted by three digits are displayed in full
using more than three digits.
When used in conjunction with the optional (nonstandard) attribute
ZEROFILL
, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(4) ZEROFILL
, a value of
5
is retrieved as 0005
.
The ZEROFILL
attribute is ignored when a
column is involved in expressions or
UNION
queries.
If you store values larger than the display width in an integer
column that has the ZEROFILL
attribute, you
may experience problems when MySQL generates temporary tables
for some complicated joins. In these cases, MySQL assumes that
the data values fit within the column display width.
All integer types can have an optional (nonstandard) attribute
UNSIGNED
. Unsigned type can be used to permit
only nonnegative numbers in a column or when 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. 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.
Integer or floating-point data types can have the additional
attribute AUTO_INCREMENT
. When you insert a
value of NULL
(recommended) or
0
into an indexed
AUTO_INCREMENT
column, the column is set to the
next sequence value. Typically this is
, where
value
+1value
is the largest value for the
column currently in the table. AUTO_INCREMENT
sequences begin with 1
.