<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="numeric-type-attributes">
      </a>
      13.1.6 Numeric Type Attributes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045213993056">
  </a>
  <a class="indexterm" name="idm46045213991984">
  </a>
  <a class="indexterm" name="idm46045213990912">
  </a>
  <p>
   MySQL supports an extension for optionally specifying the
        display width of integer data types in parentheses following the
        base keyword for the type. For example,
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT(4)
    </code>
   </a>
   specifies an
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   with a display width of four
        digits. This optional display width may be used by applications
        to display integer values having a width less than the width
        specified for the column by left-padding them with spaces. (That
        is, this width is present in the metadata returned with result
        sets. Whether it is used is up to the application.)
  </p>
  <p>
   The display width does
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   constrain the
        range of values that can be stored in the column. Nor does it
        prevent values wider than the column display width from being
        displayed correctly. For example, a column specified as
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     SMALLINT(3)
    </code>
   </a>
   has the usual
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     SMALLINT
    </code>
   </a>
   range of
   <code class="literal">
    -32768
   </code>
   to
   <code class="literal">
    32767
   </code>
   , and
        values outside the range permitted by three digits are displayed
        in full using more than three digits.
  </p>
  <p>
   When used in conjunction with the optional (nonstandard)
   <code class="literal">
    ZEROFILL
   </code>
   attribute, the default padding of
        spaces is replaced with zeros. For example, for a column
        declared as
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT(4) ZEROFILL
    </code>
   </a>
   , a
        value of
   <code class="literal">
    5
   </code>
   is retrieved as
   <code class="literal">
    0005
   </code>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <code class="literal">
     ZEROFILL
    </code>
    attribute is ignored for
          columns involved in expressions or
    <a class="link" href="union.html" title="15.2.18 UNION Clause">
     <code class="literal">
      UNION
     </code>
    </a>
    queries.
   </p>
   <p>
    If you store values larger than the display width in an
          integer column that has the
    <code class="literal">
     ZEROFILL
    </code>
    attribute, you may experience problems when MySQL generates
          temporary tables for some complicated joins. In these cases,
          MySQL assumes that the data values fit within the column
          display width.
   </p>
  </div>
  <p>
   The
   <code class="literal">
    ZEROFILL
   </code>
   attribute is deprecated for
        numeric data types, as is the display width attribute for
        integer data types. You should expect support for
   <code class="literal">
    ZEROFILL
   </code>
   and display widths for integer data
        types to be removed in a future version of MySQL. Consider using
        an alternative means of producing the effect of these
        attributes. For example, applications can use the
   <a class="link" href="string-functions.html#function_lpad">
    <code class="literal">
     LPAD()
    </code>
   </a>
   function to zero-pad
        numbers up to the desired width, or they can store the formatted
        numbers in
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     CHAR
    </code>
   </a>
   columns.
  </p>
  <p>
   All integer types can have an optional (nonstandard)
   <code class="literal">
    UNSIGNED
   </code>
   attribute. An unsigned type can be
        used to permit only nonnegative numbers in a column or when you
        need a larger upper numeric range for the column. For example,
        if an
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   column is
   <code class="literal">
    UNSIGNED
   </code>
   , the size of the column's range is
        the same but its endpoints shift up, from
   <code class="literal">
    -2147483648
   </code>
   and
   <code class="literal">
    2147483647
   </code>
   to
   <code class="literal">
    0
   </code>
   and
   <code class="literal">
    4294967295
   </code>
   .
  </p>
  <p>
   Floating-point and fixed-point types also can be
   <code class="literal">
    UNSIGNED
   </code>
   . As with integer types, this
        attribute prevents negative values from being stored in the
        column. Unlike the integer types, the upper range of column
        values remains the same.
   <code class="literal">
    UNSIGNED
   </code>
   is
        deprecated for columns of type
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     FLOAT
    </code>
   </a>
   ,
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   , and
   <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>
   (and any synonyms) and
        you should expect support for it to be removed in a future
        version of MySQL. Consider using a simple
   <code class="literal">
    CHECK
   </code>
   constraint instead for such columns.
  </p>
  <p>
   If you specify
   <code class="literal">
    ZEROFILL
   </code>
   for a numeric column,
        MySQL automatically adds the
   <code class="literal">
    UNSIGNED
   </code>
   attribute.
  </p>
  <p>
   Integer or floating-point data types can have the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   attribute. When you insert a
        value of
   <code class="literal">
    NULL
   </code>
   into an indexed
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column, the column is set to
        the next sequence value. Typically this is
   <code class="literal">
    <em class="replaceable">
     <code>
      value
     </code>
    </em>
    +1
   </code>
   , where
   <em class="replaceable">
    <code>
     value
    </code>
   </em>
   is the largest value for the
        column currently in the table.
        (
   <code class="literal">
    AUTO_INCREMENT
   </code>
   sequences begin with
   <code class="literal">
    1
   </code>
   .)
  </p>
  <p>
   Storing
   <code class="literal">
    0
   </code>
   into an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column has the same effect as
        storing
   <code class="literal">
    NULL
   </code>
   , unless the
   <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
    <code class="literal">
     NO_AUTO_VALUE_ON_ZERO
    </code>
   </a>
   SQL mode
        is enabled.
  </p>
  <p>
   Inserting
   <code class="literal">
    NULL
   </code>
   to generate
   <code class="literal">
    AUTO_INCREMENT
   </code>
   values requires that the
        column be declared
   <code class="literal">
    NOT NULL
   </code>
   . If the column is
        declared
   <code class="literal">
    NULL
   </code>
   , inserting
   <code class="literal">
    NULL
   </code>
   stores a
   <code class="literal">
    NULL
   </code>
   . When
        you insert any other value into an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column, the column is set to
        that value and the sequence is reset so that the next
        automatically generated value follows sequentially from the
        inserted value.
  </p>
  <p>
   Negative values for
   <code class="literal">
    AUTO_INCREMENT
   </code>
   columns
        are not supported.
  </p>
  <p>
   <code class="literal">
    CHECK
   </code>
   constraints cannot refer to columns
        that have the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   attribute, nor
        can the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   attribute be added to
        existing columns that are used in
   <code class="literal">
    CHECK
   </code>
   constraints.
  </p>
  <p>
   <code class="literal">
    AUTO_INCREMENT
   </code>
   is deprecated for
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     FLOAT
    </code>
   </a>
   and
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   columns; you should expect
        support for it to be removed in a future version of MySQL.
        Consider removing the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   attribute from such columns to avoid potential compatibility
        issues, or convert them to an integer type.
  </p>
 </div>
 <br/>
</div>
