where varcharcol = 0 returns all rows

Suppose I have a table like this:

create table mytable(cola int primary key, colb varchar(10));

Let's put some data in it:

insert into mytable values(1, 'abc');
insert into mytable values(2, 'def');

When I select using this query:

select * from mytable where colb = 0;

I get ALL rows.

Please note colb data type is varchar, but I use 0 as value. If I use 1 or other integers, I get no rows.

Why is that? Is there a setting to prevent this behavior?

BTW mysql 5.5.9 for windows.

This is caused MySQL's sloppy data type checking. As far as I know there is no workaround, except to write correct SQL.

In general it is highly recommended to never rely on implicit data type conversion. Make sure your literals match the datatype of the column.

So you should write:

select * from mytable where colb = '0';

StackExchange Database Administrators Q#20834, answer score: 5