<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="precision-math-decimal-characteristics">
      </a>
      14.24.2 DECIMAL Data Type Characteristics
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section discusses the characteristics of the
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   data type (and its
      synonyms), with particular regard to the following topics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Maximum number of digits
     </p>
    </li>
    <li class="listitem">
     <p>
      Storage format
     </p>
    </li>
    <li class="listitem">
     <p>
      Storage requirements
     </p>
    </li>
    <li class="listitem">
     <p>
      The nonstandard MySQL extension to the upper range of
      <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
       <code class="literal">
        DECIMAL
       </code>
      </a>
      columns
     </p>
    </li>
   </ul>
  </div>
  <p>
   The declaration syntax for a
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   column is
   <code class="literal">
    DECIMAL(
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      D
     </code>
    </em>
    )
   </code>
   .
      The ranges of values for the arguments are as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        M
       </code>
      </em>
      is the maximum number of digits
          (the precision). It has a range of 1 to 65.
     </p>
    </li>
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        D
       </code>
      </em>
      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
      <em class="replaceable">
       <code>
        M
       </code>
      </em>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   If
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   is omitted, the default is 0. If
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   is omitted, the default is 10.
  </p>
  <p>
   The maximum value of 65 for
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   means
      that calculations on
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   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 differs from before. (There is also a limit on
      how long the text of
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   literals can be; see
   <a class="xref" href="precision-math-expressions.html" title="14.24.3 Expression Handling">
    Section 14.24.3, “Expression Handling”
   </a>
   .)
  </p>
  <p>
   Values for
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   columns are
      stored using a binary format that packs nine decimal digits into 4
      bytes. The storage requirements for the integer and fractional
      parts of each value are determined separately. Each multiple of
      nine digits requires 4 bytes, and any remaining digits left over
      require some fraction of 4 bytes. The storage required for
      remaining digits is given by the following table.
  </p>
  <div class="informaltable">
   <table summary="The number of bytes required for remaining/leftover digits in DECIMAL values.">
    <colgroup>
     <col style="width: 25%"/>
     <col style="width: 25%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       Leftover Digits
      </th>
      <th>
       Number of Bytes
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       0
      </td>
      <td>
       0
      </td>
     </tr>
     <tr>
      <td>
       1–2
      </td>
      <td>
       1
      </td>
     </tr>
     <tr>
      <td>
       3–4
      </td>
      <td>
       2
      </td>
     </tr>
     <tr>
      <td>
       5–6
      </td>
      <td>
       3
      </td>
     </tr>
     <tr>
      <td>
       7–9
      </td>
      <td>
       4
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   For example, a
   <code class="literal">
    DECIMAL(18,9)
   </code>
   column has nine
      digits on either side of the decimal point, so the integer part
      and the fractional part each require 4 bytes. A
   <code class="literal">
    DECIMAL(20,6)
   </code>
   column has fourteen integer
      digits and six fractional digits. The integer digits require four
      bytes for nine of the digits and 3 bytes for the remaining five
      digits. The six fractional digits require 3 bytes.
  </p>
  <p>
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   columns do not store a
      leading
   <code class="literal">
    +
   </code>
   character or
   <code class="literal">
    -
   </code>
   character or leading
   <code class="literal">
    0
   </code>
   digits. If you insert
   <code class="literal">
    +0003.1
   </code>
   into a
   <code class="literal">
    DECIMAL(5,1)
   </code>
   column, it is stored as
   <code class="literal">
    3.1
   </code>
   . For negative
      numbers, a literal
   <code class="literal">
    -
   </code>
   character is not stored.
  </p>
  <p>
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   columns do not permit
      values larger than the range implied by the column definition. For
      example, a
   <code class="literal">
    DECIMAL(3,0)
   </code>
   column supports a range
      of
   <code class="literal">
    -999
   </code>
   to
   <code class="literal">
    999
   </code>
   . A
   <code class="literal">
    DECIMAL(
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      D
     </code>
    </em>
    )
   </code>
   column permits up to
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   -
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   digits to the left of the decimal
      point.
  </p>
  <p>
   The SQL standard requires that the precision of
   <code class="literal">
    NUMERIC(
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      D
     </code>
    </em>
    )
   </code>
   be
   <span class="emphasis">
    <em>
     exactly
    </em>
   </span>
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   digits. For
   <code class="literal">
    DECIMAL(
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      D
     </code>
    </em>
    )
   </code>
   ,
      the standard requires a precision of at least
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   digits but permits more. In MySQL,
   <code class="literal">
    DECIMAL(
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      D
     </code>
    </em>
    )
   </code>
   and
   <code class="literal">
    NUMERIC(
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      D
     </code>
    </em>
    )
   </code>
   are the same, and both have a precision of exactly
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   digits.
  </p>
  <p>
   For a full explanation of the internal format of
   <code class="literal">
    DECIMAL
   </code>
   values, see the file
   <code class="filename">
    strings/decimal.c
   </code>
   in a MySQL source
      distribution. The format is explained (with an example) in the
   <code class="literal">
    decimal2bin()
   </code>
   function.
  </p>
 </div>
 <br/>
</div>
