Why SELECT ... WHERE last_name = 0 returns all rows?

Question

I've got 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'm puzzled to discover that the following query returns ALL rows:

SELECT first_name, last_name
FROM t_users
WHERE last_name = 0;

Can someone explain that? Thank you!

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 string. And

0 = 0

is true.

If a string would start with a number - say 123abc then it would result in 123.

SQLFiddle demo

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

Documentation