<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="blob">
      </a>
      13.3.4 The BLOB and TEXT Types
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045212592944">
  </a>
  <a class="indexterm" name="idm46045212591872">
  </a>
  <a class="indexterm" name="idm46045212590800">
  </a>
  <a class="indexterm" name="idm46045212589312">
  </a>
  <a class="indexterm" name="idm46045212587824">
  </a>
  <a class="indexterm" name="idm46045212586752">
  </a>
  <p>
   A
   <code class="literal">
    BLOB
   </code>
   is a binary large object that can hold
        a variable amount of data. The four
   <code class="literal">
    BLOB
   </code>
   types are
   <code class="literal">
    TINYBLOB
   </code>
   ,
   <code class="literal">
    BLOB
   </code>
   ,
   <code class="literal">
    MEDIUMBLOB
   </code>
   , and
   <code class="literal">
    LONGBLOB
   </code>
   .
        These differ only in the maximum length of the values they can
        hold. The four
   <code class="literal">
    TEXT
   </code>
   types are
   <code class="literal">
    TINYTEXT
   </code>
   ,
   <code class="literal">
    TEXT
   </code>
   ,
   <code class="literal">
    MEDIUMTEXT
   </code>
   , and
   <code class="literal">
    LONGTEXT
   </code>
   .
        These correspond to the four
   <code class="literal">
    BLOB
   </code>
   types and
        have the same maximum lengths and storage requirements. See
   <a class="xref" href="storage-requirements.html" title="13.7 Data Type Storage Requirements">
    Section 13.7, “Data Type Storage Requirements”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    BLOB
   </code>
   values are treated as binary strings
        (byte strings). They have the
   <code class="literal">
    binary
   </code>
   character set and collation, and comparison and sorting are
        based on the numeric values of the bytes in column values.
   <code class="literal">
    TEXT
   </code>
   values are treated as nonbinary strings
        (character strings). They have a character set other than
   <code class="literal">
    binary
   </code>
   , and values are sorted and compared
        based on the collation of the character set.
  </p>
  <p>
   If strict SQL mode is not enabled and you assign a value to a
   <code class="literal">
    BLOB
   </code>
   or
   <code class="literal">
    TEXT
   </code>
   column that
        exceeds the column's maximum length, the value is truncated to
        fit and a warning is generated. For truncation of nonspace
        characters, you can cause an error to occur (rather than a
        warning) and suppress insertion of the value by using strict SQL
        mode. See
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <p>
   Truncation of excess trailing spaces from values to be inserted
        into
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   columns always
        generates a warning, regardless of the SQL mode.
  </p>
  <p>
   For
   <code class="literal">
    TEXT
   </code>
   and
   <code class="literal">
    BLOB
   </code>
   columns,
        there is no padding on insert and no bytes are stripped on
        select.
  </p>
  <p>
   If a
   <code class="literal">
    TEXT
   </code>
   column is indexed, index entry
        comparisons are space-padded at the end. This means that, if the
        index requires unique values, duplicate-key errors occur for
        values that differ only in the number of trailing spaces. For
        example, if a table contains
   <code class="literal">
    'a'
   </code>
   , an attempt
        to store
   <code class="literal">
    'a '
   </code>
   causes a duplicate-key
        error. This is not true for
   <code class="literal">
    BLOB
   </code>
   columns.
  </p>
  <p>
   In most respects, you can regard a
   <code class="literal">
    BLOB
   </code>
   column as a
   <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
    <code class="literal">
     VARBINARY
    </code>
   </a>
   column that
        can be as large as you like. Similarly, you can regard a
   <code class="literal">
    TEXT
   </code>
   column as a
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   column.
   <code class="literal">
    BLOB
   </code>
   and
   <code class="literal">
    TEXT
   </code>
   differ from
   <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="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   in the following ways:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For indexes on
      <code class="literal">
       BLOB
      </code>
      and
      <code class="literal">
       TEXT
      </code>
      columns, you must specify an index
            prefix length. For
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      and
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      , a prefix length is
            optional. See
      <a class="xref" href="column-indexes.html" title="10.3.5 Column Indexes">
       Section 10.3.5, “Column Indexes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045212546448">
      </a>
      <a class="indexterm" name="idm46045212544960">
      </a>
      <a class="indexterm" name="idm46045212543472">
      </a>
      <code class="literal">
       BLOB
      </code>
      and
      <code class="literal">
       TEXT
      </code>
      columns
            cannot have
      <code class="literal">
       DEFAULT
      </code>
      values.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If you use the
   <code class="literal">
    BINARY
   </code>
   attribute with a
   <code class="literal">
    TEXT
   </code>
   data type, the column is assigned the
        binary (
   <code class="literal">
    _bin
   </code>
   ) collation of the column
        character set.
  </p>
  <p>
   <code class="literal">
    LONG
   </code>
   and
   <code class="literal">
    LONG VARCHAR
   </code>
   map
        to the
   <code class="literal">
    MEDIUMTEXT
   </code>
   data type. This is a
        compatibility feature.
  </p>
  <p>
   MySQL Connector/ODBC defines
   <code class="literal">
    BLOB
   </code>
   values as
   <code class="literal">
    LONGVARBINARY
   </code>
   and
   <code class="literal">
    TEXT
   </code>
   values as
   <code class="literal">
    LONGVARCHAR
   </code>
   .
  </p>
  <p>
   Because
   <code class="literal">
    BLOB
   </code>
   and
   <code class="literal">
    TEXT
   </code>
   values can be extremely long, you might encounter some
        constraints in using them:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Only the first
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      bytes of
            the column are used when sorting. The default value of
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      is 1024.
            You can make more bytes significant in sorting or grouping
            by increasing the value of
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      at server
            startup or runtime. Any client can change the value of its
            session
      <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
       <code class="literal">
        max_sort_length
       </code>
      </a>
      variable:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22958973"><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">SET</span> max_sort_length <span class="token operator">=</span> <span class="token number">2000</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token keyword">comment</span> <span class="token keyword">FROM</span> t
