Skip navigation links

### Note

MySQL 5.1 Reference Manual :: 10 Data Types :: 10.2 Numeric Types

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 10.5, “Data Type Storage Requirements”.

The numeric types used for the results of calculations depends on the operations being performed and the numeric types of the operands; for more information, see Section 11.5.1, “Arithmetic Operators”.

The `BIT`

data type stores bit-field
values and is supported for `MyISAM`

,
`MEMORY`

, `InnoDB`

, and
`NDBCLUSTER`

tables.

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 integer data types in parentheses following
the base keyword for the type (for example,
`INT(4)`

). 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 the number
of digits that are displayed for values having a width exceeding
that specified for the column. For example, a column specified as
`SMALLINT(3)`

has the usual
`SMALLINT`

range of
`-32768`

to `32767`

, and values
outside the range allowed by three characters are displayed using
more than three characters.

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.

The `ZEROFILL`

attribute is ignored when a
column is involved in expressions or
`UNION`

queries.

All integer types can have an optional (nonstandard) attribute
`UNSIGNED`

. Unsigned values can be used when you
want to allow only nonnegative 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.

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

`value`

`AUTO_INCREMENT`

sequences begin with `1`

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

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

.
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.

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 B.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.

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.

MySQL 5.1 stores
`DECIMAL`

and
`NUMERIC`

values in binary format.
Before MySQL 5.0.3, they were stored as strings. See
Section 11.14, “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`

.

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 allowed to decide the value of
`M`

`DECIMAL`

and
`NUMERIC`

syntax. The default value
of `M`

The maximum number of digits for
`DECIMAL`

or
`NUMERIC`

is 65, 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.)

The `BIT`

data type is used to store
bit-field values. A type of
`BIT(`

allows for
storage of * M*)

`M`

`M`

To specify bit values,
`b'`

notation
can be used. * value*'

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

`M`

`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 strict SQL mode is enabled, MySQL rejects a value that is out of range with an error, and the insert fails, in accordance with the SQL standard.

In nonstrict 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.

Subtraction between integer values, where one is of type
`UNSIGNED`

, produces an unsigned result by
default. If the result would otherwise have been negative, it
becomes the maximum integer value. If the
`NO_UNSIGNED_SUBTRACTION`

SQL mode
is enabled, the result is negative.

mysql>mysql>`SET SQL_MODE = '';`

+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | 18446744073709551615 | +-------------------------+ mysql>`SELECT CAST(0 AS UNSIGNED) - 1;`

mysql>`SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';`

+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+`SELECT CAST(0 AS UNSIGNED) - 1;`

If the result of such an operation is used to update an
`UNSIGNED`

integer column, the result is clipped
to the maximum value for the column type, or clipped to 0 if
`NO_UNSIGNED_SUBTRACTION`

is
enabled. If strict SQL mode is enabled, an error occurs and the
column remains unchanged.

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.1.8, “Server SQL Modes”.

## User Comments

MySQL Float and Real values do not appear to handle all the IEEE standard floating point representations such as NaN, and +/- Inf. Special accommodations are needed to avoid accidentally inserting 0's for these values when integrating a MySQL database with a scientific application that generates these values.

Using the MySQL.com search feature to look for documentation on the type BOOL, this page is the highest ranked Reference Manual page that comes up. However, the term does not even appear on the page.

According to the page headed "10.1.1. Overview of Numeric Types", BOOL and BOOLEAN are synonyms for TINYINT(1).

I thought I should include that fact on this page, since the page does come up when searching for it. (Fixing the search feature would be a better solution, though!)

bool deafulting to tinyint is not strict enough. if you really want bool type you should use type "bit(1)" which will allow you to use exactly 1 and 0 and maybe save space or use type "enum('T','F')" which will not save space but make it a true binary flag.

Add your own comment.