Case-sensitive search in MySQL

Learn how to do case-sensitive search in MySQL. By default, MySQL search text-based column values without case sensitivity. For example, if your database looks like this:

Table: users

And if you run the following query:

SELECT * FROM users WHERE email = "support@Adnan-tech.com";

It will return both records. However, it should have return only the 2nd record. The reason why, is because, usually the collation of text-based columns in MySQL is utf8_general_ci which is case insensitive.

So you just need to change the collation of “email” column in “users” table by running the following command:

ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin;

If you run the query again, you will only see the 2nd row. Even though the 1st row value is also same, it is in lower-case and the query is in upper case “A”.

This is because utf8mb4_bin, it ensures that the search and sort on this column should be case sensitive.

That is how you can do case-sensitive search in MySQL using utf8mb4_bin collation. It is really helpful specially if you are searching on column that has UUID values.