MySQL :: MySQL 5.6 Reference Manual :: 10.1.8.6 The BINARY Operator

MySQL 5.6 Reference Manual / ... / The BINARY Operator

10.1.8.6 The BINARY Operator

The BINARY operator casts the string following it to a binary string. This is an easy way to force a comparison to be done byte by byte rather than character by character. BINARY also causes trailing spaces to be significant.

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0

BINARY str is shorthand for CAST(str AS BINARY).

The BINARY attribute in character column definitions has a different effect. A character column defined with the BINARY attribute is assigned the binary collation of the column character set. Every character set has a binary collation. For example, the binary collation for the latin1 character set is latin1_bin, so if the table default character set is latin1, these two column definitions are equivalent:

CHAR(10) BINARY
CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin

The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary data type. For example, the following pairs of definitions are equivalent:

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

PREV HOME UP NEXT

User Comments

Posted by Paul Masri on February 10, 2005

How to do a case sensitive search:

By default, in MySQL 4, text comparisons will be case-insensitive. e.g.
SELECT Name FROM namelist WHERE Name="Harry"
returns "Harry", "HARRY" and "harry"
because the default collation is case-insensitive - H is equivalent to h.

Using BINARY in the WHERE clause forces a match on the binary collation, which in English means that it matches actual characters by their character code, not by whether the characters are deemed equivalent.

Using the same example,
SELECT Name FROM namelist WHERE BINARY Name="Harry"
returns "Harry" only.

See also A.5.1

Posted by Yann Neuhaus on July 20, 2005

Per default the search operation in not case sensitive, example, looking for 'HYPE BEAU' returns 'Hype Beau' in the table product :

mysql> select prod_name, prod_id from products where prod_name = 'HYPE BEAU';

+-----------+---------+
| prod_name | prod_id |
+-----------+---------+
| Hype Beau |    9600 |
| Hype Beau |   25860 |
| Hype Beau |   42120 |
| Hype Beau |   46185 |
+-----------+---------+

4 rows in set (0.01 sec)

This is because the default collation is case insentitive. With the binary clause the binary collation is used and the search becomes case sensitive :

mysql> select prod_name, prod_id from products where binary prod_name = 'HYPE BEAU';
Empty set (0.09 sec)

mysql> select prod_name, prod_id from products where binary prod_name = 'Hype Beau';

+-----------+---------+
| prod_name | prod_id |
+-----------+---------+
| Hype Beau |    9600 |
| Hype Beau |   25860 |
| Hype Beau |   42120 |
| Hype Beau |   46185 |
+-----------+---------+

4 rows in set (0.09 sec)

OK but the problem is that with binary, the index cannot be used :

mysql> explain select prod_name, prod_id from products where binary prod_name = 'Hype Beau';

+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 10000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

The solution is to apply the binary operator to the constant, then the index is used :

mysql> select prod_name, prod_id from products where prod_name = binary 'Hype Beau';

+-----------+---------+
| prod_name | prod_id |
+-----------+---------+
| Hype Beau |    9600 |
| Hype Beau |   25860 |
| Hype Beau |   42120 |
| Hype Beau |   46185 |
+-----------+---------+

4 rows in set (0.00 sec)

mysql> explain select prod_name, prod_id from products where prod_name = binary 'Hype Beau';

+----+-------------+----------+-------+------------------------+------------------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys          | key                    | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+------------------------+------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | products | range | products_prod_name_idx | products_prod_name_idx | 52      | NULL |    3 | Using where |
+----+-------------+----------+-------+------------------------+------------------------+---------+------+------+-------------+

1 row in set (0.00 sec)