How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.

Any idea on how to do a case sensitive search in SQL query?

by default, MySQL does not consider the case of the strings

This is not quite true. Whenever you create database in MySQL, the database/schema has a character set and a collation. Each character set has a default collation; see here for more information.

The default collation for character set latin1, which is latin1_swedish_ci, happens to be case-insensitive.

You can choose a case-sensitive collation, for example latin1_general_cs (MySQL grammar):

CREATE SCHEMA IF NOT EXISTS `myschema` 
DEFAULT CHARACTER SET latin1 
COLLATE latin1_general_cs ;

This has an effect on things like grouping and equality. For example,

create table casetable (
  id int primary key, 
  thing varchar(50)
);

select * from casetable;
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

In a case-sensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| ABC   |        1 |
| aBc   |        1 |
| abC   |        1 |
| abc   |        1 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
+----+-------+

While in a case-insensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| abc   |        4 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Note that you can also change the collation from within a query. For example, in the case-sensitive database, I can do

select * from casetable where thing collate latin1_swedish_ci = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

StackExchange Database Administrators Q#15250, answer score: 26