Page 1 of 1

mysql VARCHAR acting like INT

Posted: Mon Oct 05, 2009 12:22 pm
by chopsmith
Hi, All.

I have a table that contains a varchar(10) column named weird_field. In this column, only integers reside (e.g., 3010101009). When I do this:

Code: Select all

 
select * from table where weird_field = '3010101009'
 
no records are returned, even though there is a record with weird_field = '3010101009'.

However, if I do this:

Code: Select all

 
select * from table where weird_field = 3010101009
 
the appropriate records are returned. I don't think this should be the case, right? Because it's a varchar column, shouldn't the 3010101009 be quoted? It's messing up some joins I'm trying to do, and I just don't get it.

Thanks

Re: mysql VARCHAR acting like INT

Posted: Mon Oct 05, 2009 5:17 pm
by infolock
Show us your table schema and the query.

The reason is, even on an INT field, quotes or no quotes will return the same result.

Seems to me it may be something else...


-Jon

Re: mysql VARCHAR acting like INT

Posted: Mon Oct 05, 2009 6:31 pm
by califdon
Do a
SHOW CREATE TABLE xxxx

I'm sure you will find that what you think is a varchar column is really an int.

Re: mysql VARCHAR acting like INT

Posted: Tue Oct 06, 2009 9:32 am
by chopsmith
Thanks for the replies, but I just decided to turn the columns in both tables I'm joining into bigints. Before doing that, my schema identified both as varchar fields, but for some reason quoting the value would not bring up any results. I wish I had done the show create table statement before making the change, because I was very perplexed. I never knew of that statment. Thanks for the help.