Mysql where clause accepting integer with strings on primary key field

Question

In MySql i have a primary key field id, when i do select * from users where id='12p' it brings the value of row 12.

Please how do I go about this.

Updated Nodejs sample

const { User } = require('models')

User.findOne({where:{id:'12p'}}).then(user => console.log(user))

if id column is a int datatype the result MySQL returns is "correct" because '12p' is auto casted to a int by MySQL.. Thats why you should NOT ever use single qoutes on a int datatype. Note other database systems will give a error when using single qoutes on a int datatype. — Raymond Nijland
– Raymond Nijland, Commented Jul 26, 2018 at 17:46

Suraj Singh · Accepted Answer · 2022-09-22 14:59:17Z

2

In MYSQL, While you put your string values in where clause it parses integer number out of it. For instance, If you put '1abc', it'll parse 1 out of it. Follow the other examples:

'abc' => 0,
'2abc' => 2,
'a2bc' => 0,
'1a1bc' => 1

I don't know exactly Why does this happen but that's how MySQL works. I myself was struggling with that bug in my job. I had to add some extra logics to get the valid response.

answered Sep 22, 2022 at 14:59

Suraj Singh

891 silver badge11 bronze badges

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

Collectives™ on Stack Overflow

Mysql where clause accepting integer with strings on primary key field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related