<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="binary-varbinary">
      </a>
      13.3.3 The BINARY and VARBINARY Types
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045212655488">
  </a>
  <a class="indexterm" name="idm46045212654448">
  </a>
  <a class="indexterm" name="idm46045212653376">
  </a>
  <a class="indexterm" name="idm46045212651888">
  </a>
  <p>
   The
   <code class="literal">
    BINARY
   </code>
   and
   <code class="literal">
    VARBINARY
   </code>
   types are similar to
   <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>
   , except that they store
        binary strings rather than nonbinary strings. That is, they
        store byte strings rather than character strings. This means
        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 the values.
  </p>
  <p>
   The permissible maximum length is the same for
   <code class="literal">
    BINARY
   </code>
   and
   <code class="literal">
    VARBINARY
   </code>
   as it
        is 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>
   , except that the length
        for
   <code class="literal">
    BINARY
   </code>
   and
   <code class="literal">
    VARBINARY
   </code>
   is measured in bytes rather than characters.
  </p>
  <p>
   The
   <code class="literal">
    BINARY
   </code>
   and
   <code class="literal">
    VARBINARY
   </code>
   data types are distinct from the
   <code class="literal">
    CHAR BINARY
   </code>
   and
   <code class="literal">
    VARCHAR BINARY
   </code>
   data types. For the latter
        types, the
   <code class="literal">
    BINARY
   </code>
   attribute does not cause
        the column to be treated as a binary string column. Instead, it
        causes the binary (
   <code class="literal">
    _bin
   </code>
   ) collation for the
        column character set (or the table default character set if no
        column character set is specified) to be used, and the column
        itself stores nonbinary character strings rather than binary
        byte strings. For example, if the default character set is
   <code class="literal">
    utf8mb4
   </code>
   ,
   <code class="literal">
    CHAR(5) BINARY
   </code>
   is
        treated as
   <code class="literal">
    CHAR(5) CHARACTER SET utf8mb4 COLLATE
        utf8mb4_bin
   </code>
   . This differs from
   <code class="literal">
    BINARY(5)
   </code>
   , which stores 5-byte binary strings
        that have the
   <code class="literal">
    binary
   </code>
   character set and
        collation. For information about the differences between the
   <code class="literal">
    binary
   </code>
   collation of the
   <code class="literal">
    binary
   </code>
   character set and the
   <code class="literal">
    _bin
   </code>
   collations of nonbinary character sets,
        see
   <a class="xref" href="charset-binary-collations.html" title="12.8.5 The binary Collation Compared to _bin Collations">
    Section 12.8.5, “The binary Collation Compared to _bin Collations”
   </a>
   .
  </p>
  <p>
   If strict SQL mode is not enabled and you assign a value to a
   <code class="literal">
    BINARY
   </code>
   or
   <code class="literal">
    VARBINARY
   </code>
   column
        that exceeds the column's maximum length, the value is truncated
        to fit and a warning is generated. For cases of truncation, to
        cause an error to occur (rather than a warning) and suppress
        insertion of the value, use 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>
   When
   <code class="literal">
    BINARY
   </code>
   values are stored, they are
        right-padded with the pad value to the specified length. The pad
        value is
   <code class="literal">
    0x00
   </code>
   (the zero byte). Values are
        right-padded with
   <code class="literal">
    0x00
   </code>
   for inserts, and no
        trailing bytes are removed for retrievals. All bytes are
        significant in comparisons, including
   <code class="literal">
    ORDER
        BY
   </code>
   and
   <code class="literal">
    DISTINCT
   </code>
   operations.
   <code class="literal">
    0x00
   </code>
   and space differ in comparisons, with
   <code class="literal">
    0x00
   </code>
   sorting before space.
  </p>
  <p>
   Example: For a
   <code class="literal">
    BINARY(3)
   </code>
   column,
   <code class="literal">
    'a '
   </code>
   becomes
   <code class="literal">
    'a \0'
   </code>
   when inserted.
   <code class="literal">
    'a\0'
   </code>
   becomes
   <code class="literal">
    'a\0\0'
   </code>
   when
        inserted. Both inserted values remain unchanged for retrievals.
  </p>
  <p>
   For
   <code class="literal">
    VARBINARY
   </code>
   , there is no padding for
        inserts and no bytes are stripped for retrievals. All bytes are
        significant in comparisons, including
   <code class="literal">
    ORDER
        BY
   </code>
   and
   <code class="literal">
    DISTINCT
   </code>
   operations.
   <code class="literal">
    0x00
   </code>
   and space differ in comparisons, with
   <code class="literal">
    0x00
   </code>
   sorting before space.
  </p>
  <p>
   For those cases where trailing pad bytes are stripped or
        comparisons ignore them, if a column has an index that requires
        unique values, inserting values into the column that differ only
        in number of trailing pad bytes results in a duplicate-key
        error. For example, if a table contains
   <code class="literal">
    'a'
   </code>
   ,
        an attempt to store
   <code class="literal">
    'a\0'
   </code>
   causes a
        duplicate-key error.
  </p>
  <p>
   You should consider the preceding padding and stripping
        characteristics carefully if you plan to use the
   <code class="literal">
    BINARY
   </code>
   data type for storing binary data and
        you require that the value retrieved be exactly the same as the
        value stored. The following example illustrates how
   <code class="literal">
    0x00
   </code>
   -padding of
   <code class="literal">
    BINARY
   </code>
   values affects column value comparisons:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75270060"><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> t <span class="token punctuation">(</span>c <span class="token datatype">BINARY</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">SET</span> c <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">HEX</span><span class="token punctuation">(</span>c<span class="token punctuation">)</span><span class="token punctuation">,</span> c <span class="token operator">=</span> <span class="token string">'a'</span><span class="token punctuation">,</span> c <span class="token operator">=</span> <span class="token string">'a\0\0'</span> <span class="token keyword">from</span> t<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 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 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> HEX(c) <span class="token punctuation">|</span> c = 'a' <span class="token punctuation">|</span> c = 'a\0\0' <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 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 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> 610000 <span class="token punctuation">|</span>       0 <span class="token punctuation">|</span>           1 <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 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 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">1 row in set (0.09 sec)</span></code></pre>
  </div>
  <p>
   If the value retrieved must be the same as the value specified
        for storage with no padding, it might be preferable to use
   <code class="literal">
    VARBINARY
   </code>
   or one of the
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   data types instead.
  </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>
