<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="identifiers">
      </a>
      11.2 Schema Object Names
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="identifier-length.html">
       11.2.1 Identifier Length Limits
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="identifier-qualifiers.html">
       11.2.2 Identifier Qualifiers
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="identifier-case-sensitivity.html">
       11.2.3 Identifier Case Sensitivity
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="identifier-mapping.html">
       11.2.4 Mapping of Identifiers to File Names
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="function-resolution.html">
       11.2.5 Function Name Parsing and Resolution
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045220079968">
  </a>
  <a class="indexterm" name="idm46045220078896">
  </a>
  <a class="indexterm" name="idm46045220077824">
  </a>
  <a class="indexterm" name="idm46045220076752">
  </a>
  <a class="indexterm" name="idm46045220075264">
  </a>
  <a class="indexterm" name="idm46045220073776">
  </a>
  <a class="indexterm" name="idm46045220072288">
  </a>
  <a class="indexterm" name="idm46045220070800">
  </a>
  <a class="indexterm" name="idm46045220069312">
  </a>
  <p>
   Certain objects within MySQL, including database, table, index,
      column, alias, view, stored procedure, partition, tablespace,
      resource group and other object names are known as identifiers.
      This section describes the permissible syntax for identifiers in
      MySQL.
   <a class="xref" href="identifier-length.html" title="11.2.1 Identifier Length Limits">
    Section 11.2.1, “Identifier Length Limits”
   </a>
   , indicates the maximum
      length of each type of identifier.
   <a class="xref" href="identifier-case-sensitivity.html" title="11.2.3 Identifier Case Sensitivity">
    Section 11.2.3, “Identifier Case Sensitivity”
   </a>
   , describes which
      types of identifiers are case-sensitive and under what conditions.
  </p>
  <a class="indexterm" name="idm46045220065408">
  </a>
  <a class="indexterm" name="idm46045220063920">
  </a>
  <a class="indexterm" name="idm46045220062848">
  </a>
  <a class="indexterm" name="idm46045220061808">
  </a>
  <p>
   An identifier may be quoted or unquoted. If an identifier contains
      special characters or is a reserved word, you
   <span class="emphasis">
    <em>
     must
    </em>
   </span>
   quote it whenever you refer to it.
      (Exception: A reserved word that follows a period in a qualified
      name must be an identifier, so it need not be quoted.) Reserved
      words are listed at
   <a class="xref" href="keywords.html" title="11.3 Keywords and Reserved Words">
    Section 11.3, “Keywords and Reserved Words”
   </a>
   .
  </p>
  <p>
   Internally, identifiers are converted to and are stored as Unicode
      (UTF-8). The permissible Unicode characters in identifiers are
      those in the Basic Multilingual Plane (BMP). Supplementary
      characters are not permitted. Identifiers thus may contain these
      characters:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Permitted characters in unquoted identifiers:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9,
              dollar, underscore)
        </p>
       </li>
       <li class="listitem">
        <p>
         Extended: U+0080 .. U+FFFF
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Permitted characters in quoted identifiers include the full
          Unicode Basic Multilingual Plane (BMP), except U+0000:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         ASCII: U+0001 .. U+007F
        </p>
       </li>
       <li class="listitem">
        <p>
         Extended: U+0080 .. U+FFFF
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      ASCII NUL (U+0000) and supplementary characters (U+10000 and
          higher) are not permitted in quoted or unquoted identifiers.
     </p>
    </li>
    <li class="listitem">
     <p>
      Identifiers may begin with a digit but unless quoted may not
          consist solely of digits.
     </p>
    </li>
    <li class="listitem">
     <p>
      Database, table, and column names cannot end with space
          characters.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use of the dollar sign as the first character in the unquoted
          name of a database, table, view, column, stored program, or
          alias is deprecated, including such names used with qualifiers
          (see
      <a class="xref" href="identifier-qualifiers.html" title="11.2.2 Identifier Qualifiers">
       Section 11.2.2, “Identifier Qualifiers”
      </a>
      ). An unquoted
          identifier beginning with a dollar sign cannot contain any
          additional dollar sign characters. Otherwise, the leading
          dollar sign is permitted but triggers a deprecation warning.
     </p>
     <p>
      The dollar sign can still be used as the leading character of
          such an identifier without producing the warning, when it is
          quoted according to the rules given later in this section.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The identifier quote character is the backtick
      (
   <code class="literal">
    `
   </code>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37468240"><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> <span class="token punctuation">`</span><span class="token keyword">select</span><span class="token punctuation">`</span> <span class="token keyword">WHERE</span> <span class="token punctuation">`</span><span class="token keyword">select</span><span class="token punctuation">`</span><span class="token punctuation">.</span>id <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If the
   <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
    <code class="literal">
     ANSI_QUOTES
    </code>
   </a>
   SQL mode is
      enabled, it is also permissible to quote identifiers within double
      quotation marks:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21759660"><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">CREATE</span> <span class="token keyword">TABLE</span> <span class="token string">"test"</span> <span class="token punctuation">(</span>col <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1064<span class="token punctuation">:</span> You have an error in your SQL syntax...</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token string">'ANSI_QUOTES'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token string">"test"</span> <span class="token punctuation">(</span>col <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
    <code class="literal">
     ANSI_QUOTES
    </code>
   </a>
   mode causes the
      server to interpret double-quoted strings as identifiers.
      Consequently, when this mode is enabled, string literals must be
      enclosed within single quotation marks. They cannot be enclosed
      within double quotation marks. The server SQL mode is controlled
      as described in
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <p>
   Identifier quote characters can be included within an identifier
      if you quote the identifier. If the character to be included
      within the identifier is the same as that used to quote the
      identifier itself, then you need to double the character. The
      following statement creates a table named
   <code class="literal">
    a`b
   </code>
   that contains a column named
   <code class="literal">
    c"d
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95046516"><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">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>a<span class="token punctuation">`</span><span class="token punctuation">`</span>b<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>c"d<span class="token punctuation">`</span> <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <a class="indexterm" name="idm46045220032272">
  </a>
  <a class="indexterm" name="idm46045220030784">
  </a>
  <p>
   In the select list of a query, a quoted column alias can be
      specified using identifier or string quoting characters:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65397435"><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 number">1</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span><span class="token keyword">one</span><span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">AS</span> <span class="token string">'two'</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>
<span class="token output"><span class="token punctuation">|</span> one <span class="token punctuation">|</span> two <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>
<span class="token output"><span class="token punctuation">|</span>   1 <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 class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Elsewhere in the statement, quoted references to the alias must
      use identifier quoting or the reference is treated as a string
      literal.
  </p>
  <p>
   It is recommended that you do not use names that begin with
   <code class="literal">
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    e
   </code>
   or
   <code class="literal">
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    e
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
   </code>
   ,
      where
   <em class="replaceable">
    <code>
     M
    </code>
   </em>
   and
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   are integers. For example, avoid
      using
   <code class="literal">
    1e
   </code>
   as an identifier, because an
      expression such as
   <code class="literal">
    1e+3
   </code>
   is ambiguous. Depending
      on context, it might be interpreted as the expression
   <code class="literal">
    1e
      + 3
   </code>
   or as the number
   <code class="literal">
    1e+3
   </code>
   .
  </p>
  <p>
   Be careful when using
   <a class="link" href="encryption-functions.html#function_md5">
    <code class="literal">
     MD5()
    </code>
   </a>
   to
      produce table names because it can produce names in illegal or
      ambiguous formats such as those just described.
  </p>
  <p>
   It is also recommended that you do not use column names that begin
      with
   <code class="literal">
    !hidden!
   </code>
   to ensure that new names do not
      collide with names used by existing hidden columns for functional
      indexes.
  </p>
  <p>
   A user variable cannot be used directly in an SQL statement as an
      identifier or as part of an identifier. See
   <a class="xref" href="user-variables.html" title="11.4 User-Defined Variables">
    Section 11.4, “User-Defined Variables”
   </a>
   , for more information and
      examples of workarounds.
  </p>
  <p>
   Special characters in database and table names are encoded in the
      corresponding file system names as described in
   <a class="xref" href="identifier-mapping.html" title="11.2.4 Mapping of Identifiers to File Names">
    Section 11.2.4, “Mapping of Identifiers to File Names”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
