How to Make Case Sensitive String Comparison in MySQL

By default, MySQL does a case insensitive string comparison. It means when you select rows where a column matches a string, MySQL will return all rows where the column value is equal to the string irrespective of the case of the column value. But sometimes you may want to make case sensitive string comparison in MySQL. In this article, we will learn how to make case sensitive string comparison in MySQL.

How to Make Case Sensitive String Comparison in MySQL

Let us say you have the following table students(id, name) in MySQL

mysql> create table students(id int, name varchar(20));

mysql> insert into students(id, name) 
values(1, 'John Doe'),(2, 'JOHN DOE'),(3,'john doe'),
(4,'Jane Doe');

mysql> select * from students;
1, John Doe
2, JOHN DOE
3, john doe
4, Jane Doe

Let us say you run the following select query to your table.

mysql> select * from students where name='john doe';

You will get the following result.

1, John Doe
2, JOHN DOE
3, john doe

As you can see, although we have specified match string as ‘john doe’, MySQL returns rows matching ‘John Doe’, ‘JOHN DOE’, and ‘john doe’.

It performs a case insensitive string match. If you want to do a case sensitive match, then you need to use the keyword BINARY after WHERE keyword and before the column name.

mysql> select * from students where BINARY name='john doe';

You will get the following result.

3, john doe

The above query does a binary matching that is it does a bit by bit comparison to ensure that the match is exact.

But the above query may have performance issues if your table is large and has indexes. In such cases, you can use the keyword BINARY in front of the search value as shown below.

mysql> select * from students where name= BINARY 'john doe';

In this article, we have learnt how to make case sensitive string comparison in MySQL.

Also read:

How to Fix Error MySQL Shutdown Unexpectedly
How to Fix 2006 MySQL Server Has Gone Away
How to Get Previous URL in JavaScript
How to Get Client Timezone & Offset in JavaScript
How to Check if JS Object Has Property