<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="enum">
      </a>
      13.3.5 The ENUM Type
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045212490160">
  </a>
  <a class="indexterm" name="idm46045212489088">
  </a>
  <p>
   An
   <code class="literal">
    ENUM
   </code>
   is a string object with a value
        chosen from a list of permitted values that are enumerated
        explicitly in the column specification at table creation time.
  </p>
  <p>
   See
   <a class="xref" href="string-type-syntax.html" title="13.3.1 String Data Type Syntax">
    Section 13.3.1, “String Data Type Syntax”
   </a>
   for
   <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
    <code class="literal">
     ENUM
    </code>
   </a>
   type syntax and length
        limits.
  </p>
  <p>
   The
   <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
    <code class="literal">
     ENUM
    </code>
   </a>
   type has these
        advantages:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Compact data storage in situations where a column has a
            limited set of possible values. The strings you specify as
            input values are automatically encoded as numbers. See
      <a class="xref" href="storage-requirements.html" title="13.7 Data Type Storage Requirements">
       Section 13.7, “Data Type Storage Requirements”
      </a>
      for storage
            requirements for the
      <code class="literal">
       ENUM
      </code>
      type.
     </p>
    </li>
    <li class="listitem">
     <p>
      Readable queries and output. The numbers are translated back
            to the corresponding strings in query results.
     </p>
    </li>
   </ul>
  </div>
  <p>
   and these potential issues to consider:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If you make enumeration values that look like numbers, it is
            easy to mix up the literal values with their internal index
            numbers, as explained in
      <a class="xref" href="enum.html#enum-limits" title="Enumeration Limitations">
       Enumeration Limitations
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Using
      <code class="literal">
       ENUM
      </code>
      columns in
      <code class="literal">
       ORDER
            BY
      </code>
      clauses requires extra care, as explained in
      <a class="xref" href="enum.html#enum-sorting" title="Enumeration Sorting">
       Enumeration Sorting
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="enum.html#enum-using" title="Creating and Using ENUM Columns">
       Creating and Using ENUM Columns
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enum.html#enum-indexes" title="Index Values for Enumeration Literals">
       Index Values for Enumeration Literals
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enum.html#enum-literals" title="Handling of Enumeration Literals">
       Handling of Enumeration Literals
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enum.html#enum-nulls" title="Empty or NULL Enumeration Values">
       Empty or NULL Enumeration Values
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enum.html#enum-sorting" title="Enumeration Sorting">
       Enumeration Sorting
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enum.html#enum-limits" title="Enumeration Limitations">
       Enumeration Limitations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enum-using">
       </a>
       Creating and Using ENUM Columns
      </h4>
     </div>
    </div>
   </div>
   <p>
    An enumeration value must be a quoted string literal. For
          example, you can create a table with an
    <code class="literal">
     ENUM
    </code>
    column like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69554908"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> shirts <span class="token punctuation">(</span>
    <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    size <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'x-small'</span><span class="token punctuation">,</span> <span class="token string">'small'</span><span class="token punctuation">,</span> <span class="token string">'medium'</span><span class="token punctuation">,</span> <span class="token string">'large'</span><span class="token punctuation">,</span> <span class="token string">'x-large'</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> shirts <span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">,</span> size<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'dress shirt'</span><span class="token punctuation">,</span><span class="token string">'large'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">'t-shirt'</span><span class="token punctuation">,</span><span class="token string">'medium'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'polo shirt'</span><span class="token punctuation">,</span><span class="token string">'small'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">name</span><span class="token punctuation">,</span> size <span class="token keyword">FROM</span> shirts <span class="token keyword">WHERE</span> size <span class="token operator">=</span> <span class="token string">'medium'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name    <span class="token punctuation">|</span> size   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> t<span class="token punctuation">-</span>shirt <span class="token punctuation">|</span> medium <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token keyword">UPDATE</span> shirts <span class="token keyword">SET</span> size <span class="token operator">=</span> <span class="token string">'small'</span> <span class="token keyword">WHERE</span> size <span class="token operator">=</span> <span class="token string">'large'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Inserting 1 million rows into this table with a value of
    <code class="literal">
     'medium'
    </code>
    would require 1 million bytes of
          storage, as opposed to 6 million bytes if you stored the
          actual string
    <code class="literal">
     'medium'
    </code>
    in a
    <code class="literal">
     VARCHAR
    </code>
    column.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enum-indexes">
       </a>
       Index Values for Enumeration Literals
      </h4>
     </div>
    </div>
   </div>
   <p>
    Each enumeration value has an index:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The elements listed in the column specification are
              assigned index numbers, beginning with 1.
      </p>
     </li>
     <li class="listitem">
      <p>
       The index value of the empty string error value is 0. This
              means that you can use the following
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statement to find
              rows into which invalid
       <code class="literal">
        ENUM
       </code>
       values
              were assigned:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48868951"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">enum_col</em><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The index of the
       <code class="literal">
        NULL
       </code>
       value is
       <code class="literal">
        NULL
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The term
       <span class="quote">
        “
        <span class="quote">
         index
        </span>
        ”
       </span>
       here refers to a position
              within the list of enumeration values. It has nothing to
              do with table indexes.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For example, a column specified as
    <code class="literal">
     ENUM('Mercury',
          'Venus', 'Earth')
    </code>
    can have any of the values shown
          here. The index of each value is also shown.
   </p>
   <div class="informaltable">
    <table summary="Possible values for a column specified as ENUM('Mercury', 'Venus', 'Earth'). The table also shows the index of each value.">
     <colgroup>
      <col style="width: 15%"/>
      <col style="width: 15%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Value
       </th>
       <th>
        Index
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <code class="literal">
         NULL
        </code>
       </td>
       <td>
        <code class="literal">
         NULL
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         ''
        </code>
       </td>
       <td>
        0
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         'Mercury'
        </code>
       </td>
       <td>
        1
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         'Venus'
        </code>
       </td>
       <td>
        2
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         'Earth'
        </code>
       </td>
       <td>
        3
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    An
    <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
     <code class="literal">
      ENUM
     </code>
    </a>
    column can have a
          maximum of 65,535 distinct elements.
   </p>
   <p>
    If you retrieve an
    <code class="literal">
     ENUM
    </code>
    value in a numeric
          context, the column value's index is returned. For example,
          you can retrieve numeric values from an
    <code class="literal">
     ENUM
    </code>
    column like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa9743022"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <em class="replaceable">enum_col</em><span class="token operator">+</span><span class="token number">0</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Functions such as
    <a class="link" href="aggregate-functions.html#function_sum">
     <code class="literal">
      SUM()
     </code>
    </a>
    or
    <a class="link" href="aggregate-functions.html#function_avg">
     <code class="literal">
      AVG()
     </code>
    </a>
    that expect a numeric
          argument cast the argument to a number if necessary. For
    <code class="literal">
     ENUM
    </code>
    values, the index number is used in
          the calculation.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enum-literals">
       </a>
       Handling of Enumeration Literals
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045212416912">
   </a>
   <p>
    Trailing spaces are automatically deleted from
    <code class="literal">
     ENUM
    </code>
    member values in the table definition
          when a table is created.
   </p>
   <p>
    When retrieved, values stored into an
    <code class="literal">
     ENUM
    </code>
    column are displayed using the lettercase that was used in the
          column definition. Note that
    <code class="literal">
     ENUM
    </code>
    columns
          can be assigned a character set and collation. For binary or
          case-sensitive collations, lettercase is taken into account
          when assigning values to the column.
   </p>
   <p>
    If you store a number into an
    <code class="literal">
     ENUM
    </code>
    column,
          the number is treated as the index into the possible values,
          and the value stored is the enumeration member with that
          index. (However, this does
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    work with
    <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
     <code class="literal">
      LOAD DATA
     </code>
    </a>
    , which treats all
          input as strings.) If the numeric value is quoted, it is still
          interpreted as an index if there is no matching string in the
          list of enumeration values. For these reasons, it is not
          advisable to define an
    <code class="literal">
     ENUM
    </code>
    column with
          enumeration values that look like numbers, because this can
          easily become confusing. For example, the following column has
          enumeration members with string values of
    <code class="literal">
     '0'
    </code>
    ,
    <code class="literal">
     '1'
    </code>
    , and
    <code class="literal">
     '2'
    </code>
    , but numeric index values of
    <code class="literal">
     1
    </code>
    ,
    <code class="literal">
     2
    </code>
    , and
    <code class="literal">
     3
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa63911817"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">numbers <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'0'</span><span class="token punctuation">,</span><span class="token string">'1'</span><span class="token punctuation">,</span><span class="token string">'2'</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    If you store
    <code class="literal">
     2
    </code>
    , it is interpreted as an
          index value, and becomes
    <code class="literal">
     '1'
    </code>
    (the value
          with index 2). If you store
    <code class="literal">
     '2'
    </code>
    , it matches
          an enumeration value, so it is stored as
    <code class="literal">
     '2'
    </code>
    . If you store
    <code class="literal">
     '3'
    </code>
    ,
          it does not match any enumeration value, so it is treated as
          an index and becomes
    <code class="literal">
     '2'
    </code>
    (the value with
          index 3).
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84170049"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>numbers<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'2'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'3'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> numbers <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    To determine all possible values for an
    <code class="literal">
     ENUM
    </code>
    column, use
    <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
     <code class="literal">
      SHOW COLUMNS
          FROM
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      LIKE
          '
      <em class="replaceable">
       <code>
        enum_col
       </code>
      </em>
      '
     </code>
    </a>
    and parse the
    <code class="literal">
     ENUM
    </code>
    definition in the
    <code class="literal">
     Type
    </code>
    column of the output.
   </p>
   <p>
    In the C API,
    <code class="literal">
     ENUM
    </code>
    values are returned as
          strings. For information about using result set metadata to
          distinguish them from other strings, see
    <a class="ulink" href="/doc/c-api/8.4/en/c-api-data-structures.html" target="_top">
     C API Basic Data Structures
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enum-nulls">
       </a>
       Empty or NULL Enumeration Values
      </h4>
     </div>
    </div>
   </div>
   <p>
    An enumeration value can also be the empty string
          (
    <code class="literal">
     ''
    </code>
    ) or
    <code class="literal">
     NULL
    </code>
    under
          certain circumstances:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If you insert an invalid value into an
       <code class="literal">
        ENUM
       </code>
       (that is, a string not present in
              the list of permitted values), the empty string is
              inserted instead as a special error value. This string can
              be distinguished from a
       <span class="quote">
        “
        <span class="quote">
         normal
        </span>
        ”
       </span>
       empty string
              by the fact that this string has the numeric value 0. See
       <a class="xref" href="enum.html#enum-indexes" title="Index Values for Enumeration Literals">
        Index Values for Enumeration Literals
       </a>
       for details about the
              numeric indexes for the enumeration values.
      </p>
      <p>
       If strict SQL mode is enabled, attempts to insert invalid
       <code class="literal">
        ENUM
       </code>
       values result in an error.
      </p>
     </li>
     <li class="listitem">
      <p>
       If an
       <code class="literal">
        ENUM
       </code>
       column is declared to permit
       <code class="literal">
        NULL
       </code>
       , the
       <code class="literal">
        NULL
       </code>
       value
              is a valid value for the column, and the default value is
       <code class="literal">
        NULL
       </code>
       . If an
       <code class="literal">
        ENUM
       </code>
       column is declared
       <code class="literal">
        NOT NULL
       </code>
       , its
              default value is the first element of the list of
              permitted values.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enum-sorting">
       </a>
       Enumeration Sorting
      </h4>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     ENUM
    </code>
    values are sorted based on their index
          numbers, which depend on the order in which the enumeration
          members were listed in the column specification. For example,
    <code class="literal">
     'b'
    </code>
    sorts before
    <code class="literal">
     'a'
    </code>
    for
    <code class="literal">
     ENUM('b', 'a')
    </code>
    . The empty string sorts
          before nonempty strings, and
    <code class="literal">
     NULL
    </code>
    values
          sort before all other enumeration values.
   </p>
   <p>
    To prevent unexpected results when using the
    <code class="literal">
     ORDER
          BY
    </code>
    clause on an
    <code class="literal">
     ENUM
    </code>
    column, use
          one of these techniques:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Specify the
       <code class="literal">
        ENUM
       </code>
       list in alphabetic
              order.
      </p>
     </li>
     <li class="listitem">
      <p>
       Make sure that the column is sorted lexically rather than
              by index number by coding
       <code class="literal">
        ORDER BY
              CAST(
        <em class="replaceable">
         <code>
          col
         </code>
        </em>
        AS CHAR)
       </code>
       or
       <code class="literal">
        ORDER BY
              CONCAT(
        <em class="replaceable">
         <code>
          col
         </code>
        </em>
        )
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enum-limits">
       </a>
       Enumeration Limitations
      </h4>
     </div>
    </div>
   </div>
   <p>
    An enumeration value cannot be an expression, even one that
          evaluates to a string value.
   </p>
   <p>
    For example, this
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statement does
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    work because the
    <code class="literal">
     CONCAT
    </code>
    function cannot be used to construct
          an enumeration value:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10172814"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> sizes <span class="token punctuation">(</span>
    size <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'small'</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'med'</span><span class="token punctuation">,</span><span class="token string">'ium'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'large'</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    You also cannot employ a user variable as an enumeration
          value. This pair of statements do
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    work:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21835649"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@mysize</span> <span class="token operator">=</span> <span class="token string">'medium'</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> sizes <span class="token punctuation">(</span>
    size <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'small'</span><span class="token punctuation">,</span> <span class="token variable">@mysize</span><span class="token punctuation">,</span> <span class="token string">'large'</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    We strongly recommend that you do
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    use
          numbers as enumeration values, because it does not save on
          storage over the appropriate
    <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
     <code class="literal">
      TINYINT
     </code>
    </a>
    or
    <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>
    type, and it is easy
          to mix up the strings and the underlying number values (which
          might not be the same) if you quote the
    <code class="literal">
     ENUM
    </code>
    values incorrectly. If you do use a
          number as an enumeration value, always enclose it in quotation
          marks. If the quotation marks are omitted, the number is
          regarded as an index. See
    <a class="xref" href="enum.html#enum-literals" title="Handling of Enumeration Literals">
     Handling of Enumeration Literals
    </a>
    to
          see how even a quoted number could be mistakenly used as a
          numeric index value.
   </p>
   <p>
    Duplicate values in the definition cause a warning, or an
          error if strict SQL mode is enabled.
   </p>
  </div>
 </div>
 <br/>
</div>
