Why does SELECT ... WHERE last_name = 0 return all rows?

Question

I have a simple table in MySQL:

create table t_users( user_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, PRIMARY KEY(user_id)); 

I am puzzled to find that the following query returns ALL rows:

 SELECT first_name, last_name FROM t_users WHERE last_name = 0; 

Can someone explain this? Thanks!

Answer 1

In MySQL, if you compare a string and a number, the string will be converted to a number, which results in 0 for each row. AND

 0 = 0 

truly.

If the line starts with a number - say 123abc , then this will result in 123 .

SQLFiddle demo

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some transformations occur implicitly. For example, MySQL automatically converts numbers to strings as needed and vice versa.

Documentation

Answer 2

The reason is that u is comparing a string with a number field. Any field without a valid integer will be 0. You must compare it as

 SELECT first_name, last_name FROM t_users WHERE last_name = '0';