Why does MySQL return two different data sets if I do a `WHERE field=1` and `WHERE field='1'`?
I noticed something weird -- MySQL is returning two different data sets when I do a query with WHERE field=1
and开发者_开发知识库 with WHERE field='1'
.
The field
is defined as enum('0', '1', '2').
How is it possible that I get different data sets?
For the first query I get roughly 500 records. For the second I get 19 (!!!).
I can't explain it. Any ideas?
Thanks, Boda Cydo.
In MySQL the enum index starts with 1 (actually, 0 is the empty string). So
field = 1
should be viewed as
field = '0'
What datatype is "field"? In one example you're comparing the value of "field" to the NUMBERIC value 1, and in the other you're comparing it to a string "1", i.e. a one character string that contains the text "1". The two are not necessarily the same. [Edit: I'd forgotten about MySql ENUM's, it's been a while since I actively used MySql, so for this specific case, what KennyTM said =)]
Without seeing the dataset you're using, and the table definition (to view datatypes), along with the specific version of MySql you're using, I can't give a better answer than that. But, it does boil down to the fact that the two queries you're performing, whilst superficially then same, are actually different.
精彩评论