(mysql.info) numeric-types

 
 11.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  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.
 
 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
  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 zeroes and ones. For example, `b'111'' and
 `b'100000000'' represent 7 and 128, respectively. See 
 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 zeroes. 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 255 or 127,
 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 
 server-sql-mode.
Info Catalog
(mysql.info) data-type-overview
(mysql.info) data-types
(mysql.info) date-and-time-types
automatically generated byinfo2html