mysql select query gives result where ID (a number) is equal to text

Question

I have a table _users with a field id as bigint(24).

The result of this query:

SELECT * FROM `_users` WHERE `id`='5text'

is the row with id = 5. I would expect no result since '5text' is not a number. How is this possible?

Answer 1

MySQL implicitly converts strings to numbers when evaluates expressions to make the operands compatible.

From the documentation:

If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

See the Type Conversion in Expression Evaluation article for the detailed explanation and examples.