MySQL LIKE is case sensitive but I don't want it to be

Question

As I understand it, MySQL LIKE is supposed to be case insensitive. Everywhere I've looked provides instructions on how to make it case sensitive if needed. Mine seems to be case sensitive, but I don't want it to be

This is causing an issue with my authentication server which needs to be case insensitive when authenticating users. Please let me know how to fix this, or how I can figure out why LIKE is case sensitive here.

Answer 1

Case sensitivity is based on the collation of the column you are searching, defined in your CREATE TABLE, or else the collation of the session, which determines the character set and collation of string literals.

Example:

CREATE TABLE `users_user` (
  `username` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into users_user set username='DEMO1-0048';

Here we see the default collation of utf8mb4_general_ci is case-insensitive:

mysql> select * from users_user where username like 'DeMO1-0048';
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

But if I force the column to use a case-sensitive collation:

mysql> select * from users_user where username collate utf8mb4_bin like 'DeMO1-0048';
Empty set (0.00 sec)

Or if I force the string literal to use a case-insensitive collation:

mysql> select * from users_user where username like 'DeMO1-0048' collate utf8mb4_bin;
Empty set (0.00 sec)

Or if I define the table with a case-sensitive collation:

CREATE TABLE `users_user` (
  `username` text COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into users_user set username='DEMO1-0048';

mysql> select * from users_user where username like 'DeMO1-0048';
Empty set (0.00 sec)

So I would infer that your table is defined with a case-sensitive collation. You can check this:

mysql> select character_set_name, collation_name from information_schema.columns where table_name='users_user' and column_name='username';
+--------------------+----------------+
| character_set_name | collation_name |
+--------------------+----------------+
| utf8mb4            | utf8mb4_bin    |
+--------------------+----------------+

You can force a string comparison to be case-insensitive, even if the default collation defined for the table/column is case-sensitive.

mysql> select * from users_user where username like 'DeMO1-0048' collate utf8mb4_general_ci;
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

This works if you use the collate option on the column too:

mysql> select * from users_user where username collate utf8mb4_general_ci like 'DeMO1-0048';
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+