<span class="token prompt">    -&gt;</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">comment</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Instances of
      <code class="literal">
       BLOB
      </code>
      or
      <code class="literal">
       TEXT
      </code>
      columns in the result of a query
            that is processed using a temporary table causes the server
            to use a table on disk rather than in memory because the
      <code class="literal">
       MEMORY
      </code>
      storage engine does not support
            those data types (see
      <a class="xref" href="internal-temporary-tables.html" title="10.4.4 Internal Temporary Table Use in MySQL">
       Section 10.4.4, “Internal Temporary Table Use in MySQL”
      </a>
      ). Use of disk
            incurs a performance penalty, so include
      <code class="literal">
       BLOB
      </code>
      or
      <code class="literal">
       TEXT
      </code>
      columns
            in the query result only if they are really needed. For
            example, avoid using
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT *
       </code>
      </a>
      ,
            which selects all columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      The maximum size of a
      <code class="literal">
       BLOB
      </code>
      or
      <code class="literal">
       TEXT
      </code>
      object is determined by its type,
            but the largest value you actually can transmit between the
            client and server is determined by the amount of available
            memory and the size of the communications buffers. You can
            change the message buffer size by changing the value of the
      <a class="link" href="server-system-variables.html#sysvar_max_allowed_packet">
       <code class="literal">
        max_allowed_packet
       </code>
      </a>
      variable, but you must do so for both the server and your
            client program. For example, both
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      and
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      enable you to change the
            client-side
      <a class="link" href="server-system-variables.html#sysvar_max_allowed_packet">
       <code class="literal">
        max_allowed_packet
       </code>
      </a>
      value.
            See
      <a class="xref" href="server-configuration.html" title="7.1.1 Configuring the Server">
       Section 7.1.1, “Configuring the Server”
      </a>
      ,
      <a class="xref" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       Section 6.5.1, “mysql — The MySQL Command-Line Client”
      </a>
      , and
      <a class="xref" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       Section 6.5.4, “mysqldump — A Database Backup Program”
      </a>
      .
            You may also want to compare the packet sizes and the size
            of the data objects you are storing with the storage
            requirements, see
      <a class="xref" href="storage-requirements.html" title="13.7 Data Type Storage Requirements">
       Section 13.7, “Data Type Storage Requirements”
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   Each
   <code class="literal">
    BLOB
   </code>
   or
   <code class="literal">
    TEXT
   </code>
   value is
        represented internally by a separately allocated object. This is
        in contrast to all other data types, for which storage is
        allocated once per column when the table is opened.
  </p>
  <p>
   In some cases, it may be desirable to store binary data such as
        media files in
   <code class="literal">
    BLOB
   </code>
   or
   <code class="literal">
    TEXT
   </code>
   columns. You may find MySQL's string
        handling functions useful for working with such data. See
   <a class="xref" href="string-functions.html" title="14.8 String Functions and Operators">
    Section 14.8, “String Functions and Operators”
   </a>
   . For security and other
        reasons, it is usually preferable to do so using application
        code rather than giving application users the
   <a class="link" href="privileges-provided.html#priv_file">
    <code class="literal">
     FILE
    </code>
   </a>
   privilege. You can discuss
        specifics for various languages and platforms in the MySQL
        Forums (
   <a class="ulink" href="http://forums.mysql.com/" target="_blank">
    http://forums.mysql.com/
   </a>
   ).
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Within the
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    client, binary strings
          display using hexadecimal notation, depending on the value of
          the
    <a class="link" href="mysql-command-options.html#option_mysql_binary-as-hex">
     <code class="option">
      --binary-as-hex
     </code>
    </a>
    . For more
          information about that option, see
    <a class="xref" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     Section 6.5.1, “mysql — The MySQL Command-Line Client”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
