2

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 beNote that by lowercasing the m, I no longer get the user

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.

TheJoe
  • 125
  • 7

3 Answers3

4

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 |
+------------+
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This is exactly it. My collation shows as utf8m4_bin. Now I have a lot of data in my table, so I'd rather not have to re-create it, so I consulted https://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column to figure out how to change the collation without recreating the table. – TheJoe Feb 23 '21 at 22:18
0

did you try this ?

SELECT users_user.username FROM users_user WHERE users_user.username LIKE '%DEMO1-0048%'
Duckvader
  • 3
  • 4
  • SELECT users_user.username FROM users_user WHERE users_user.username LIKE '%DEMO1-0048%' will find DEMO1-0048 because it is correct casing. But I tried SELECT users_user.username FROM users_user WHERE users_user.username LIKE '%DEmO1-0048%' and I still get the empty set. The desire is to be able to enter my username with lower cases and still get the User with a username that is uppercase. – TheJoe Feb 23 '21 at 21:56
0

You could make your query convert to upper case so that they match,

SELECT * FROM users_user WHERE UPPER(username) LIKE UPPER('%DeMO1-0048%');
Mark B
  • 649
  • 5
  • 11