<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="identifier-case-sensitivity">
      </a>
      11.2.3 Identifier Case Sensitivity
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045219900384">
  </a>
  <a class="indexterm" name="idm46045219898896">
  </a>
  <a class="indexterm" name="idm46045219897408">
  </a>
  <a class="indexterm" name="idm46045219895920">
  </a>
  <a class="indexterm" name="idm46045219894432">
  </a>
  <a class="indexterm" name="idm46045219892944">
  </a>
  <a class="indexterm" name="idm46045219891456">
  </a>
  <a class="indexterm" name="idm46045219889968">
  </a>
  <a class="indexterm" name="idm46045219888480">
  </a>
  <p>
   In MySQL, databases correspond to directories within the data
        directory. Each table within a database corresponds to at least
        one file within the database directory (and possibly more,
        depending on the storage engine). Triggers also correspond to
        files. Consequently, the case sensitivity of the underlying
        operating system plays a part in the case sensitivity of
        database, table, and trigger names. This means such names are
        not case-sensitive in Windows, but are case-sensitive in most
        varieties of Unix. One notable exception is macOS, which is
        Unix-based but uses a default file system type (HFS+) that is
        not case-sensitive. However, macOS also supports UFS volumes,
        which are case-sensitive just as on any Unix. See
   <a class="xref" href="extensions-to-ansi.html" title="1.7.1 MySQL Extensions to Standard SQL">
    Section 1.7.1, “MySQL Extensions to Standard SQL”
   </a>
   . The
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   system
        variable also affects how the server handles identifier case
        sensitivity, as described later in this section.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Although database, table, and trigger names are not
          case-sensitive on some platforms, you should not refer to one
          of these using different cases within the same statement. The
          following statement would not work because it refers to a
          table both as
    <code class="literal">
     my_table
    </code>
    and as
    <code class="literal">
     MY_TABLE
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa40517084"><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> my_table <span class="token keyword">WHERE</span> MY_TABLE<span class="token punctuation">.</span>col<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <p>
   Partition, subpartition, column, index, stored routine, event,
        and resource group names are not case-sensitive on any platform,
        nor are column aliases.
  </p>
  <p>
   However, names of logfile groups are case-sensitive. This
        differs from standard SQL.
  </p>
  <p>
   By default, table aliases are case-sensitive on Unix, but not so
        on Windows or macOS. The following statement would not work on
        Unix, because it refers to the alias both as
   <code class="literal">
    a
   </code>
   and as
   <code class="literal">
    A
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36264493"><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">col_name</em> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">AS</span> a
       <span class="token keyword">WHERE</span> a<span class="token punctuation">.</span><em class="replaceable">col_name</em> <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">OR</span> A<span class="token punctuation">.</span><em class="replaceable">col_name</em> <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, this same statement is permitted on Windows. To avoid
        problems caused by such differences, it is best to adopt a
        consistent convention, such as always creating and referring to
        databases and tables using lowercase names. This convention is
        recommended for maximum portability and ease of use.
  </p>
  <p>
   How table and database names are stored on disk and used in
        MySQL is affected by the
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   system
        variable.
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   can take
        the values shown in the following table. This variable does
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   affect case sensitivity of trigger
        identifiers. On Unix, the default value of
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   is 0. On
        Windows, the default value is 1. On macOS, the default value is
        2.
  </p>
  <p>
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   can only
        be configured when initializing the server. Changing the
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   setting
        after the server is initialized is prohibited.
  </p>
  <div class="informaltable">
   <table summary="Values for the lower_case_table_names system variable.">
    <colgroup>
     <col style="width: 10%"/>
     <col style="width: 90%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       Value
      </th>
      <th>
       Meaning
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        0
       </code>
      </td>
      <td>
       Table and database names are stored on disk using the lettercase
              specified in the
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE
              TABLE
        </code>
       </a>
       or
       <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
        <code class="literal">
         CREATE
              DATABASE
        </code>
       </a>
       statement. Name comparisons are
              case-sensitive. You should
       <span class="emphasis">
        <em>
         not
        </em>
       </span>
       set
              this variable to 0 if you are running MySQL on a system
              that has case-insensitive file names (such as Windows or
              macOS). If you force this variable to 0 with
       <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
        <code class="option">
         --lower-case-table-names=0
        </code>
       </a>
       on a case-insensitive file system and access
       <code class="literal">
        MyISAM
       </code>
       tablenames using different
              lettercases, index corruption may result.
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        1
       </code>
      </td>
      <td>
       Table names are stored in lowercase on disk and name comparisons are not
              case-sensitive. MySQL converts all table names to
              lowercase on storage and lookup. This behavior also
              applies to database names and table aliases.
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        2
       </code>
      </td>
      <td>
       Table and database names are stored on disk using the lettercase
              specified in the
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE
              TABLE
        </code>
       </a>
       or
       <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
        <code class="literal">
         CREATE
              DATABASE
        </code>
       </a>
       statement, but MySQL converts them to
              lowercase on lookup. Name comparisons are not
              case-sensitive. This works
       <span class="emphasis">
        <em>
         only
        </em>
       </span>
       on
              file systems that are not case-sensitive!
       <code class="literal">
        InnoDB
       </code>
       table names and view names are
              stored in lowercase, as for
       <code class="literal">
        lower_case_table_names=1
       </code>
       .
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   If you are using MySQL on only one platform, you do not normally
        have to use a
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   setting
        other than the default. However, you may encounter difficulties
        if you want to transfer tables between platforms that differ in
        file system case sensitivity. For example, on Unix, you can have
        two different tables named
   <code class="literal">
    my_table
   </code>
   and
   <code class="literal">
    MY_TABLE
   </code>
   , but on Windows these two names are
        considered identical. To avoid data transfer problems arising
        from lettercase of database or table names, you have two
        options:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use
      <code class="literal">
       lower_case_table_names=1
      </code>
      on all
            systems. The main disadvantage with this is that when you
            use
      <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
       <code class="literal">
        SHOW TABLES
       </code>
      </a>
      or
      <a class="link" href="show-databases.html" title="15.7.7.15 SHOW DATABASES Statement">
       <code class="literal">
        SHOW DATABASES
       </code>
      </a>
      , you do not
            see the names in their original lettercase.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <code class="literal">
       lower_case_table_names=0
      </code>
      on Unix and
      <code class="literal">
       lower_case_table_names=2
      </code>
      on Windows. This
            preserves the lettercase of database and table names. The
            disadvantage of this is that you must ensure that your
            statements always refer to your database and table names
            with the correct lettercase on Windows. If you transfer your
            statements to Unix, where lettercase is significant, they do
            not work if the lettercase is incorrect.
     </p>
     <p>
      <span class="bold">
       <strong>
        Exception
       </strong>
      </span>
      : If you are using
      <code class="literal">
       InnoDB
      </code>
      tables and you are trying to avoid
            these data transfer problems, you should use
      <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
       <code class="literal">
        lower_case_table_names=1
       </code>
      </a>
      on
            all platforms to force names to be converted to lowercase.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Object names may be considered duplicates if their uppercase
        forms are equal according to a binary collation. That is true
        for names of cursors, conditions, procedures, functions,
        savepoints, stored routine parameters, stored program local
        variables, and plugins. It is not true for names of columns,
        constraints, databases, partitions, statements prepared with
   <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
    <code class="literal">
     PREPARE
    </code>
   </a>
   , tables, triggers, users,
        and user-defined variables.
  </p>
  <p>
   File system case sensitivity can affect searches in string
        columns of
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables. For
        more information, see
   <a class="xref" href="charset-collation-information-schema.html" title="12.8.7 Using Collation in INFORMATION_SCHEMA Searches">
    Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
