Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
I bet your varchar is silently casted as some numeric type and that gives a value of 0. – dezso Jul 13 '12 at 10:43
up vote 5 down vote accepted

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';
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.