Tolgay Toklar Tolgay Toklar - 2 years ago 67
SQL Question

Where field=0 returns all records

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

and name is
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?


Records one of them:
and this query returns this row too.

Why is this happening and how to avoid this situtation?

Answer Source

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 it wouldn't be returned, because that converts to 12. But 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 = ''.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download