How to deal with case sensitive records in MySQL?

[SandraJose]

Hi

I am facing very unusual problem with mysql. I have two records in my table, one with “jeremy” and one with “Jeremy”, but when I fired query

SELECT * FROM Namelist WHERE Name = “Jeremy”;

MySQL returns two records Jeremy, and jeremy to me, how to set these two records as a separate record?

[Rozanne]

In this case you need to use BINARY compare in the WHERE clause, which will force to match records on the binary collection.

You should try following query:

SELECT Name FROM Namelist WHERE BINARY Name = “Jeremy”;

Also try to fix the data so it doesn't happen and even better fix the design or the application so it cannot happen again.

[Kristine Habeck]

Let me jump here with my concern issue. I use following statement to check user name and password of users:

SELECT COUNT (*) AS num FROM 'tb1users' WHERE BINARY user_name = 'un' AND password = 'pw'

problem is, it considers both user_name and password case sensitive, and I want only password as a case sensitive, so how can I change the statement?

[Rozanne]

The BINARY operator converts a string to an array of bytes and compares the results byte by byte. This only requests to the variable preceding the BINARY reserved word. I don't understand why you want to convert it into BINARY when you can compare it at a string level?

I am not sure about this statement, but you can try:

select user_name, password from tb1users where binary password = 'pw';

You will get the list of users who are using same password.