Why does MySQL return two different data sets if I do `WHERE field = 1` and` WHERE field = '1``?

Question

I noticed something strange: MySQL returns two different data sets when I make a query with WHERE field=1 and WHERE field='1' .

field defined as enum ('0', '1', '2').

How is it possible that I get different data sets?

For the first request, I get about 500 entries. For the second I get 19 (!!!).

I can not explain it. Any ideas?

Thanks, Boda Sido.

Answer 1

In MySQL, the enum index starts at 1 (in fact, 0 is an empty string). So

 field = 1 

should be seen as

 field = '0' 

Answer 2

What type of data is the field? In one example, you compare the value of "field" with a NUMBERIC value of 1, and in another you compare it with the string "1", that is, with one character string containing the text "1". These two are not necessarily the same . [Edit: I forgot about MySql ENUM, it was a long time ago since I actively used MySql, so for this particular case, what KennyTM said =)]

Without looking at the dataset that you are using and defining the table (for viewing the data types), as well as the specific version of MySql that you are using, I cannot give a better answer. But, it comes down to the fact that the two queries that you execute, while superficially the same, are actually different.