Tolgay Toklar Tolgay Toklar - 7 months ago 12
SQL Question

Where field=0 returns all records

I am using Mysql, I have a field which type is

varchar(50)
and name is
email
and if I execute a query like this:

SELECT * FROM `users` WHERE email =0


It returns all records on the table. How can is this possible?

Example:

Records one of them:
gorkemasan24@gmail.com
and this query returns this row too.

Why is this happening and how to avoid this situtation?

Answer

When you compare a string field to a number, it converts the string to a number, and then compares those numbers. Converting a string to a number uses the number at the beginning of the string; if it doesn't begin with a number, it converts to 0.

So if you had an email like 12james@domain.com it wouldn't be returned, because that converts to 12. But gorkemasan24@gmail.com converts to 0.

You should write WHERE email = '0' to compare it to a string rather than a number. Or if you want to test for an empty email, use WHERE email = ''.