(mysql.info.gz) Numeric types

Info Catalog

(mysql.info.gz) Column type overview

(mysql.info.gz) Column types

(mysql.info.gz) Date and time 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'.
 
 As an extension to the SQL standard, MySQL also supports the integer
 types `TINYINT', `MEDIUMINT', and `BIGINT' as listed in the following
 table.
 
 *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 will be 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 trusts that the data did fit 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 bigger upper numeric
 range for the column.
 
 As of MySQL 4.0.2, 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.
 
 The `DECIMAL' and `NUMERIC' types are implemented as the same type by
 MySQL.  They are used to store 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(5,2)
 
 In this example, `5' is the precision and `2' is the scale.  The
 precision represents the number of significant decimal digits that will
 be stored for values, and the scale represents the number of digits
 that will be stored following the decimal point.
 
 MySQL stores `DECIMAL' and `NUMERIC' values 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 the scale is greater than 0), and the `-'
 sign (for negative numbers).  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 varies from this in two ways:
 
    * On the positive end of the range, the column actually can store
      numbers up to `9999.99'. For positive numbers, MySQL uses the byte
      reserved for the sign to extend the upper end of the range.
 
    * `DECIMAL' columns in MySQL before 3.23 are stored differently and
      cannot represent all the values required by standard SQL. This is
      because for a type of `DECIMAL(M,D)', the value of M includes the
      bytes for the sign and the decimal point.  The range of the
      `salary' column before MySQL 3.23 would be `-9.99' to `99.99'.
 
 
 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.  As of MySQL 3.23.6, both of these variant forms of the `DECIMAL'
 and `NUMERIC' data types are supported.  The default value of M is 10.
 Before 3.23.6, M and D both must be specified explicitly.
 
 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.)  When a `DECIMAL' or `NUMERIC' 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 end point of that range.
 
 For floating-point column types, MySQL uses four bytes for
 single-precision values and eight bytes for double-precision values.
 
 The `FLOAT' type is used to represent approximate numeric data types.
 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.  The MySQL implementation 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 four-byte
 single-precision `FLOAT' column. A precision from 24 to 53 results in
 eight-byte double-precision `DOUBLE' column.
 
 When the keyword `FLOAT' is used for a column type without a precision
 specification, MySQL uses four bytes to store the values.  MySQL also
 supports variant syntax with two numbers given in parentheses following
 the `FLOAT' keyword.  The first number represents the display width 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.
 
 In standard SQL, the `REAL' and `DOUBLE PRECISION' types do not accept
 precision specifications.  MySQL supports a variant syntax with two
 numbers given in parentheses following the type name.  The first number
 represents the display width and the second number specifies the number
 of digits to be stored and displayed following the decimal point.  As
 an extension to the SQL 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 eight-byte
 double-precision floating-point values (unless the server SQL mode
 includes the `REAL_AS_FLOAT' option).
 
 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,
 MySQL clips the value to the lower endpoint of the range and stores
 `-2147483648' instead. Similarly, if you try to insert `9999999999',
 MySQL clips the value to the upper endpoint of the range and stores
 `2147483647' 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 are `0' and `4294967296'.
 
 Conversions that occur due to clipping are reported as "warnings" for
 `ALTER TABLE', `LOAD DATA INFILE', `UPDATE', and multiple-row `INSERT'
 statements.
 

Info Catalog

(mysql.info.gz) Column type overview

(mysql.info.gz) Column types

(mysql.info.gz) Date and time types