What is the use of MySQL BINARY keyword while performing string comparison?

MySQLMySQLi Database

When MySQL performs string comparison then it is not case-sensitive but with the help of BINARY keyword, MySQL can perform case-sensitive string comparison. It is because BINARY keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters. It can be illustrated with the following example from table ‘Student_info’ having the following data −

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
+------+---------+------------+------------+
3 rows in set (0.00 sec)

The query below will use BINARY keyword to force MySQL for performing case-sensitive string comparison.

mysql> select * from student_info WHERE BINARY Name IN('YashPal', 'GAURAV');
+------+---------+----------+---------+
| id   | Name    | Address  | Subject |
+------+---------+----------+---------+
| 101  | YashPal | Amritsar | History |
+------+---------+----------+---------+
1 row in set (0.08 sec)

From the above result set it is clear that after using the keyword BINARY, MySQL performs case-sensitive string comparison.

raja
Published on 20-Feb-2018 07:35:30
Advertisements