Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.9Kb
Man Pages (Zip) - 189.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

charset-binary-op

This page has moved or been replaced. The new page is located here:

http://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

Please update any bookmarks that point to the old page.


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)

Sign Up Login You must be logged in to post a comment.