Does the query return "LIKE" despite the absence of wildcards?

Question

I'm really confused. Executing the following query:

SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = 5 

returns lines that also begin with "5", despite the fact that I did not use LIKE and % . Why?

The size field is of type VARCHAR .

Answer 1

This is because you are using a comparison between numeric and varchar data. MySQL will implicitly convert your column to double , resulting in 5 . See the following simple test data:

  mysql> select * from test;
 + ----------------- +
 |  name |
 + ----------------- +
 |  5 |
 |  5 and some crap |
 + ----------------- +
 2 rows in set (0.00 sec)

Now, the "good" way: compare the lines:

  mysql> select * from test where name = '5';
 + ------ +
 |  name |
 + ------ +
 |  5 |
 + ------ +
 1 row in set (0.00 sec)

And the "bad" way: compare integers:

  mysql> select * from test where name = 5;
 + ----------------- +
 |  name |
 + ----------------- +
 |  5 |
 |  5 and some crap |
 + ----------------- +
 2 rows in set, 1 warning (0.05 sec)

- and here is your reason:

  + --------- + ------ + -------------------------------- --------------------- +
 |  Level |  Code |  Message |
 + --------- + ------ + -------------------------------- --------------------- +
 |  Warning |  1292 |  Truncated incorrect DOUBLE value: '5 and some crap' |
 + --------- + ------ + -------------------------------- --------------------- +
 1 row in set (0.00 sec)

Finally, to understand why this is so:

 SELECT CAST('5' AS DECIMAL) AS 5d, CAST('5 and some crap' AS DECIMAL) AS 5sd, CAST('5' AS DECIMAL) = CAST('5 and some crap' AS DECIMAL) AS areEqual; 

will result in:

  + ---- + ----- + ---------- +
 |  5d |  5sd |  areEqual |
 + ---- + ----- + ---------- +
 |  5 |  5 |  1 |
 + ---- + ----- + ---------- +
 1 row in set (0.00 sec)

- as you can see, a small part was simply truncated (as indicated in the warning above)

Answer 2

 SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = '5' -- this will compare the string 'size' with the string '5' SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = 5 -- this will convert string 'size' to integer and then compare with the integer 5 

Converting a string to an integer searches for ints at the beginning of the string and takes the largest integer up to the first non-numeric character.

 select '5s4'=5, 's5'=5, '5'=5 -- =>1,0,1 

Answer 3

 SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = '5' 

You must specify 5 because MySQL converts a row from a table to int without quotes.