MySQL searching on INT columns returning wrong results

Question

I have discovered that MySQL is returning odd results when searching on INT columns.

a)

SELECT *
  FROM `sellers` 
 WHERE `seller_key` = '1' 

Returns seller with the key 1.

b)

SELECT * 
  FROM `sellers` 
 WHERE `seller_key` = '1sdjhksadhak' 

Returns seller with the key 1.

c)

SELECT * 
  FROM `sellers` 
 WHERE `seller_key` = '1adlksajdkj187987'

Returns seller with the key 1.

d)

SELECT * 
  FROM `sellers` 
 WHERE `seller_key` = 'adlksajdkj187987' 

Does not return anything.

Why does b and c return a result? if there a way to make the searching strict?

Answer 1

It's probably because your seller_key column is of a numeric type.

So when you use '1', '1sdjhksadhak' or '1adlksajdkj187987', they are all converted to numeric value 1.

Answer 2

You are doing a comparison against a numeric column.

To do that, mySQL needs to automatically convert the string into a number.

The casting process uses anything that can be used for an integer value, starting from the left-hand side, and discards everything else.

Here is an example that highlights the behaviour:

SELECT CAST( '1adlksajdkj187987' TO unsigned)

will return 1:

The easiest solution is to not use quotes:

SELECT *
  FROM `sellers` 
 WHERE `seller_key` = 1 

I imagine mySQL will throw an error here if run in traditional mode.

mySQL theory for those interested:

• 11.2. Type Conversion in Expression Evaluation
• 11.10. Cast Functions and Operators

Answer 3

seller_key is an INT column, so you should use :

SELECT * FROM sellers WHERE seller_key = 1 to compare seller_key with a numeric value.