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(``M`

,`D`

)

or ```
DOUBLE
PRECISION(
````M`

,`D`

)

.
Here,
“`(``M`

,`D`

)

”
means than values can be stored with up to
*digits in total, of which*

`M`

*digits may be after the decimal point. For example, a column defined as*

`D`

`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(``M`

,0)

.
Similarly, the syntax `DECIMAL`

is
equivalent to
`DECIMAL(``M`

,0)

, where
the implementation is permitted to decide the value of
*. MySQL supports both of these variant forms of*

`M`

`DECIMAL`

syntax. The
default value of *is 10.*

`M`

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*)

*-bit values.*

`M`

*can range from 1 to 64.*

`M`

To specify bit values,
`b'`

notation
can be used. * value*'

*is a binary value written using zeros and ones. For example,*

`value`

`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*)

*bits long, the value is padded on the left with zeros. For example, assigning a value of*

`M`

`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*+1

*is the largest value for the column currently in the table.*

`value`

`AUTO_INCREMENT`

sequences begin with `1`

.