MySQL returns wrong results on search by id

Question

I am having a simple table users with 'userid' and 'username' fields. Please check commands as follow:

Select * from users where userid = '5'; --returns me 1 result - Right

Select * from users where userid = '5 shiv'; --returns me 1 result - Wrong

Why second query still returns me one result. What could be the problem? Is this a problem with the engine or MySQL itself?

possible duplicate : http://stackoverflow.com/questions/21762075/mysql-automatically-cast-convert-a-string-to-a-number — xlecoustillier, Jul 07 '15 at 10:18
@dirtyhandsphp Probably because you haven't included the details, or done any more work yourself — James Z, Jul 12 '15 at 11:57

score 0 · Answer 1 · edited Apr 13 '17 at 12:42

0

'5 shiv' evaluates to 5 and skips your String part after 5.And you have record with userid as 5.

Edit : Cast your expression to varchar

Select * from users where CAST (userid as varchar(10)) = '5 shiv';

edited Apr 13 '17 at 12:42

Community

answered Jul 07 '15 at 10:10

Pratik Joshi

Can you expand and explain why it would behave like that ? And ideally provide a reference ? – xlecoustillier Jul 07 '15 at 10:11
It behaves like that because you are telling it to compare a string to an integer :) – Vatev Jul 07 '15 at 10:33

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

0

The problem here is that MySQL is doing an implicit conversion from the string to an int when doing the comparison. For example :

SELECT * FROM cloths WHERE id = "5 shiv"

making the conversion explicit, this is what MySQL is doing:

SELECT * FROM cloths WHERE id = CAST("5 shiv" as int)

Instead, what you want to do is cast the id to a string, and compare that way. The solution is simply to:

SELECT * FROM cloths WHERE CAST (id as varchar(10)) = "5 shiv"

You can also check by Refer the link: Doc link

edited Apr 13 '17 at 12:42

Community

answered Jul 07 '15 at 10:23

Aman Aggarwal

why 2 answers ? And why do you copy `as it is` from the Link? – Pratik Joshi Jul 07 '15 at 10:30
1

so that user can understand better – Aman Aggarwal Jul 07 '15 at 10:32
If you want to copy answer **`As It Is`** , Just Give link in Comment just below the Question as you are not adding anything else to the Readymade answer. – Pratik Joshi Jul 07 '15 at 10:35
Please consider modifying answer as per OP's Query. – Pratik Joshi Jul 07 '15 at 10:36

2 Answers2