String comparison is NOT case-sensitive by default in MySQL, but if there is a need for case-sensitive string comparison, there are ways to deal with it.

1. Use BINARY operator

The BINARY operator converts a character string to a binary string. It forces a character string comparison to be done byte by byte using numeric byte values rather than character by character.

For example, the following query returns 1 which stands for TRUE because the string geek is the same as string GEEK.

SELECT 'geek' = 'GEEK';

But if we apply BINARY operator to the string 'GEEK', the result is FALSE (returns 0) because the string lowercase geek is different to uppercase string GEEK.

SELECT 'geek' = BINARY 'GEEK';

Another example:

select 'géek' = 'geek';

Query above returns 1 which is TRUE, whereas select 'géek' = BINARY 'geek';returns 0 which is FALSE.

The following query is case-sensitive search against a column by using BINARY operator. It extracts all category records where the category name contains lowercase character 's'. Note that category 'Seafood' is not in the result because the first letter 'S' is uppercase.

select CategoryID, CategoryName 
from categories
where BINARY CategoryName like '%s%';

5 records returned:

Below is the case-insensitive version of the above query and it extracts all category records where the category name contains character 's' or 'S' (both lowercase 's' and uppercase 'S' are returned in the result).

select CategoryID, CategoryName 
from categories
where CategoryName like '%s%';

6 records returned:

A couple of things to note about using BINARY operator:

• The BINARY operator also causes trailing spaces in comparisons to be significant. Query SELECT 'geek' = BINARY 'geek '; returns 0 which is FALSE because the string on the right-hand side of the equal sign has a trailing space.

• You can treat the use of BINARY operator as a function like BINARY(). For example, in this query SELECT 'geek' = BINARY('GEEK'); BINARY is used with brackets like a function.

• The BINARY operator is deprecated as of MySQL 8.0.27, and you should expect its removal in a future version of MySQL. Use CAST(... AS BINARY) instead.

  SELECT 'geek' = CAST('GEEK' AS BINARY);
  

• In a query, it has performance differences when applying BINARY to a column versus applying BINARY to the value the column is searched for.

  When applying BINARY to a column, the index of the column becomes obsolete, which could dramatically slow down your query. This is because category name in each row has to be converted before comparing to the value 'Seafood'. This forces the query engine to carry out row-by-row scan of the entire table.

  -- Applying BINARY to column CategoryName
  select * from categories
  where BINARY CategoryName = 'Seafood';
  

  When applying BINARY to the value, the index of the column is used. Below the BINARY operator is applied to string 'Seafood' and index Uidx_categories_category_name is used.

  -- Applying BINARY to the value to be searched
  select * from categories
  where CategoryName = BINARY 'Seafood';
  

  To see the effect of index usage, run EXPLAIN command on the two queries.

  -- Index is getting used
  EXPLAIN select * from categories
  where CategoryName = BINARY 'Seafood';
  

  

  -- Index is not used
  EXPLAIN select * from categories
  where BINARY CategoryName = 'Seafood';
  

  

2. Use the COLLATE operator to define a column as case-sensitive

Below a new table is created and column CategoryName is defined by COLLATE utf8mb4_bin which is case-sensitive.

CREATE TABLE categories_case_sensitive (
  `CategoryID` tinyint unsigned NOT NULL,
  `CategoryName` varchar(15) COLLATE utf8mb4_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB;

Now add some records to the new table.

insert  into categories_case_sensitive(CategoryID,CategoryName) values(1,'Beverages');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(2,'Condiments');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(3,'Confections');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(4,'Dairyé Products');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(5,'Grains/Cereals');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(6,'Meat/Poultry');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(7,'Produce');
insert  into categories_case_sensitive(CategoryID,CategoryName) values(8,'Seafood');

select * from categories_case_sensitive
where CategoryName like '%s%';