DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) precision-math-decimal-changes

Info Catalog (mysql.info) precision-math-numbers (mysql.info) precision-math (mysql.info) precision-math-expressions 
 
 21.2 `DECIMAL' Data Type Changes
 ================================
 
 This section discusses the characteristics of the `DECIMAL' data type
 (and its synonyms) as of MySQL 5.0.3, with particular regard to the
 following topics:
 
    * Maximum number of digits
 
    * Storage format
 
    * Storage requirements
 
    * The non-standard MySQL extension to the upper range of `DECIMAL'
      columns
 
 Some of these changes result in possible incompatibilities for
 applications that are written for older versions of MySQL. These
 incompatibilities are noted throughout this section.
 
 The declaration syntax for a `DECIMAL' column remains `DECIMAL(M,D)',
 although the range of values for the arguments has changed somewhat:
 
    * M is the maximum number of digits (the precision). It has a range
      of 1 to 65. This introduces a possible incompatibility for older
      applications, because previous versions of MySQL allow a range of
      1 to 254.
 
      The precision of 65 digits actually applies as of MySQL 5.0.6.
      From 5.0.3 to 5.0.5, the precision is 64 digits.
 
    * D is the number of digits to the right of the decimal point (the
      scale). It has a range of 0 to 30 and must be no larger than M.
 
 The maximum value of 65 for M means that calculations on `DECIMAL'
 values are accurate up to 65 digits. This limit of 65 digits of
 precision also applies to exact-value numeric literals, so the maximum
 range of such literals is different from before. (Prior to MySQL 5.0.3,
 decimal values could have up to 254 digits. However, calculations were
 done using floating-point and thus were approximate, not exact.) This
 change in the range of literal values is another possible source of
 incompatibility for older applications.
 
 Values for `DECIMAL' columns no longer are represented as strings that
 require one byte per digit or sign character. Instead, a binary format
 is used that packs nine decimal digits into four bytes. This change to
 `DECIMAL' storage format changes the storage requirements as well. The
 storage requirements for the integer and fractional parts of each value
 are determined separately. Each multiple of nine digits requires four
 bytes, and the "leftover" digits require some fraction of four bytes.
 For example, a `DECIMAL(18,9)' column has nine digits on either side of
 the decimal point, so the integer part and the fractional part each
 require four bytes. A `DECIMAL(20,10)' column has ten digits on either
 side of the decimal point. Each part requires four bytes for nine of
 the digits, and one byte for the remaining digit.
 
 The storage required for leftover digits is given by the following
 table:
 
 *Leftover Digits*  *Number of Bytes*
 0                  0
 1                  1
 2                  1
 3                  2
 4                  2
 5                  3
 6                  3
 7                  4
 8                  4
 9                  4
 
 As a result of the change from string to numeric format for `DECIMAL'
 storage, `DECIMAL' columns no longer store a leading `+' character or
 leading `0' digits. Before MySQL 5.0.3, if you inserted `+0003.1' into a
 `DECIMAL(5,1)' column, it was stored as `+0003.1'. As of MySQL 5.0.3,
 it is stored as `3.1'. Applications that rely on the older behavior
 must be modified to account for this change.
 
 The change of storage format also means that `DECIMAL' columns no
 longer support the non-standard extension that allowed values larger
 than the range implied by the column definition. Formerly, one byte was
 allocated for storing the sign character. For positive values that
 needed no sign byte, MySQL allowed an extra digit to be stored instead.
 For example, a `DECIMAL(3,0)' column must support a range of at least `
 -999' to `999', but MySQL would allow storing values from `1000' to
 `9999' as well, by using the sign byte to store an extra digit. This
 extension to the upper range of `DECIMAL' columns no longer is allowed.
 In MySQL 5.0.3 and up, a `DECIMAL(M,D)' column allows at most M−D
 digits to the left of the decimal point. This can result in an
 incompatibility if an application has a reliance on MySQL allowing
 "too-large" values.
 
 The SQL standard requires that the precision of `NUMERIC(M,D)' be
 _exactly_ M digits. For `DECIMAL(M,D)', the standard requires a
 precision of at least M digits but allows more. In MySQL, `DECIMAL(M,D)'
 and `NUMERIC(M,D)' are the same, and both have a precision of exactly M
 digits.
 
 Summary of incompatibilities:
 
 The following list summarizes the incompatibilities that result from
 changes to `DECIMAL' column and value handling. You can use it as guide
 when porting older applications for use with MySQL 5.0.3 and up.
 
    * For `DECIMAL(M,D)', the maximum M is 65, not 254.
 
    * Calculations involving exact-value decimal numbers are accurate to
      65 digits. This is fewer than the maximum number of digits allowed
      before MySQL 5.0.3 (254 digits), but the exact-value precision is
      greater. Calculations formerly were done with double-precision
      floating-point, which has a precision of 52 bits (about 15 decimal
      digits).
 
    * The non-standard MySQL extension to the upper range of `DECIMAL'
      columns no longer is supported.
 
    * Leading ‘`+'’ and ‘`0'’ characters are not stored.
Info Catalog (mysql.info) precision-math-numbers (mysql.info) precision-math (mysql.info) precision-math-expressions
automatically generated byinfo2html