GNU Info

(mysql.info)Numeric types

---

Next: Date and time types Prev: Column types Up: Column types

---

Enter node , (file) or (file)node

Numeric Types
-------------

MySQL supports all of the ANSI/ISO SQL92 numeric types.  These types
include the exact numeric data types (`NUMERIC', `DECIMAL', `INTEGER',
and `SMALLINT'), 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'.

The `NUMERIC' and `DECIMAL' types are implemented as the same type by
MySQL, as permitted by the SQL92 standard.  They are used for values
for which it is important to preserve exact precision, for example with
monetary data.  When declaring a column of one of these types the
precision and scale can be (and usually is) specified; for example:

         salary DECIMAL(9,2)

In this example, `9' (`precision') represents the number of significant
decimal digits that will be stored for values, and `2' (`scale')
represents the number of digits that will be stored following the
decimal point.  In this case, therefore, the range of values that can
be stored in the `salary' column is from `-9999999.99' to `9999999.99'.
(MySQL can actually store numbers up to `9999999.99' in this column
because it doesn't have to store the sign for positive numbers)

In ANSI/ISO SQL92, the syntax `DECIMAL(p)' is equivalent to
`DECIMAL(p,0)'.  Similarly, the syntax `DECIMAL' is equivalent to
`DECIMAL(p,0)', where the implementation is allowed to decide the value
of `p'.  MySQL does not currently support either of these variant forms
of the `DECIMAL'/`NUMERIC' data types.  This is not generally a serious
problem, as the principal benefits of these types derive from the
ability to control both precision and scale explicitly.

`DECIMAL' and `NUMERIC' values are stored as strings, rather than as
binary floating-point numbers, in order to preserve the decimal
precision of those values.  One character is used for each digit of the
value, the decimal point (if `scale' > 0), and the `-' sign (for
negative numbers).  If `scale' is 0, `DECIMAL' and `NUMERIC' values
contain no decimal point or fractional part.

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 rounded to that `scale'.  When a `DECIMAL' or `NUMERIC'
column is assigned a value whose magnitude exceeds the range implied by
the specified (or defaulted) `precision' and `scale', MySQL stores the
value representing the corresponding end point of that range.

As an extension to the ANSI/ISO SQL92 standard, MySQL also supports the
integral types `TINYINT', `MEDIUMINT', and `BIGINT' as listed in the
tables above.  Another extension is supported by MySQL for optionally
specifying the display width of an integral value in parentheses
following the base keyword for the type (for example, `INT(4)').  This
optional width specification is used to left-pad the display of values
whose width is less than the width specified for the column, but does
not constrain the range of values that can be stored in the column, nor
the number of digits that will be displayed for values whose width
exceeds that specified for the column.  When used in conjunction with
the optional extension attribute `ZEROFILL', the default padding of
spaces is replaced with zeroes.  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, as in these cases MySQL trusts that the data
did fit into the original column width.

All integral types can have an optional (non-standard) attribute
`UNSIGNED'.  Unsigned values can be used when you want to allow only
positive numbers in a column and you need a little bigger numeric range
for the column.

The `FLOAT' type is used to represent approximate numeric data types.
The ANSI/ISO SQL92 standard allows an optional specification of the
precision (but not the range of the exponent) in bits following the
keyword `FLOAT' in parentheses.  The MySQL implementation also supports
this optional precision specification.  When the keyword `FLOAT' is
used for a column type without a precision specification, MySQL uses
four bytes to store the values.  A variant syntax is also supported,
with two numbers given in parentheses following the `FLOAT' keyword.
With this option, the first number continues to represent the storage
requirements for the value in bytes, and the second number specifies
the number of digits to be stored and displayed following the decimal
point (as with `DECIMAL' and `NUMERIC').  When MySQL is asked to store
a number for such a column with more decimal digits following the
decimal point than specified for the column, the value is rounded to
eliminate the extra digits when the value is stored.

The `REAL' and `DOUBLE PRECISION' types do not accept precision
specifications.  As an extension to the ANSI/ISO SQL92 standard, MySQL
recognizes `DOUBLE' as a synonym for the `DOUBLE PRECISION' type.  In
contrast with the standard's requirement that the precision for `REAL'
be smaller than that used for `DOUBLE PRECISION', MySQL implements both
as 8-byte double-precision floating-point values (when not running in
"ANSI mode").  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 decimal
points.

When asked to store a value in a numeric column that is outside the
column type's allowable range, MySQL clips the value to the appropriate
endpoint of the range and stores the resulting value instead.

For example, the range of an `INT' column is `-2147483648' to
`2147483647'.  If you try to insert `-9999999999' into an `INT' column,
the value is clipped to the lower endpoint of the range, and
`-2147483648' is stored instead. Similarly, if you try to insert
`9999999999', `2147483647' is stored instead.

If the `INT' column is `UNSIGNED', the size of the column's range is
the same but its endpoints shift up to `0' and `4294967295'.  If you
try to store `-9999999999' and `9999999999', the values stored in the
column become `0' and `4294967296'.

Conversions that occur due to clipping are reported as "warnings" for
`ALTER TABLE', `LOAD DATA INFILE', `UPDATE', and multi-row `INSERT'
statements.