<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="silent-column-changes">
      </a>
      15.1.20.7 Silent Column Specification Changes
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045184842560">
  </a>
  <p>
   In some cases, MySQL silently changes column specifications from
        those given in a
   <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="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement. These
        might be changes to a data type, to attributes associated with a
        data type, or to an index specification.
  </p>
  <p>
   All changes are subject to the internal row-size limit of 65,535
        bytes, which may cause some attempts at data type changes to
        fail. See
   <a class="xref" href="column-count-limit.html" title="10.4.7 Limits on Table Column Count and Row Size">
    Section 10.4.7, “Limits on Table Column Count and Row Size”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Columns that are part of a
      <code class="literal">
       PRIMARY KEY
      </code>
      are made
      <code class="literal">
       NOT NULL
      </code>
      even if not declared
            that way.
     </p>
    </li>
    <li class="listitem">
     <p>
      Trailing spaces are automatically deleted from
      <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
       <code class="literal">
        ENUM
       </code>
      </a>
      and
      <a class="link" href="set.html" title="13.3.6 The SET Type">
       <code class="literal">
        SET
       </code>
      </a>
      member values when the
            table is created.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL maps certain data types used by other SQL database
            vendors to MySQL types. See
      <a class="xref" href="other-vendor-data-types.html" title="13.9 Using Data Types from Other Database Engines">
       Section 13.9, “Using Data Types from Other Database Engines”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If you include a
      <code class="literal">
       USING
      </code>
      clause to specify
            an index type that is not permitted for a given storage
            engine, but there is another index type available that the
            engine can use without affecting query results, the engine
            uses the available type.
     </p>
    </li>
    <li class="listitem">
     <p>
      If strict SQL mode is not enabled, a
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      column with a length
            specification greater than 65535 is converted to
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      , and a
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        VARBINARY
       </code>
      </a>
      column with a
            length specification greater than 65535 is converted to
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      . Otherwise, an error
            occurs in either of these cases.
     </p>
    </li>
    <li class="listitem">
     <p>
      Specifying the
      <code class="literal">
       CHARACTER SET binary
      </code>
      attribute for a character data type causes the column to be
            created as the corresponding binary data type:
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      becomes
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        BINARY
       </code>
      </a>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      becomes
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        VARBINARY
       </code>
      </a>
      , and
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      becomes
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      . For the
      <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
       <code class="literal">
        ENUM
       </code>
      </a>
      and
      <a class="link" href="set.html" title="13.3.6 The SET Type">
       <code class="literal">
        SET
       </code>
      </a>
      data types, this does not
            occur; they are created as declared. Suppose that you
            specify a table using this definition:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72594072"><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> t
<span class="token punctuation">(</span>
  c1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span><span class="token punctuation">,</span>
  c2 <span class="token datatype">TEXT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span><span class="token punctuation">,</span>
  c3 <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span><span class="token string">'b'</span><span class="token punctuation">,</span><span class="token string">'c'</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The resulting table has this definition:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2759581"><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> t
<span class="token punctuation">(</span>
  c1 <span class="token datatype">VARBINARY</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  c2 <span class="token datatype">BLOB</span><span class="token punctuation">,</span>
  c3 <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span><span class="token string">'b'</span><span class="token punctuation">,</span><span class="token string">'c'</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   To see whether MySQL used a data type other than the one you
        specified, issue a
   <a class="link" href="describe.html" title="15.8.1 DESCRIBE Statement">
    <code class="literal">
     DESCRIBE
    </code>
   </a>
   or
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   statement after
        creating or altering the table.
  </p>
  <a class="indexterm" name="idm46045184804576">
  </a>
  <p>
   Certain other data type changes can occur if you compress a
        table using
   <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
    <span class="command">
     <strong>
      myisampack
     </strong>
    </span>
   </a>
   . See
   <a class="xref" href="compressed-format.html" title="18.2.3.3 Compressed Table Characteristics">
    Section 18.2.3.3, “Compressed Table Characteristics”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
