Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
3  
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
add comment

1 Answer 1

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
add comment

Your Answer

 
discard

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.