MySql SELECT found row when WHERE doesn't match-CodePudding

I have a pretty simple table called roles:

When I ran SELECT * FROM roles WHERE role_id = "1ojosd041l"(the WHERE clause clearly didn't match with any row in the table). It somehow returned this:

Does anyone know why this is happening? My guess is that my role_id column is of type int, but I passed a string into the WHERE clause, so the str to int conversion produced something weird (like a 0 or 1).

I'm just looking for an official explanation for this. If anyone knows why this happens please let me know! Thank you!

CodePudding user response：

Where MySQL expects a number, it will convert a string to a number, and ignore any leading whitespace or trailing garbage.

If you want to avoid this, do something like

SELECT * FROM roles WHERE role_id = "1ojosd041l" and concat(role_id) = "1ojosd041l"

Just the second condition would be enough but leaving the first condition in allows an index to be used.

CodePudding user response：

There is somee MySQL magic happening here. MySQL sees that role_id is numeric. So it converts your string '1ojosd041l' to a number. This string isn't a number obviously, and MySQL should throw an error. But MySQL just takes as many digits as it finds from the left side of your string instead, so it gets number 1. (If your string started with a non-digit, then MySQL would even return a zero.)