MYSQL Binary in Where Clause-CodePudding

I have table mysql name customer . And have 1 record customer.mypage_login_id = 'hoangthien.lp [email protected]' .

When i use query :

SELECT mypage_login_id , customer.*
FROM (`customer`)
WHERE mypage_login_id = BINARY 'Hoangthien.lp [email protected]'
AND `disable` =  0;

Result : no row found

But when i use query :

SELECT mypage_login_id , customer.*
FROM (`customer`)
WHERE mypage_login_id = 'Hoangthien.lp [email protected]'
AND mypage_login_id = BINARY 'Hoangthien.lp [email protected]'
AND `disable` =  0;

Result : 1 row found . Why ? I using Mysql 5.7

CodePudding user response：

The binary keyword can be used after WHERE clause to compare a value with exact case sensitive match.

In your case, the value of mypage_login_id in your first query (Hoangthien.lp [email protected]) doesn't match your record in your table hoangthien.lp [email protected].

And in your second query, your first WHERE condition isn't case sensitive, that's why that works.

CodePudding user response：

You do not need BINARY data type specificator - your data is textual.

You may specify binary collation (like WHERE mypage_login_id = 'Hoangthien.lp [email protected]' COLLATE utf8mb4_bin) if you want to apply binary compare. This is more correct.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fd012fcf1e61c432fef269fc9c6c7ea0

PS. Do not enclose the table name into the parenthesis.