Reputation: 26033
I'm really confused here. Running the following query:
SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = 5
returns rows that also start with "5", despite me neither using LIKE
nor a %
wildcard operator. How come?
The size
field is of type VARCHAR
.
Upvotes: 6
Views: 142
Reputation: 37365
That is because you're using comparison between numeric and varchar data. MySQL will implicitly convert your column to double
, resulting in 5
. See this simple test data:
mysql> select * from test; +-----------------+ | name | +-----------------+ | 5 | | 5 and some crap | +-----------------+ 2 rows in set (0.00 sec)
Now, "good" way: compare strings:
mysql> select * from test where name = '5'; +------+ | name | +------+ | 5 | +------+ 1 row in set (0.00 sec)
And "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 is it 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, non-significant part was just truncated (as mentioned in warning message above)
Upvotes: 11
Reputation: 21947
SELECT *
FROM `articles`
WHERE `form` = 'Depotplåster'
AND `size` = '5'
You should quote 5 because MySQL convert string from the table to int without quotes.
Upvotes: 0
Reputation: 2588
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
The conversion of string to integer looks for ints i nthe beginning of the string, and takes the largest integer until the first non-numeric character.
select '5s4'=5, 's5'=5, '5'=5 -- =>1,0,1
Upvotes: 1