Mysql database query case in-sensitive...

somik · March 11

So I just found out that mysql database queries are case insensitive. So how do we query case sensitive contents?

My usecase is generating those short URLs for file uploads/link redirection, etc. I used to generate a random string of alphanumeric chars and save it as the identifier. When the same string (short url) is provided, i query the database and retrieve the appropriate result. However I noticed that it doesn't matter whether I used all upper case or all lower case or mixed case, the mysql query ignores case when I query with the short url.

So all of these queries returns the same row:

SELECT * FROM MyTable WHERE short_url = 'h6Sp1A';
SELECT * FROM MyTable WHERE short_url = 'H6sP1a';
SELECT * FROM MyTable WHERE short_url = 'H6SP1A';
SELECT * FROM MyTable WHERE short_url = 'h6sp1a';

I do not want to use links that just contain either upper only or lower only chars mixed with numbers as in this case, the short url will be a bit long in this case...

So what to do about this?

As per my understanding, using BINARY to query will cause issues if both the input string and database are not in same character encoding so that's out.

One option I have is to hash the short url (md5, sha128) and search for the hash instead of the url itself, like we do for passwords.

Do you guys/gals have any other ideas better idea?

yoursunny · March 11

@somik said:
As per my understanding, using BINARY to query will cause issues if both the input string and database are not in same character encoding so that's out.

Convert string to UTF8 prior to insertion/query.

tetech · March 11

Define the column with a collation type ending in _bin

ehab · March 11

are you able

select * from MyTable where short_url collate latin1_swedish_ci = "H6SP1A";

choose the correct encoding first?

somik · March 11

@yoursunny said:

@somik said:
As per my understanding, using BINARY to query will cause issues if both the input string and database are not in same character encoding so that's out.

Convert string to UTF8 prior to insertion/query.

Yes, that is certainly an option but wont I need to ensure that the database encoding is also in utf8?

@tetech said:
Define the column with a collation type ending in _bin

Sorry, could you please explain a bit about this? Do you mean what @ehab is recommending?

@ehab said:
are you able
select * from MyTable where short_url collate latin1_swedish_ci = "H6SP1A";
choose the correct encoding first?

Oh... this might actually work with collate type utf8... Need to try it with MySQL/MariaDB.

tetech · March 11

@somik said: Sorry, could you please explain a bit about this? Do you mean what @ehab is recommending?

CREATE TABLE MyTable (short_url CHAR(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, ...); or do ALTER on an existing column.

It functionally ends up doing the same, but putting it in the schema means you shouldn't have to adjust every query.

somik · March 11

@tetech said:

@somik said: Sorry, could you please explain a bit about this? Do you mean what @ehab is recommending?

CREATE TABLE MyTable (short_url CHAR(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, ...); or do ALTER on an existing column.

It functionally ends up doing the same, but putting it in the schema means you shouldn't have to adjust every query.

Thank you very much for the SQL code. I was planning to modify my tables anyway as the existing tables are not working so having actual code really helps!

Mysql database query case in-sensitive...

Comments