(mysql.info) binary-varbinary

Info Catalog

(mysql.info) char

(mysql.info) string-types

(mysql.info) blob

 
 11.4.2 The `BINARY' and `VARBINARY' Types
 -----------------------------------------
 
 The `BINARY' and `VARBINARY' types are similar to `CHAR' and `VARCHAR',
 except that they contain binary strings rather than non-binary strings.
 That is, they contain byte strings rather than character strings. This
 means that they have no character set, and sorting and comparison are
 based on the numeric values of the bytes in the values.
 
 The allowable maximum length is the same for `BINARY' and `VARBINARY'
 as it is for `CHAR' and `VARCHAR', except that the length for `BINARY'
 and `VARBINARY' is a length in bytes rather than in characters.
 
 The `BINARY' and `VARBINARY' data types are distinct from the `CHAR
 BINARY' and `VARCHAR BINARY' data types. For the latter types, the
 `BINARY' attribute does not cause the column to be treated as a binary
 string column. Instead, it causes the binary collation for the column
 character set to be used, and the column itself contains non-binary
 character strings rather than binary byte strings. For example,
 `CHAR(5) BINARY' is treated as `CHAR(5) CHARACTER SET latin1 COLLATE
 latin1_bin', assuming that the default character set is `latin1'. This
 differs from `BINARY(5)', which stores 5-bytes binary strings that have
 no character set or collation.
 
 When `BINARY' values are stored, they are right-padded with the pad
 value to the specified length. The pad value and how it is handled is
 version specific:
 
    * As of MySQL 5.0.15, the pad value is `0x00' (the zero byte).
      Values are right-padded with `0x00' on insert, and no trailing
      bytes are removed on select. All bytes are significant in
      comparisons, including `ORDER BY' and `DISTINCT' operations.
      `0x00' bytes and spaces are different in comparisons, with `0x00'
      < space.
 
      Example: For a `BINARY(3)' column, `'a '' becomes `'a \0'' when
      inserted.  `'a\0'' becomes `'a\0\0'' when inserted. Both inserted
      values remain unchanged when selected.
 
    * Before MySQL 5.0.15, the pad value is space. Values are
      right-padded with space on insert, and trailing spaces are removed
      on select. Trailing spaces are ignored in comparisons, including
      `ORDER BY' and `DISTINCT' operations.  `0x00' bytes and spaces are
      different in comparisons, with `0x00' < space.
 
      Example: For a `BINARY(3)' column, `'a '' becomes `'a  '' when
      inserted and `'a'' when selected.  `'a\0'' becomes `'a\0 '' when
      inserted and `'a\0'' when selected.
 
 For `VARBINARY', there is no padding on insert and no bytes are
 stripped on select. All bytes are significant in comparisons, including
 `ORDER BY' and `DISTINCT' operations. `0x00' bytes and spaces are
 different in comparisons, with `0x00' < space. (Exceptions: Before MySQL
 5.0.3, trailing spaces are removed when values are stored.  Before
 MySQL 5.0.15, trailing 0x00 bytes are removed for `ORDER BY'
 operations.)
 
 Note that the `InnoDB' storage engine continues to preserve trailing
 spaces in `BINARY' and `VARBINARY' column values through MySQL 5.0.18,
 and then, beginning with MySQL 5.0.19, ignores trailing space
 characters in making comparisons as do other MySQL storage engines.
 
 For those cases where trailing pad bytes are stripped or comparisons
 ignore them, if a column has an index that requires unique values,
 inserting into the column values that differ only in number of trailing
 pad bytes will result in a duplicate-key error. For example, if a table
 contains `'a'', an attempt to store `'a\0'' causes a duplicate-key
 error.
 
 You should consider the preceding padding and stripping characteristics
 carefully if you plan to use the `BINARY' 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 `0x00'-padding of
 `BINARY' values affects column value comparisons:
 
      mysql> CREATE TABLE t (c BINARY(3));
      Query OK, 0 rows affected (0.01 sec)
 
      mysql> INSERT INTO t SET c = 'a';
      Query OK, 1 row affected (0.01 sec)
 
      mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
      +--------+---------+-------------+
      | HEX(c) | c = 'a' | c = 'a\0\0' |
      +--------+---------+-------------+
      | 610000 |       0 |           1 |
      +--------+---------+-------------+
      1 row in set (0.09 sec)
 
 If the value retrieved must be the same as the value specified for
 storage with no padding, it might be preferable to use `VARBINARY' or
 one of the `BLOB' data types instead.
 
 In some cases, MySQL may silently change the type of a `BINARY' or
 `VARBINARY' column at table creation time. See 
 silent-column-changes.
 

Info Catalog

(mysql.info) char

(mysql.info) string-types

(mysql.info) blob