<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="charset-database">
      </a>
      12.3.3 Database Character Set and Collation
     </h3>
    </div>
   </div>
  </div>
  <p>
   Every database has a database character set and a database
        collation. The
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE DATABASE
    </code>
   </a>
   and
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   statements
        have optional clauses for specifying the database character set
        and collation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22514143"><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">DATABASE</span> <em class="replaceable">db_name</em>
    <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">]</span>

<span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> <em class="replaceable">db_name</em>
    <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   The keyword
   <code class="literal">
    SCHEMA
   </code>
   can be used instead of
   <code class="literal">
    DATABASE
   </code>
   .
  </p>
  <p>
   The
   <code class="literal">
    CHARACTER SET
   </code>
   and
   <code class="literal">
    COLLATE
   </code>
   clauses make it possible to create
        databases with different character sets and collations on the
        same MySQL server.
  </p>
  <p>
   Database options are stored in the data dictionary and can be
        examined by checking the Information Schema
   <a class="link" href="information-schema-schemata-table.html" title="28.3.31 The INFORMATION_SCHEMA SCHEMATA Table">
    <code class="literal">
     SCHEMATA
    </code>
   </a>
   table.
  </p>
  <p>
   Example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa50711255"><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">DATABASE</span> <em class="replaceable">db_name</em> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> latin1 <span class="token keyword">COLLATE</span> latin1_swedish_ci<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL chooses the database character set and database collation
        in the following manner:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If both
      <code class="literal">
       CHARACTER SET
       <em class="replaceable">
        <code>
         charset_name
        </code>
       </em>
      </code>
      and
      <code class="literal">
       COLLATE
       <em class="replaceable">
        <code>
         collation_name
        </code>
       </em>
      </code>
      are
            specified, character set
      <em class="replaceable">
       <code>
        charset_name
       </code>
      </em>
      and collation
      <em class="replaceable">
       <code>
        collation_name
       </code>
      </em>
      are used.
     </p>
    </li>
    <li class="listitem">
     <p>
      If
      <code class="literal">
       CHARACTER SET
       <em class="replaceable">
        <code>
         charset_name
        </code>
       </em>
      </code>
      is
            specified without
      <code class="literal">
       COLLATE
      </code>
      , character set
      <em class="replaceable">
       <code>
        charset_name
       </code>
      </em>
      and its default
            collation are used. To see the default collation for each
            character set, use the
      <a class="link" href="show-character-set.html" title="15.7.7.4 SHOW CHARACTER SET Statement">
       <code class="literal">
        SHOW CHARACTER
            SET
       </code>
      </a>
      statement or query the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-character-sets-table.html" title="28.3.4 The INFORMATION_SCHEMA CHARACTER_SETS Table">
       <code class="literal">
        CHARACTER_SETS
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      If
      <code class="literal">
       COLLATE
       <em class="replaceable">
        <code>
         collation_name
        </code>
       </em>
      </code>
      is
            specified without
      <code class="literal">
       CHARACTER SET
      </code>
      , the
            character set associated with
      <em class="replaceable">
       <code>
        collation_name
       </code>
      </em>
      and collation
      <em class="replaceable">
       <code>
        collation_name
       </code>
      </em>
      are used.
     </p>
    </li>
    <li class="listitem">
     <p>
      Otherwise (neither
      <code class="literal">
       CHARACTER SET
      </code>
      nor
      <code class="literal">
       COLLATE
      </code>
      is specified), the server
            character set and server collation are used.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The character set and collation for the default database can be
        determined from the values of the
   <a class="link" href="server-system-variables.html#sysvar_character_set_database">
    <code class="literal">
     character_set_database
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_collation_database">
    <code class="literal">
     collation_database
    </code>
   </a>
   system
        variables. The server sets these variables whenever the default
        database changes. If there is no default database, the variables
        have the same value as the corresponding server-level system
        variables,
   <a class="link" href="server-system-variables.html#sysvar_character_set_server">
    <code class="literal">
     character_set_server
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_collation_server">
    <code class="literal">
     collation_server
    </code>
   </a>
   .
  </p>
  <p>
   To see the default character set and collation for a given
        database, use these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78625744"><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">USE</span> <em class="replaceable">db_name</em><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@@character_set_database</span><span class="token punctuation">,</span> <span class="token variable">@@collation_database</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Alternatively, to display the values without changing the
        default database:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56471871"><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">SELECT</span> DEFAULT_CHARACTER_SET_NAME<span class="token punctuation">,</span> DEFAULT_COLLATION_NAME
<span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>SCHEMATA <span class="token keyword">WHERE</span> <span class="token keyword">SCHEMA_NAME</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">db_name</em>'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The database character set and collation affect these aspects of
        server operation:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statements,
            the database character set and collation are used as default
            values for table definitions if the table character set and
            collation are not specified. To override this, provide
            explicit
      <code class="literal">
       CHARACTER SET
      </code>
      and
      <code class="literal">
       COLLATE
      </code>
      table options.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      statements that
            include no
      <code class="literal">
       CHARACTER SET
      </code>
      clause, the
            server uses the character set indicated by the
      <a class="link" href="server-system-variables.html#sysvar_character_set_database">
       <code class="literal">
        character_set_database
       </code>
      </a>
      system variable to interpret the information in the file. To
            override this, provide an explicit
      <code class="literal">
       CHARACTER
            SET
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      For stored routines (procedures and functions), the database
            character set and collation in effect at routine creation
            time are used as the character set and collation of
            character data parameters for which the declaration includes
            no
      <code class="literal">
       CHARACTER SET
      </code>
      or a
      <code class="literal">
       COLLATE
      </code>
      attribute. To override this,
            provide
      <code class="literal">
       CHARACTER SET
      </code>
      and
      <code class="literal">
       COLLATE
      </code>
      explicitly.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
