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?

Pekka · Accepted Answer · 2010-09-10 11:48:49Z

3

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:

edited Sep 10, 2010 at 11:48

answered Sep 10, 2010 at 11:42

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pablo Santa Cruz · Accepted Answer · 2010-09-10 11:40:54Z

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.

answered Sep 10, 2010 at 11:40

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Comments

Julien Hoarau · Accepted Answer · 2010-09-10 11:44:02Z

0

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.

answered Sep 10, 2010 at 11:44

Julien Hoarau

50.2k20 gold badges134 silver badges117 bronze badges

Collectives™ on Stack Overflow

MySQL searching on INT columns returning wrong results

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related