MySQL SELECT query returning for integer and integer + string

Question

My table name students

uid  |  name
-----+-------
10   |  John
11   |  Smith

**Data types : **

uid  int(11)
name varchar(256)

My query :

SELECT name FROM students WHERE uid = '10'

Returns : John

My 2nd query :

SELECT name FROM students WHERE uid = '10someChar'

Returns : John

Why the second query returns John ?

@phpsessionid include create script for the table students — Vivek S.
– Vivek S., Commented Aug 20, 2014 at 5:19

Taryn East · Accepted Answer · 2014-08-20 05:11:20Z

5

The uid column is integer, and the value you pass in the where clause is first coerced into an integer... and most integer-conversion algorithms just grab the first set of digits they can find in the string (and ignore anything non-matching after it)... thus it finds 10 and ignores the rest

answered Aug 20, 2014 at 5:11

Taryn East

27.8k9 gold badges88 silver badges110 bronze badges

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

4 Comments

phantomCoder Over a year ago

I know coercion happens in programming languages, does SQL also works the same way? Its a new info for me.

Taryn East Over a year ago

SQL is a programming language :)

phantomCoder Over a year ago

Anyway to avoid coercion so that my second query should return empty rows. Possible?

Taryn East Over a year ago

That's a different question (to which I don't know the answer). I recommend you ask a new question for that.

Mauricio Piber Fão · Accepted Answer · 2014-08-20 06:25:15Z

1

MySQL automatically converts numbers to strings as necessary, and vice versa.

It is also possible to convert a number to a string explicitly using the CAST() function.

Type Conversion

take a read.

MySQL ever tries to return something - even if it's the wrong type, he'll cast automatically.

You should filter at PHP to validate your business rule.

Postgresql should throw a exception

edited Aug 20, 2014 at 6:25

answered Aug 20, 2014 at 5:22

Mauricio Piber Fão

9535 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT query returning for integer and integer + string

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related