Clever Geek Handbook

Using one simple MySQL trait instead of equal sign

I'm just wondering if anyone knows of the difference between:

select * from database .user_table WHERE user_id-'user ';

and

select * from database .user_table WHERE user_id = 'user';

Both seem to work, however they return a different result.

Thank!

+3


source to share


1 answer


It depends on the type user_id

. Let's assume it's a string and then:

WHERE user_id = 'user'

will return strings where user_id

is a string 'user'

(or possibly capital letters depending on the mappings for the database and the column).

When you do:



WHERE user_id - 'user'

Then two things happen. If the expression evaluates to 0

or ( NULL

), the row is filtered. This is equivalent WHERE user_id - 'user' <> 0

.

-

is a numeric operator, so both values ​​are converted to numbers. MySQL uses a silent conversion method that converts leading "digits". Thus, it 'user'

converts to 0

(no leading or numeric characters). 'user_id'

will also be converted. In this case, basically anyone user_id

that starts with a digit will pass the test (as do most that start with a decimal point).

If user_id

is an integer, then it is the user_id = 'user_id'

same as user_id = 0

. And it user_id - 'user'

matches user_id <> 0

.

+6


source






More articles:


All Articles